Simple Question.. I hope :) 1

[Borian]

Currently I have built a Service Management Database for the trucking company I work for. I figured out how to muddle through creating the forms and querries to make it work the way I wanted it. Now for my current problem.

How do I make a report that will do the following.

Tables/Forms/Querries in use.
Tables:
1. Tractor/Trailer Table - Parent Table which holds information about the Equipment
2. Tractor/Trailer Service Orders - Child Table which holds the information about the Service work being performed.
3. Tractor/Trailer Parts - A Grandchild Table linked to the Service Order for all parts used in that Service Order. Currently I am also linking it to the Parent table but it requires a manual entry of the Unit Number (Parent Master Index)
*** The relationships are Parent Table (Primary Key = Unit Number) --> Child Table (Primary Key = Service Order Number (Auto assigned #) with a relationship to Parent Table using Unit Number)--> Grandchild Table (Primary Key = Part Number and relationships to Parent and Child Tables) ***

Queries:
I have two querries to pull everything into format which allowed me to created the forms.

Forms:
I finally have Three forms which are being used in a linked form format. There is the main form and two subforms. This allowed me the format I needed.

Finally the question at hand!
I need a report which will print the Unit information, Service Order information and the parts replaced on that service order. I only want to print the Tractor Information, Service order which is currently loaded and the parts associated with that service order.

I know I'm long winded but for those of you who have stuck with me thanks for reading and I hope someone can help me with this.

 

[dhookom]

What you are doing is comparable to storing the customerID in the Order Details table in Northwind. Northwind stores the CustomerID in the Orders table and not the Order Details table. It doesn't make much difference how many records you add.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Borian]

Thank you Duane,

I agree with your analysis on the situation, however the situation is different.

There is a real need to view the parts associated with the unit irrespective of the service order which generated the replacement of the part.

But having said that I have fixed the problem.

If there's any interest:

When building subforms into forms it allows you to identify the linked fields in both child and master. What I didn't realize was that you could add additional links by seperating them with semicolons.
So now:
Repair ID;Unit# ---> Linked to Repair ID;Unit# in Service Order File

Problem solved.

Thanks Again,
Paul

 

[dhookom]

Paul,
I could have told you that but didn't want to ;-). You haven't convinced me that you need your tables set up as you have but it isn't my application to build.

Good luck, I'm sure you will have success.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Borian]

LOL
Duane,

The difference between the Northwind database and what I am looking to accomplish is this.

In the orders database, there is really no reason to go back and look at the order detail records independent of the Order record. Without the Order Record the details have little to no meaning.

However, in this situation the parts on a Tractor, Trailer, Forklift and other equipment do have a purpose not related to the Service Orders.

example: Forklift #1 is an odd forklift which has been in use for 30 some years. The parts to tune up the fork lift are hard to find and usually take some research, especially when you consider that it gets a tune up maybe once a year. With having the parts independent of the service orders all they need to do is click on the parts button and sort the description column alphabetically and look for tune up kit. This give the supplier last used, last date replaced and last price. They no longer need to spend time trying to figure out what and where to get the parts neccesary. They don't have to search through a years worth of oil changes, tire replacements, fork replacement, ect to find it either. (It can be done that way, but as a matter of convinience it's easier done this way)

 

[dhookom]

Then Parts may or may not be related to a service order. If this is the case then I can undestand storing the unit number in the Parts table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.