Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Ken

IS-IT--Management
Jul 13, 2005
68
CA
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]

Code:
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) ?
 
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
 
Hi dhookom,

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

Thank you again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top