Combining Three Queries into one

[Guest]

Good morning

I have three queries at the moment and would like to create a fourth one to combine the info into one location. Each query contains three columns (year, week, total). However, not each query has the same entries...thus one query may have an entry for 2003 week 12 while the two others may not. In some cases there are entry for a week in all three queries

How do I go about combining them under one query

The wanted result would resembl
Year Week Qry1Count Qry2Count Qry3Coun
2003 12 0 0
2003 15 1 0
2003 16 5 9
....

Thank yo

Danie

 

[Chris]

Create a new query...

Select Year, Week from qry1
union
Select Year, Week from qry2
union
Select Year, Week from qry3


save this as qryA
This gives a list of all Year/week combinations with
duplicates removed.

Then use this query as the final query...

SELECT qryA.Year, qryA.Week, qry1.Qry1Count,
qry2.Qry2Count, qry3.Qry3Count
FROM ((qryA LEFT JOIN qry1 ON (qryA.Week = qry1.Week) AND
(qryA.Year = qry1.Year)) LEFT JOIN qry2 ON (qryA.Week =
qry2.Week) AND (qryA.Year = qry2.Year)) LEFT JOIN qry3 ON
(qryA.Week = qry3.Week) AND (qryA.Year = qry3.Year);

-----Original Message-----
Good morning,

I have three queries at the moment and would like to

create a fourth one to combine the info into one
location. Each query contains three columns (year, week,
total). However, not each query has the same
entries...thus one query may have an entry for 2003 week
12 while the two others may not. In some cases there are
entry for a week in all three queries.

How do I go about combining them under one query?

The wanted result would resemble
Year Week Qry1Count Qry2Count Qry3Count
2003 12 0 0 4
2003 15

1 0 7

2003 16

5 9 2

 

[Guest]

Thank you Chris... that was exactly what I was looking for!