Duplicate madness

[jamtrad]

The table below contain 12 different stores (Str) and thousands of Items. I trying to build a supply and demand query whch will show all the stores that have not sold and item but have it demand, and show me all the stores that have sold and items and have none on demand. Kind of a supply and demand. So the result would be store 1 have item 12352 and needs to send it to store 2.



Hopes this makes send

Table headings from data table

SELECT [Tbl Data].Str, [Tbl Data].[Item#], [Tbl Data].[O/H], [Tbl Data].SOLD, [SOLD]-[O/H] AS Expr1, [Tbl Data].[primary Key]
FROM [Tbl Data];

[aytee111]

Query 1 - use the "Totals" button - group by store and item, sum Sold and OH - criteria Sold>0 and OH=0

Query 2 - same - criteria is Sold=0 and OH>0

Now link the two queries by item number, showing both stores and the OH. You are going to have to deal with the duplicates somehow.

[orange]

Also, do NOT use field names with special characters - use only alphas and underscore.