I need to display each product per row and compare number side by side

[Ken]

Hi,

There 2 tables with same Product name with different numbers.
Need compare side by side as below or .

[pre] Table1 Table2
ProductName Qty ProductName Qty Diff (Table1-Table2)
1) A 5 5
2) A1 2 A1 4 -2
3) B 7 B 4 3
4) B1 5 B1 5 0
5) C 4 -4
6) D 2 D 2 0

or display
Product Name Qty_Table1 Qty_Table2 Diff(Table1-Table2)

[/pre]

SQL Access Code

Select T1.ProductName as T1_Product,     T1.Qty as T1_Qty, 
                   "" as T2_Product,         "" as T2_Qty  
from   Table1 as T1
Union  
Select             "" as T1_Product,         "" as T1_Qty, 
       T2.ProductName as T2_ProductName, T2.Qty as T2_Qty  
from   Table2 as T2

The above Access SQL code displays 2 rows for each item instead of 1.

Is there a way to display 1 row for each product name and compare side by side or
display 1 column for Product Name with separate column T1_Qty, T2_Qty and Difference (T1_Qty - T2_Qty) ?

 

[dhookom]

I would start with creating a union query like;

SELECT DISTINCT ProductName FROM Table1
UNION
SELECT DISTINCT ProductName FROM Table2

Then LEFT JOIN table1 and table2 to the UNION query to get the quantities and differene.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Ken]

Hi dhookom,

Thank you - first union then left join to UNION worked.

Thank you again.