Strange query results

[newfrontiers]

Help. I have a brain lock. I am trying to compare values within two tables by using a query.

Table A has the following fields (with sample info)
-Item XYZ
-Qty 100
-Desg F
Table B has the same fields (with sample info)
-Item ABC
-Qty 50
-Desg S
I want a query to show the quantity of any item in either table. Basically, I am looking for the query to return:

ITEM QTYF QTYS
ABC 0 50
XYZ 100 0

The problem I have is the join between the tables. I can get any items that are in both tables but cannot get the above when an item is in one table and not in another table. When I try to add a third table that has all ITEMS and then link this table individually to TABLEA and TABLEB I get incorrect data.

Any help with this problem would be appreciated.

Thanks,

John

 

[SuicidED]

Hi John

Try this. Create a union query, "select * from TableA UNION select * from TableB;", then do a crosstab on this query 'TRANSFORM Sum(Query1.Qty) AS [The Value]
SELECT Query1.Item
FROM Query1
GROUP BY Query1.Item
PIVOT Query1.Desg;'

Can't think how to show zero for the blanks.

Eddie