Combine like data from multiple queries into single field

[twinspar]

I am trying to create a query from several "preliminary" queries. Each prelim
query is finding it's data in a different source table (partscost, laborcost,
etc) The prelim queries each have a record UID field, a dateposted field, a
tablesource identifier (SID), and a cost field. I need to combine data
retreived from the prelim queries into a simple TotalCostQry, grouped by UID.
The UID is a joined field from the primary WorkOrder table. See sample data:

Prelim_PartsQry
UID dateposted SID partscost
003 20060801 P 3.52
003 20060803 P 9.27
005 20060804 P 15.75
005 20060808 P 8.29

Prelim_LaborQry
UID dateposted SID laborcost
003 20060801 L 63.24
005 20060808 L 23.72

I want my totals query to return a result like:

TotalCostQry
UID dateposted SID cost
003 20060801 P 3.52
003 20060801 L 63.24
003 20060803 P 9.27
005 20060804 P 15.75
005 20060808 P 8.29
005 20060808 L 23.72

The big challenge here for me is finding a way to combine all date entries
from prelim queries into a single dateposted field.

 

[John Spencer]

Take a look at UNION queries. They cannot be created with the query grid,
but one should be the answer to your problem.

In the SQL view (Menu: View: SQL) enter something like the following

SELECT * FROM Prelim_PartsQry
UNION ALL
SELECT * FROM Prelim_LaborAry
UNION ALL
SELECT * FROM ...

IF you use this syntax, all of the queries must return the same number of
fields and the fields should be similar in type and order.

You can specify the fields if some of the queries have a different order or
different number of fields and you can even add in an identifier to specify
the factor (CostType) in the query below.
SELECT UID, dateposted , SID, partscost as Cost, "Parts" as
CostType
FROM Prelim_PartsQry
UNION ALL
SELECT UID, dateposted , SID, LaborCost , "Labor"
FROM Prelim_LaborAry
UNION ALL
SELECT UID, DatePosted, SID, Overhead , "Overhead"
FROM ...

 

[twinspar]

Thank you, John. Looks like I'll have to restructure my prelim queries a bit,
but seeing is believing. I greatly appreciate the thoroughness of your reply,
it worked just as you promised. Cross your fingers for me though - SQL is new
territory for me! Don't be surprised if you see me back here soon.