Need help with logistics database - multiple stops from origin to destination

[kagoodwin13]

I am currently designing a database with logistics rates. I'm stumped on a task now, and can't seem to find a way around it.



Right now, I am working on generating a report of rail deliveries. I made a similar one with truck deliveries, and since truck deliveries are direct from origin to destination (no stops/changeovers), it went rather smoothly. Rail is different though as there can be as many as two stops (origin, stop1, stop2, destination) in the path. For instance, if you ship a rail car from a city in Mexico to the US East Coast, you will have one carrier in Mexico, another in the midwest, and another in the eastern US. Each carrier has a monopoly on their rails, so the cars must be stopped and changed over to another carrier to continue on.

The end goal would be to report:
Origin, Destination, each stop in between, the rate between each point (along with the carrier), then multiply the rate between each point by the volume of rail cars to find the total cost between each point. With that, I can summarize total cost from each origin to destination.

I currently have a table of point-to-point rates (per rail car), a table of volume of rail cars used, and a table of routing. I have made some concatenated IDs in Excel to try and relate the three tables, but I'm not getting very far. Any ideas on how to best relate all of this information together?

[kagoodwin13]

UPDATE:
I have created three queries: Origin to Stop 1, Stop 1 to Stop 2, and Stop 2 to Destination. I believe what I need to do now is to create a Union query to join all of this information together.

This is a bit above my skill level. I am not a trained SQL programmer. I know Visual Basic and HTML, so I'm not completely foreign to programming, but I've never used this language. I'm having trouble implementing the SQL code necessary to make it happen.

Conceptually I'm having a hard time understanding a union query. When I thought, "how do I create a query to combine the results of three other queries?" a union query seemed to be the definite answer. However, union queries reference tables and not other queries. This makes it tough, as not all tables have the same number of records or fields. I keep running into error messages that tell me the union query cannot be made with tables that are not the same size.

I think the union query is the only way to do this. I thought possibly that I could create a report from the queries, but the wizard seems to think some query fields are table fields and it won't go forward.

Ultimately, this needs the functionality to take search criteria from a form, generate query results, and then populate a report that will summarize the data.

Any other ideas?

I am currently designing a database with logistics rates. I'm stumped on a task now, and can't seem to find a way around it.

Right now, I am working on generating a report of rail deliveries. I made a similar one with truck deliveries, and since truck deliveries are direct from origin to destination (no stops/changeovers), it went rather smoothly. Rail is different though as there can be as many as two stops (origin, stop1, stop2, destination) in the path. For instance, if you ship a rail car from a city in Mexico to the US East Coast, you will have one carrier in Mexico, another in the midwest, and another in the eastern US. Each carrier has a monopoly on their rails, so the cars must be stopped and changed over to another carrier to continue on.

The end goal would be to report:
Origin, Destination, each stop in between, the rate between each point (along with the carrier), then multiply the rate between each point by the volume of rail cars to find the total cost between each point. With that, I can summarize total cost from each origin to destination.

I currently have a table of point-to-point rates (per rail car), a table of volume of rail cars used, and a table of routing. I have made some concatenated IDs in Excel to try and relate the three tables, but I'm not getting very far. Any ideas on how to best relate all of this information together?

[kagoodwin13]

My progress so far on a union query:

SELECT [Route Origin City],[Rail Carrier 1],[Interchange 1],[Rail Carrier 2],[Interchange 2],[Rail Carrier 3],[Route Destination]
FROM [Rail Routing Table]
INNER JOIN [Rail rate data]
ON [Rail Routing Table].[OrgCarrierStop1]=[Rail rate data].[Rate Route ID]
INNER JOIN [Rail Volumes]
ON [Rail Routing Table].[OrgCarrierStop1]=[Rail Volumes].[Volume Route ID]
UNION ALL SELECT [TRI-LEVEL],[BI-LEVEL],[AUTO-MAX BI-LEVEL],[EFFECTIVE DATE],[EFFECTIVE THRU]
FROM [Rail rate data]
INNER JOIN [Rail Routing Table]
ON [Rail rate data].[Rate Route ID]=[Rail Routing Table].[OrgCarrierStop1]
ON [Rail Routing Table].[OrgCarrierStop1]=[Rail rate data].[Rate Route ID]
UNION ALL SELECT [Sum of TRI-LEVEL],[Sum of BI-LEVEL],[Sum of AUTO-MAX BI-LEVEL]
FROM [Rail Volumes]
INNER JOIN [Rail Routing Table]
ON [Rail Volumes].[Volume Rail ID]=[Rail Routing Table].[OrgDest]

I now get the error: The Microsoft Office Access database engine could not find the object ". Make sure the object exists and that you spell its name and the file path correctly.

I could maybe spell it correctly if it had more characters than ". Am I missing quotes that should be somewhere?

x-posted to Queries

[orange]

Get a data model and some test data and make sure it represents the business involved.
Don't get hung up on the details of Access until you have some designed, normalized table structures.

[orange]

See post #11 here (for the concept of stop1 to stop2, stop2 to stop3 etc)
https://www.accessforums.net/showthr...ink-two-tables