Table/Junction tables?

[tklingborg]

Hi. I'm trying to design a database where I can log the transports I make. I need help with ideas on how to build the table either in one table or with junction tables.
The information I want to store is Transportid, vehicle, Containernr, Haulier, Pick up terminal, pick up date, pick up time, pick up comments, loading customer, loading date, loading start, loading end, loading product, loading compartment(s), Unloading customer, unloading date, unloading start, unloading end, unloading product, unloading compartment(s), drop off terminal, drop off date, drop off time, drop off comments.

The thing that I find tricky in designing this is that sometimes I pick up more than one container at the same time, sometimes I have more than 1 product in the container and sometimes I have multiple customers in the same container.

At the moment I have tried with 4 junction tables but I'm not sure how to get the information together in a report in a good way.

Thankful for any help

 

[Micron]

If you can pull all the information together in a query, you should be able to build a report. Without benefit of more information, I'm thinking the report, grouped by trip/container/item might work, but a sub report might prove useful too. What did you come up with for a table structure? I envision a trip table, container table with tripID, item table with tripID, containerID & custID and customer table. A comments table would be separate only if there could be multiple comments, say from different people. Your db may also need to store info about suppliers.

 

[ask2tsp]

From your description I derived the following Entity/Relations

----- entities -----
Compartment
Container
Customer
Haulier
Product
Terminal
Transport
Vehicle

----- relations -----
A Compartment has one or more Products
A Compartment in a Container
A Container has one or more Compartments
A Container at a Terminal
A Container transported on a Vehicle
A Customer has one or more Products
A Haulier executes one or more Transports
A Product is for one or more Customers
A Product is in one or more Containers
A Terminal has zero or more Containers
A Transport uses one or more Vehicles
A Transport picks up from a Terminal
A Transport drops of at a Terminal
A Vehicle is used in a Transport
A Vehicle has one or more Containers
--------------------

You only mention 4 tables. So either I misread your description or this is one of the reasons why you are not able to produce that report.

So let us first agree on the ER model, complete this by adding the attributes to the entities.
Then draw a scheme that shows how all this data is build and routed to your excel app (A DataFlow Diagram).

Finally build the required tables in exel and, by linking them, create a 'super' table as a basis for a PowerPivot.

Not a simple task, but do-able I think.

Also, start reading the book Excel 2013 Pivot Table Data Crunching Jelen, Bill; Alexander, Michael (2013-01-07). Excel 2013 Pivot Table Data Crunching (MrExcel Library)