Select only records that are left.

[LindaC]

I have a tbllocation which has records A1a, A1b, A1C. I also have a
tblarchive with records and I select the location. When adding a new record
in a form I have to select a location but only want to see locations that are
not used by other records already. Was doing this awhile back but still
can't get it. The primary key in tbllocation is the first and only field and
is a one to many relationship with tblarchive. Any step by step would be
appreciated.

 

[Jason Lepack]

SELECT
tblLocation.loaction
FROM
tblLocation
LEFT JOIN tblArchive
ON tblLocation.loaction = tblArchive.location
WHERE
tblArchive.location Is Null;

This query selects all of the items in tblLocation.loaction that don't
appear in tblArchive.location.

Paste this code using SQL View of the Query Designer.

Cheers,
Jason Lepack