Query to show what is not in a table 1

[oticonaus]

I have two tables, Salespeople and WorkingData. Both have a SPCode field. I want a query to tell me the records in WorkingData that are not in Salespeople. So the result from the data belwo would be JE, SW

Salespeople.SPCode
AB
DK

WorkingData.SPCode
AB
DK
JE
SW

I was thinking of doing a Dlookup on every record and then testing the result, but I am 98% sure there is a MUCH better way?

 

[dhookom]

Use the Find Unmatched Query Wizard.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[mordja]

oticonaus,

Or you could use the SQL NOT IN statement ie

Select SPCode from WorkingData
Where SPCode NOT IN (Select SPCode from Salespeople)

Mordja