Combining results from unrelated tables

[jwreding]

First of all, I did search this forum for all threads with "combine" in the title, but couldn't find exactly what I'm talking about here. If it has been discussed before, please bear with me.

The situation is as follows.

The scenario:

I work at a freight brokerage company. We currently collect and manage three different types of rates in three spreadsheets. I have prepared and normalized all of the data and created a table in our DB for each collection of rates.

Before I list the tables and relationships, let me preface it by saying that there are business reasons for these tables being laid out the way they are, so making too drastic of changes to them isn't very feasible.

The tables:



As indicated, I have three tables (primary key is *, foreign keys all have ID in name)

tblCustLanes

*LaneID
CustomerID
VendorID
ConsigneeID
Miles
CustomerRate
FSC
TCU
Loop
XStop
Notes
DateEntered

tblRouting

*RoutingID
LaneID (tblCustLanes)
FleetID
Priority
FleetRate
FSC
EmployeeID
ContactID
Notes
DateEntered

tblMRD

*MrdID
CustomerID
FleetID
OriginCity
OriginST
DestCity
DestST
FleetRate
EmployeeID
ContactID
Notes
DateEntered

Relationship: 1 tblCustlanes record to n tblRouting records

tblRouting contains rates from our carriers for business that we actually do. tblCustLanes has the information pertaining to the customer, and we can have many carriers for one lane, thus many tblRouting records for one tblCustLanes record.

The MRD contains "fishing" rates, i.e. rates that carriers have provided us on potential lanes, but we do not have any information other than city-city. Since the lane is not "real" and contains no other information, we do not want to include it in tblCustLanes for multiple reasons.

This situation would be fine in and of itself. However we wish to see rates from both tblRouting and tblMRD combined into one list. This was possible when everything was in Excel, because I just had a macro that would copy/paste the info from both spreadsheets into one, and leave blanks where there was no information. Obviously a query doesn't work that way, so I'm at a loss as to how to mimic this functionality in Access. Here is what I want the end result to be:

CustomerName
FleetName
OriginCity
OriginST
DestCity
DestST
FleetRate
EmployeeName
ContactName
Notes
Date

The kicker is that the cities and states would come from the city/st fields in tblMRD, but come from separate Vendor and Consignee tables for tblCustLanes.

I don't know much about union queries, but don't think I can accomplish this with one, since the tables really don't have anything in common, and both are using related tables. Can anybody help me with this? I really need to find a way to create results similar to the way it is currently done in Excel, otherwise I have no argument for bringing it into the DB.

Thank you for reading this exhausting post!

[rpeare]

can you give an example of what your data would look like in each table and what you want the output to look like. I think a union query will do what you want it's just a matter of figuring out what you want to see. You're referencing fields in your output that you don't have in your examples but that's understandable (customer name would be on a customer table for instance). Your tblRouting and tblMRD tables do have some fields in common but do you want to list ALL routing and ALL MRD with this query or do you want to only show items for a specific employee ID etc.

[jwreding]

rpeare...thanks. Yes, I meant to imply that I'd be getting several fields from other tables, such as tblCustomers (using CustomerID of MRD and Routing).

I want to start by listing ALL Routing and MRD records. Once I get that figured out, I can start playing with grouping, criteria, etc.

Where it seems to be breaking down (at least mentally for me) is combining the city/st data, since in the MRD it is a text field and in the Routing it comes from foreign tables. It might just be because I'm not at all familiar with union queries.

Here is a sample of one record in each table, and the desired output:

tblCustLanes
*LaneID: 1042
CustomerID: 2034
VendorID: 1046
ConsigneeID: 1503
Miles: 234
CustomerRate: $450
FSC: Y
TCU: Y
Loop: N
XStop: N
Notes: Stuff
DateEntered: 3/1/10

tblRouting
*RoutingID: 1001
LaneID: 1042
FleetID: 1034
Priority: 3
FleetRate: $300
FSC: Y
EmployeeID: 3904
ContactID: 1203
Notes: Blah
DateEntered: 5/5/11

tblMRD
*MrdID: 1050
CustomerID: 3402
FleetID: 2305
OriginCity: Indianapolis
OriginST: IN
DestCity: Columbus
DestST: OH
FleetRate: $250
EmployeeID: 3409
ContactID: 2405
Notes: Hi
DateEntered: 6/1/11

Now, I want to get all records from tblRouting and tblMRD. Notice each tblRouting record has a foreign key for LaneID, which relates to the LaneID of tblCustLanes...which in turn has VendorID and ConsigneeID. These relate to a tblVendors and tblConsignees, respectively. Each of those tables has a city/st. So I want to populate the OriginCity and OriginST of the output with the VendorCity and VendorST, and populate DestCity and DestST with ConsigneeCity and ConsigneeST.

Desired output:

qryCombinedRtgMRD

This would be from tblMRD record 1050:

CustomerName: Bob's Widgets
FleetName: ABC Trucking
OriginCity: Indianapolis
OriginST: IN
DestCity: Columbus
DestST: OH
FleetRate: $250
EmployeeName: Jon
ContactName: Bob
Notes: Hi
DateEntered: 6/1/11

This would be from tblRouting record 1001, and use info from tblCustLanes record 1042 (I'm not going to show all related tables for clarity's sake, so assume the below information comes from these tables)

CustomerName: Carpet World
FleetName: On-Time Transport
OriginCity: Chicago
OriginST: IL
DestCity: Milwaukee
DestST: WI
FleetRate: $300
EmployeeName: Jeff
ContactName: Joe
Notes: Blah
DateEntered: 5/5/11

Again, these different tables, although containing similar information, serve two very different purposes, so combining them is not an option (except in the special case, which is the basis for needing this query!)

Thanks!

[rpeare]

so assume for the sake of argument that this is your entire dataset (the three examples you have showed.

What do you want your OUTPUT to look like?

[jwreding]

Sorry if I wasn't clear...the output should be the two records at the bottom. I edited the post to put a bold header on that section since I got long winded again.

[rpeare]

Here's an example.

I left you three queries, one is the actual union query, but you can see how I built the portions of it with the two UNIONBASE queries

[jwreding]

rpeare you are a consummate genius. Thank you so much for taking the time to create this for me.

So, if I'm understanding the concept right, I just need to mask the resultant field names in each query to have the same name, then combine them with the union query.

Again, union queries are foreign to me (to be honest, SQL in general is not yet a strong suit), but I can work through that and learn more about them. This definitely gives me a good foundation to work from.

Thanks again!!

[rpeare]

I think that the union query will inherit the field names from the FIRST portion of the query regardless of what you do (I'm not certain). I just name everything the same because it helps me match up the portions of the union query and what they are supposed to represent (because I'm old and I need these visual reminders)