I want to combine the results of two queries

[rb9999]

I work for a delivery company and need to combine the results of two queries into one dataset.

They are:

Delivery Query
1 - Center Number
2 - Vehicle Number
3 - Employee Name
4 - Data Date
5 - Sum of Delivery Packages
6 - Count of Delivery Stops

Pickup Query
1 - Center Number
2 - Vehicle Number
3 - Employee Name
4 - Data Date
5 - Sum of Pickup Packages
6 - Count of Pickup Stops

Each of these are fine on their own, but....there has to be a but...I want to combine both of these into one dataset which shows:
1 - Center Number
2 - Vehicle Number
3 - Employee Name
4 - Data Date
5 - Sum of Delivery Packages
6 - Count of Delivery Stops
7 - Sum of Pickup Packages
8 - Count of Pickup Stops

The problem is that using the relationships in Access I can only say, use the data from both tables that match, or use all the data from one and only those that match in the other. In this case, I want to combine the info and use all data from both tables. A vehicle may have deliveries and no pickups, or can have pickups and no deliveries. If I set the relationship up with one of the arrow join lines (for example from Delivery table to Pickup table), my dataset won't contain any information for a vehicle if it had pickups but no deliveries.

Hope this makes sense.

 

[LonnieJohnson]

Have you looked at MS Access help at "Union Queries"

SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1
UNION SELECT FIELD1, FIELD2, FIELD3 FROM TABLE2

The above will return a combined record set of the two tables. The fields have to match as far as types and there has to be the same number of fields in each select statement. This is all explained in the help files.




ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[rb9999]

Yes, I am familiar with Union queries, however, if I did a union query with this then my results will be all jumbled wouldn't they? I don't have a lot of experience with them, but I can do basic ones. I used one in this case.

For example, there are three different data elements for vehicle number. Vehicle1, Vehicle2 and Vehicle3. I did a union query to put all three vehicle numbers into one data field and this worked fine. But in my case, I want to add two more fields to my query.

With my understanding of union queries, I would be combining delivery data with pickup data into one new field and I wouldn't be able to differentiate the two.

Maybe union queries can do more than I know. I'll have to read up on them.

Thanks for the response.

 

[LonnieJohnson]

You could add fake columns in one table to make up for a column in another table. You could also create a column to identify which record came from which table. Both examples are in the following syntax:

SELECT 'T1' as [WHICH TABLE], FIELD1, FIELD2, FIELD3, SPECIALFIELD FROM TABLE1
UNION SELECT 'T2' as [WHICH TABLE], FIELD1, FIELD2, FIELD3, NULL AS SPECIALFIELD FROM TABLE2

T1 AND T2 AS [WHICH FIELD] WILL CREATE A COLUMN IN YOUR RESULTS TELLING YOU WHICH TABLE THE RECORD IS FROM. ALSO I HAVE A SPECIAL FIELD IN TABLE ONE THAT IS NOT IN TABLE TWO, SO FOR THIS COLUMN IN TABLE TWO I HAVE NULL AS SPECIALFIELD.



HOPE THAT ANSWERS YOUR Q

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!