Issues creating a work order system

[Udaman]

I have managed so far to create numerous tables, queries and forms to view and edit data, but the one thing I am completely stumped on is getting the right information based on two criteria to appear.

I have two database files:

• Assets - This is where I track all information pertinent to just the assets (vehicles).
• WorkOrders - Each Asset will have multiple work order numbers. These are Autonumber generated.

What I have been able to do is create a form that when you type in the Asset Number, a list of all Work Order numbers appears in a sub-form, with a datasheet view of all the available work order numbers and a summary of information. I can then double click on the work order number and it opens up another form with extended data about the work order. However, this is still just a summary of the work order. What I need next is to break out what I will call "Modules", which are specific parts of the work order. Examples of these modules are:

• Brakes
• Transmission
• Engine
• Powertrain
• Etc...

In total I will have 10 of these modules (each it's own table), and I need to be able to have information shared from the main tables to these modules and back.

So the thing I am stumped on right now is being able to have a form that when I type in the Asset Number, instead of having it pull a query of all work orders numbers with a summary, I simply just want a list of corresponding work order numbers to show up in a combo box to choose from. Once the Asset Number and Work Order numbers have been chosen, then I can use navigation buttons to jump back and forth between the modules, without having to keep doing a search for the Asset and WO number combos.

So let's say a mechanic is working on a specific module, in order to get there he first has to type in the Asset Number, choose the correct WO number, then he would then just be able to jump to that module, make the changes, and have it reflect back on the summary page.

I have tried numerous methods to get this to work, and the only success I have had is in either a list of all work order numbers, or a blank list. I can't move forward with creating navigation buttons to the modules unless I get this piece to work first, so I am at an impasse at the moment.

So, my first question is, can anyone suggest the best practice to make something like this system work? Should I be using text boxes, lists, combo boxes, or perhaps something else I have not thought of or know about? Perhaps there is a tutorial out there that I have not found (believe me I looked) that might give me some step by step instructions that could aid me?

Any help or direction will be enormously appreciated.

 

[Micron]

Not sure I agree with your approach. Most of my career was as a mechanic with some specialization in equipment reliability and maintenance, and our company was known world wide (I kid you not) with our approach to ER. Though not involved in db design (it surpassed $10,000,000 in design, support, maintenance and modifications), I am VERY familiar with the concepts and use of such a system. So my take is that you are mixing the systems of a vehicle with a work order system by treating those vehicle subsets (modules, as you call them) as parts of a work order system. The biggest drawback you will experience down the road is that you cannot easily break out a system into sub system parts without parsing your tables into sub tables. If I were you, I would seriously consider having a table of equipment location codes with a parent level and as many child levels as you initially see fit. Any time you want to create a new child level, it is easy to add table rows as you probably know. It might look something like this:

EQUIP_LOC
LOC_CD	SUB_LVL	DESC
1000	000	POWERTRAIN
1000	100	ENGINE
1000	200	TRANSMISSION
1000	300	DIFFERENTIAL
1000	400	AXLES
2000	000	FRAME
2000	100	CAB
3000	000	ELECTRICAL
3000	100	WIRING
3000	200	LIGHTS
3000	300	CHARGING

<tbody>
</tbody>

If you want to be able to break child levels into sub-children, a slightly different approach would require you to have a pre-determined number of sub levels as fields, or perhaps a table of location numbers as well. Anyway, if that concept interests you, let me know.

The simple answer to your dilemma if I understand all of your post would be to have separate combos for asset and module, whose choices provide the applicable work orders. However, using your current approach, you will require a query with 10 module tables plus your asset and work order tables with external joins (because not every module table will return records for an asset). Should you ever add a table, the query will have to be redesigned - two good reasons for not doing it your way.

I also wonder if you really need a separate database for the assets, and if you've considered work order status and type as well. Types (PM or Corrective, Emergency, Unplanned, Fabrication) can provide useful data on where the money is spent (breakdowns or PM?). Status allows a work order to move through approvals, or at least, prevent them from showing up in your form results so that you don't have to see work orders from years ago because they are 'Complete'.
Lots of possibilities!