Combine all records from two tables to include related and unrelat

[Scott]

I have two queries and I need to combine all the data in one query with all
the data in the other query. Query 1 is qrySalesPreviousPeriod. Query 2 is
qrySalesCurrentPeriod. The queries are joined by CustomerCode. The query
joined by CustomerCode only returns data for those customers that have data
in both queries. It does not return data if the customer exists in only one
period or the other. How do I get the query to include all customer in both
periods and show the data as null in the period in which there is no data?

Thanks,

Scott

 

[Marshall Barton]

I have two queries and I need to combine all the data in one query with all
the data in the other query. Query 1 is qrySalesPreviousPeriod. Query 2 is
qrySalesCurrentPeriod. The queries are joined by CustomerCode. The query
joined by CustomerCode only returns data for those customers that have data
in both queries. It does not return data if the customer exists in only one
period or the other. How do I get the query to include all customer in both
periods and show the data as null in the period in which there is no data?

That's pretty vague, but I try to take a stab at it.

SELECT qry1.f1, qry1.f2, qry1.f3, qry1.f4, qry1.f5,
qry2.f2, qry2.f5
FROM qry1 LEFT JOIN qry2
ON qry1.CustomerCode = qry2.CustomerCode
UNION
SELECT qry2.f1, qry1.f2, qry2.f3, qry2.f4, qry1.f5,
qry2.f2, qry2.f5
FROM qry1 RIGHT JOIN qry2
ON qry1.CustomerCode = qry2.CustomerCode