I've been stuck on a problem for a few days, and despite furious Google searching, asking other DBAs, and blind attempts to force this to work, I can't seem to combine multiple queries into one.
When I attempt to do this with the wizard, I get the following error message: "You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."
I've also attempted to use SQL to create a union query, but I'm a little over my head when it comes to programming SQL. I'm not trained or certified to do so, and what I've picked up along the way has been through my past experience with Visual Basic and HTML.
My database contains logistics contracted rates. In particular, this section of it has rates for rail shipments. It contains origin, destination, stops from origin to destination (ranges from 0 stops - 2 stops), and volume of shipments for each route. I've attached the database with example data. It has been scrubbed to protect confidentiality. The rates are not real dollar amounts.
My ultimate goal is to create a report with the following: each route, with each stop along the way, with each route multiplied by volume to calculate total cost between each stop, and then total cost for each route.
I would like to create a query to generate this report. With a query, I can enter criteria to make it searchable from a form. For example, if I want to see rates between Mexico City and New York City, I would like to search those two cities and generate results.
There are also multiple contracts for each route, as the rates change on an annual basis.
Any help creating this query and/or report would be much appreciated!
Example.zip