Selecting Items Not In Another Table...

[Sean Massey]

I'm trying to build a query in Access that will compare data in two
tables and show me a list of items that aren't in use. This list will
later be displayed in a combo box when new records are created.

My database is for a real estate company, and it contains the PIN
numbers that are used when clients call about a house we're marketing.
The list of PIN Numbers is stored in one table, and assigned PIN numbers
are stored in a field on the Property table. Properties that the agency
is currently listing are denoted by a Yes/No field.

The code I have is as follows:

SELECT PIN.PIN_Number
FROM PIN LEFT JOIN Property ON PIN.PIN_Number = Property.Property_PIN
WHERE (((Property.Property_Active_Record)=Yes) AND
((Property.Property_PIN) Is Null));

However, this returns no values. Is there a better way to compare two
tables and return items that are listed in one but not the other?

 

[kingston via AccessMonster.com]

Use a subquery:

SELECT PIN_Number
FROM PIN
WHERE (PIN_Number NOT IN (SELECT Property_PIN FROM Property WHERE
Property_Active_Record=-1);

 

[Sean Massey]

Use a subquery:

SELECT PIN_Number
FROM PIN
WHERE (PIN_Number NOT IN (SELECT Property_PIN FROM Property WHERE
Property_Active_Record=-1);

Worked like a charm. Thank you.