I have a table named "Services" which has a "ServicesID" field as a
key, GuestID, ServicesType and another field called "Bed". When a
guest stays in our shelter, a record is produced in Services that
includes the GuestID, ServicesType (in this case "Men's Dorm") and a
bed number. The bed number is related to a table called
"tblLookupBeds" that has a BedID (key), BedType and BedDescription so
users can select the bed from a drop down list.

Sometimes, new guys don't get a bed number assigned when they first
come in until all the "regulars" are assigned. Therefore, I have a
query that lists all the guys who do not have beds and lets the user
assign beds to them. However, now I realize I need a way to produce a
list of available beds for that day.

How can I get a list of the bed numbers (BedType) that have NOT been
assigned?

Use a subquery to select the beds that are not assigned for the date.

Something like this:

SELECT BedID FROM tblLookupBeds
WHERE NOT NOT EXISTS
(SELECT Bed FROM Services
WHERE Services.Bed = tblLookupBeds.BedID
AND Services.ServiceDate = Date())

If subqueries are new, here's an introduction:
Subquery basics: Identifying what is NOT there
at:
http://allenbrowne.com/subquery-01.html#NotThere

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RussCRM" <srusskinyon (AT) gmail (DOT) com> wrote