I need to create multiple bills of lading from one shipment record

[wchelly]

I need to create multiple bills of lading from one shipment record.
Each shipment could have multiple trucks.
Each truck has mutiple container types.
And each container has multiple serial numbers.
Each Truck needs it's own bill of lading.

I have a Shipment Table
A container by shipment table Table
A Serial Number by shipment Table

I was thinking of making a field on the Container by shipment table to track the truck. I'm not sure if this will allow me to print my bill of ladings by truck. I guess maybe if I group them by truck number. Is my thinking correct here?

 

[G37Sam]

If your tables & relationships are well setup then you won't have any problems printing the desired reports (sorry if this sounds too generic)

 

[wchelly]

Thanks but yes, that's probably too generic and perhaps this question isn't posted in the right forum, but that was the whole point to my question. It is unclear to me if I need to create a table with a record for each truck to create this report....or if I can just create a field for the truck number on the container record which relates back to the shipment number.

 

[Isskint]

Without seeing the structure you have, it will be difficult. Can you post a picture of your table relationships?

essentially you should have something like this:
Shipment Table
ShipmentID
other fields

container by shipment table Table
cbsID
ShipmentID
other fields

Serial Number by shipment Table
snbsID
ShipmentID
other fields

If this is the case then adding the truck reg (or ID) to one of the last 2 tables might work, but again it depends on your structure. Incidentally, I work in the Logistics world so am quite familliar with the concept you are describing. Do you track the trailer and/or skellie numbers also?


In the attachment you can see how I link Work (driver, vehicle, start time etc) with container moves (what box, where from etc) to the jobs (where to drop the box OR items from the box at multiple locations).

 

[wchelly]

Well I was trying to simplify the concept when I explained it so the tables are a bit more involved. But this attachement will explain what I think you need. And yes, we do (or will) track trailer numbers for the BOL also.

 

[Isskint]

But this will explain what I think you need.

no attachment??

 

[wchelly]

I noticed that I forgot the attachement and then added it to my above post, sorry~!

 

[Isskint]

You should have everything you need here. Everything seems to be tied back to the Shipment#.

First step is create a query with the relevant fields (PK's and FK's and fields needed for BOL) from the 4 tables on your attachment. Set a criteria in the query equal to a shipment# (eg ="123456" OR =Forms!SomeForm.Shipment#). The query should return information on Materials, Packages and Tracking. Create a report that only uses the info you need for the BOL. Difficult to be certain without some data to play with

 

[wchelly]

I have a query like that, the only thing is that to this point, the containers are not assigned to a particular truck. Which trucks go on which shipments (some shipments have multiple trucks), but we havne't tracked which containers go on which trucks. So I need to add a "truck #" field to the material description table so that when I group them, they will group correctly.

 

[Isskint]

Yeah i see the problem now. You would need to add/relate the truck ID to another table and material description should work.

 

[G37Sam]

So a truck can have more than one shipment, and a shipment can have more than one truck?

 

[wchelly]

Yes, some shipments require more than one truck. And of course one truck can be used for more than one shipment, but we don't really care if the same truck is used for A, B, C, or D shipments. We do care which packages are on which truck.

 

[wchelly]

Attached is an image of what the tables and relationships now look like.

 

[wchelly]

I've created this bill of lading. It should print one truck per page, but it ends up printing several all of the materials for every truck in the detail section. What do I need to change.

 

[G37Sam]

If you're not storing any info about the trucks (driver, VIN, number plate etc..) then you won't need a table for them.

 

[wchelly]

That's what I was thinking. I think I just need to add a column to my materials/containers table for truck number so that I can group which materials go with which truck, which is what I've done above, but I can't get the report to display how I want it.