Comparing two tables to find missing items

[Jerseynjphillypa]

I have to two tables called Location and Sell.



The Location table list location of the item. The Sell table list item I Sell.

How would I show item that I don't sell by item and location

Would I use Is Null

If it is Null, then how can I show "NO" in the table

[RuralGuy]

Have you tried out the "Unmatched Query Wizard"?

[Jerseynjphillypa]

Thank you for your suggestion

Yes that's how I got "Is Null", but now I would like for it to say NO or a message like I don't sell this item

[RuralGuy]

Since you solved your issue,would you care to share your solution with others that read this forum?

[Jerseynjphillypa]

Sorry, i taught I was able to solve it myself

This is what I did, iif(isnull([Sell]),"NO",[Sell]) but it still shows the Sell Items.

I just care about the items I don't sell.

So I would like to see only Null values and replace it with a text string

[orange]

Try the UNmatched query wizard as Allan suggested - - and use Is Null to select onlythose records that "haven't been Sold".

Format along this line

SELECT TablA.Fld1, TablA.Fld2, TablA.Fld3, TablA.Fld4
FROM TablA LEFT JOIN TablB ON TablA.id = TablB.id
WHERE (((TablB.id) Is Null));

[Jerseynjphillypa]

Thanks for the help orange, I tried what Allen suggested but now I would like to replace the Unmatched items with a text field to say "NO"

[orange]

Show your query sql.

Go to your query window, select SQL view; copy it and paste it in your post.

[Jerseynjphillypa]

SELECT Location.Item, Location.Location, Sell.SellFROM Location LEFT JOIN Sell ON Location.Item = Sell.Item
WHERE (((Sell.Sell) Is Null));

[orange]

I have 2003 so can not open your accdb file to see what your table layouts are.
Normally you would have tables Location and Sell each with a Primary key.

Location may look like this

Location(id autonumber, Item text, Location text,...other fields specific to Location..)
Sell(id autonumber, Loc_FK number, Salesman text, ..other fields specific to Sell...)

Your tables would be related by a relationship between Location.Id and Sell.Loc_FK which would mean "Locations that has been Sold can be found by
using the FK field in SELL to find records in Location.

revised query using my tables

SELECT Location.id, Location.item, Location.Location, IIf(IsNull([sell_FK]),"NO",[Sell_FK]) AS MyDisplayValue
FROM Location LEFT JOIN Sell ON Location.id = Sell.sell_Fk
WHERE sell.sell_fk is null;

The attached jpg shows the table values and the result of the revised query.

[Jerseynjphillypa]

Thank you for your help orange and posting an example

[orange]

You're welcome. Good luck with your project.