Many to Many ?

[keydash]

I seem to have brain lock on this problem, I keep going round and round and can’t get a handle on it.

 

Given:

• A dump truck is given a ticket number for each load of gravel.
• Each load will be deposited in up to six numbered rail cars (usually not reused).
• Each rail car will contain gravel from one to three dump trucks.

 

Problem:

• Track which rail cars a truck load went into and the cubic yards from the truck.
• Track which trucks delivered to a rail car and the cubic yards of gravel in the rail car.

Help!

 

Thanks,

Al

[comment]

Each load will be deposited in up to six numbered rail cars (usually not reused).

 

Do you mean that a single load can be divided among many rail cars? And what does "not reused" mean in this context?

 

 

--

P.S. Are you really using version 7?

[keydash]

Yes, a single load can be divided among many rail cars. The rail cars are not reused, I was trying to indicate that new rail cars are continually cycled in.

 

Oops! No, using 13.

[comment]

If I am reading this correctly, you should have the following tables:

 

Trucks -< Loads -< Deposits >- RailCars

 

The Deposits table would have (at least) these fields:

• DepositID (auto-entered serial number)

• LoadID (or TicketNumber)

• RailCarID (or a unique rail car number)

• Amount

 

If you don't want to track the rail cars, you can do without the RailCars table. However, if you want to answer the question "how much gravel went into a specific rail car, from which trucks/loads?", you will need to either produce a report from the Deposits table, sorted and summarized by rail car, or enter a specific rail car number into a global field and get the data through a relationship based on the global.

 

Note that regardless of whether you track the rail cars or not, you will need some kind of mechanism to associate a rail car with the relevant deposits only. Otherwise, if a rail car was re-used several times during the year, you will not have a way to separate between the deposits made on different dates.

[keydash]

This is a late response. Thank you for your insight. You seem to have grasped exactly what I need. At this point I am unclear as to how I can designate the amount of gravel to be transferred to three rail cars from a single Truck. 

 

Making three deposits with the same LoadID and different RailCarID’s did not work as I had expected.

[comment]

did not work as I had expected.

 

Not much to go on here.