Union Qry - Need to use Distinct? 1

[Zed5010]

Need help: I have 2 tables : ORDERS & PURCHASES and need to calculate the difference between items purchased and ordered. Both tables contain fields (STYLE and COLOR). I need to extract records from the PURCHASES table that are not in the ORDERS table ie. combination of style and color not found in ORDERS. Certain style/color combinations were not ordered.


Orders QRY

Style Color Units
A Blue 1
A White 1
A Green 1
B White 1
B Red 1
C Blue 1

Purchases QRY

Style Color Units
A Black 4
B White 3
B Red 2
B Green 1
C Blue 9

Desired QRY
(All Orders incl. purchases of colors not in Orders)

Style Color Units
A Black 4
A Blue -1
A White -1
A Green -1
B White 2
B Red 1
B Green 1
C Blue 8


Thanks

 

[PHV]

SELECT P.*
FROM Orders O RIGHT JOIN Purchases P
ON O.Style = P.Style AND O.Color = P.Color
WHERE O.Style Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zed5010]

Thanks a million PHV!!!!