Query to get records whose children are all marked “No”.

[CharlieVictor102]

This is my first post here. I am a real NOOB when it comes to Access. Sorry if this question has been asked before but I just don’t know what key words to search for to find an answer to my question.
I have a table of equipment checkout requests (tblRequests). This has a many-to-many relationship to a table of equipment (tblEquipment). These are linked together with a junction table (tblRequests_Equipment). The tblRequest has a Yes/No field that indicates if the request is still active (that request’s equipment is still reserved and the request has not been dispositioned). The tblEquipment has a Yes/No field to indicate if the equipment is still reserved. The following is an example of the data:
tblRequests_ID RequestName IsActive tblEquipment_ID EquipmentName IsReserved
1 Request1 No 1 Equipment1 No
2 Request2 Yes 2 Equipment2 Yes
3 Request3 Yes 3 Equipment3 No


3 Request3 Yes 4 Equipment4 No
3 Request3 Yes 5 Equipment5 No
I would like to have a query that lists only the requests that are active (tblRequest.IsActive == Yes) AND all of their reserved equipment has been unreserved (tblEquipment.IsReserved == No). This is so that the user can know when a request can finally be dispositioned. With the example data above, only “Request3” should show up in the results of the query.
I will try to attach my example database to this post. I'm using Access 2007 but I saved it as a .mdb file so I could attach it.

Curt

[maximus]

Due to some reason I was not able to open the mdb file that you had uploaded. But What I understand from your thread is that you want to create a query and add a criteria "NO" to a Yes/No Field. Yes/No Field are Boolean Type and When you Check a Yes/No Field it is = True and When Un-checked it is = False

So in the criteria of your Query instead of putting "No" or "Yes" put False and True and with out the Quotes not ("True" or "False")
if this solves your problem mark the thread solved.

[CharlieVictor102]

Thanks Maximus but that was not exactly what I was looking for.

The forum would not let me attach a .accdb (Access 2007) file.

I know how to set up a criteria so I can filter if tblEquipment.IsReserved is True or False but what I'm looking for is to only have a row for a tblRequest when ALL of it's related tblEquipment (all the equipment checked out via a Request) are flagged as tblEquipment.IsReserved equals False. In the example I gave, "Request3" has three pieces of equipment associated with it ("Equipment3", "Equipment4", and "Equipment5"). All three of those pieces of equipment are no longer reserved (tblEquipment.IsReserved is False). I would like a single result (row) from the query that lists "Request3". Note that "Request1" is not active and its equipment ("Equipment1") is not reserved (should not appear in the result). Also note that "Request2" is active and its equipment ("Equipment2") is still reserved so "Request2" should not appear in the result.

Is there a way that I can Count() the number of related Equipments that a Request has? Then can I Count() the Requests related equipment where tblEquipment.IsReserved is False? When these two calculations are equal, then that is the tblRequest record that I want. Note that there is a junction table in between (because a piece of equipment might be associated with an old Request that is no longer active).