should be so easy! - trying to combine two qrys!

[Guest]

I have two queries i am trying to combine, Each query has two columns, date
(unique) and a cash value.

I am trying to get a single query that will display all the dates in one
column, and then the corresponding cash values for that date from query 1
and query2.
so far, so simple! Howver, some dates may occur in qry 1 but not in qry 2,
and some other dates may occur in qry 2 but not qry 1 - and it is essential
my resultant qry makes a row for every date used, regardless if it is in qry
1 or qry 2!

I've tried simple joins, but of course it will always miss dates from one of
the two queries!

eg
qry 1 may contain:
1st Nov 50
2nd Nov 10

qry 2 may contain

1st Nov 800
3rd nov 100
4th nov 550

and i am trying to get: -

Date / qry 1 / qry 2

1st Nov / 50 / 800
2nd nov / 10 / 0
3rd Nov / 0/ 100
4th nov / 0 / 550

any ideas? i seem to be making something simple very complicated!!

 

[Douglas J. Steele]

Since a given date may be in qry1 but not in qry2, you have to do separate
queries, and union them together:

SELECT Qry1.DateField, Qry1Value, Nz(Qry2Value, 0)
FROM Qry1 LEFT JOIN Qry2
ON Qry1.DateField = Qry2.DateField
UNION
SELECT Qry2.DateField, Nz(Qry1Value, 0), Qry2Value
FROM Qry2 LEFT JOIN Qry1
ON Qry1.DateField = Qry2.DateField

This assumes that there will never be more than one row in each query for a
given day: if qry1, say, has two rows for 1st Nov, you're going to get two
rows in the resultant query.