Exclude record if in 2 locations

[Darrell Childress]

I have a table that has the following fields
item
location
qty

Many items are in multiple locations (SBC and COMP). I am trying to
write a query that will produce all records where [location] = COMP and
only those records (items) at location SBC where there is not a record
for that item at location COMP. By the way, it might be worth mentioning
that every item has an entry for location SBC, but only certain items
have an entry for location COMP. In the example below, I would want to
exclude record 1 because it also exists in location COMP (record 2). I
would want to INCLUDE record 3 because it does not exist in location COMP.

Item Location Qty
123 SBC 0
123 COMP 5
254 SBC 6
365 COMP 4

As I'm writing this, it occurred to me that I may have to do this in 2
queries, the first simply setting the criteria of location = COMP and
then do an unmatched query???..and then possibly a UNION query of the 2
Thanks for any help,
Darrell

 

[Tom Ellison]

Dear Darrell:

I'm thinking of joining the table to a query of the same table limiting
itself to Location = COMP, joined on Item. Make this a LEFT JOIN. Then,
when there is NO row for that same item at Location COMP, values from the
joined query will be null.

SELECT Item, Location, Qty
FROM YourTable T
LEFT JOIN
(SELECT * FROM YourTable
WHERE Location = "COMP") Q
ON Q.Item = T.Item
WHERE T.Location = "SBC"
AND Q.Location IS NULL

For this to work, I have assumed that Location is never NULL in the table.

Note that this will not include items that exist in location COMP but have a
zero quantity there. Your post did not mention this possibility. If you
wish to include such items, change the last line to:

AND Nz(Q.Qty, 0) <> 0

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison