query for values not in use

[ArielZusya]

I've got a table (tblSeat) which just holds the numbers associated with
seats. There are 200 seats. Record #1 holds Seat 1, #2 2, and so on. I've
then got another table (tblEvents) that holds events associated with people
listed in a third table (tblMain). Each person in tblMain is also associated
with a reference number (tblRefNum); there is one RefNum for a group of
people in tblMain. I record into tblEvents that a person in tblMain who is
associated with a particular RefNum was just seated in seat #. There are
many events in tblEvents for each person in tblMain (one:Many relationship).
There are many people in tblMain for each RefNum in tblRefNum (One:Many
relationship).

For purposes of this question, let's say person1 in tblMain is in seat 6,
person2 in tblMain is in seat 8, and person3 in tblMain is in seat 12 and all
three of these people are associated with ref num 206CBR2985. No other
people are assigned to seats (assignment, incidentally, is action 4 in
tblAction) and as a result, the rest of the seats are unassigned.

I can figure out how to query to return all the people associated with
206CBR2985 who have been assigned a seat (qryShowAllPeopleWithSeatsAssigned):

SELECT tblAction.Seat
FROM tblMain RIGHT JOIN tblAction
ON tblMain.ID_Main=tblAction.person
WHERE (((tblAction.Seat) Is Not Null)
AND ((tblAction.Event)=4)
AND ((tblMain.RefNum)="206CBR2985"));

and in this case it shows me three seats: 6, 8, and 12. What I'd like is
for it to show me a list of seats that are not assigned... in other words
show me a list that has 1, 2, 3, 4, 5, 7, 9, 10, 11, 13, 14, ... 199, 200. I
tried querying as follows (qryShowAllEmptySeats):

SELECT tblSeat.Seat
FROM tblSeat RIGHT JOIN qryShowAllPeopleWithSeatsAssigned
ON tblSeat.Seat <> qryShowAllPeopleWithSeatsAssigned.Seat;

But it gave me strange results. It returned 597 values. 1-197 were 1, 2,
3, 4, 5, 7, 9, 10, 11, 13, 14, etc. just as I wanted. But, 198-397 and
398-597 were all 200 seats repeated twice. What am I doing wrong? How do I
fix this? Thanks for your help!

Just in case it's helpful:

tblMain
.ID_Main, AutoNumber, PrimaryKey
.FirstName, text, First Name of Person
.LastName, text, Last Name of Person
.UserIdentifier, text, The person's state issued ID number
.RefNum, text, Unique ref num for group

tblRefNum
.RefNum, text, PrimaryKey, selected distinct refnums from tblMain
.BatchNum, text, PrimaryKey, every time a new refnum is added
this goes up sequentially

tblSeat
.Seat num, PrimaryKey, fixed list of seat numbers 1-200

tblAction
.ID_Action, AutoNumber, PrimaryKey
.Person, num, link to person in tblMain.ID_Main
.Event, num, link to event
.Seat, num, link to seat

tblEvent
.ID_Event, AutoNumber, PrimaryKey
.Event, text, Wait, Seated, Moved, etc.

Relationships (one : many):

tblMain.ID_Main : tblAction.Person
tblMain.RefNum : tblRefNum.RefNum
tblAction.Event : tblEvent.ID_Event
tblAction.Seat : tblSeat.Seat

 

[John Spencer]

Try the following

SELECT tblSeat.Seat
FROM tblSeat LEFT JOIN qryShowAllPeopleWithSeatsAssigned
ON tblSeat.Seat = qryShowAllPeopleWithSeatsAssigned.Seat
WHERE qryShowAllPeopleWithSeatsAssigned.Seat is NULL

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ArielZusya]

Eureka! That is perfect! Thank you so much!

Try the following

SELECT tblSeat.Seat
FROM tblSeat LEFT JOIN qryShowAllPeopleWithSeatsAssigned
ON tblSeat.Seat = qryShowAllPeopleWithSeatsAssigned.Seat
WHERE qryShowAllPeopleWithSeatsAssigned.Seat is NULL

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..