Multiple queries in one report

[Dan]

I have a Query1 that generates a list of product names and quantities
(Quantity1). I have a different Query2 that generates product names and
quantities (Quantity2). I want to make a report that joins the results of the
two queries so that where the product name is the same, Quantity1 and
Quantity2 show on the same line. In other words, I want three columns in my
report: product name, Quantity1, and Quantity2.

There are cases where Quantity1 is null and Quantity2 is not, and vice
versa. I need it to be able to handle all possibilities.

I'm stumped! Can anyone help me with how to set up the query for this
report? Thanks!

 

[Marshall Barton]

I have a Query1 that generates a list of product names and quantities
(Quantity1). I have a different Query2 that generates product names and
quantities (Quantity2). I want to make a report that joins the results of the
two queries so that where the product name is the same, Quantity1 and
Quantity2 show on the same line. In other words, I want three columns in my
report: product name, Quantity1, and Quantity2.

There are cases where Quantity1 is null and Quantity2 is not, and vice
versa. I need it to be able to handle all possibilities.

I'm stumped! Can anyone help me with how to set up the query for this
report? Thanks!

I think this kind of query will work as the report's record
source:

SELECT A.product, A.Quantity1, B.Quantity2
FROM Query1 As A LEFT JOIN Query2 As B
ON A.product = B.product
UNION
SELECT B.product, Null, B.Quantity2
FROM Query1 As A RIGHT JOIN Query2 As B
ON A.product = B.product
WHERE A.product Is Null

 

[Dan]

That was just the answer I needed. Thanks very much for your help!