Query on multiple yes-no field type

[nemmeh]

Hey everyone,

left me give you a brief rundown on what I've got going on. I've got a table with 1000 records, each record contains last name, first name, address, etc. Then I have a series of yes/no (checkboxes) for each record. These checkboxes are "LA Claimant","MS Claimant","Relation","Worker".

Ok now, each record could have none selected or it could have 2 or 3 out of the available options checked. The problem I'm having is...

How would i construct a query that basically excludes any records where any of the checkboxes are yes?

So say I have 4 checkboxes per record, if checkbox 1 is yes, but 2,3,4 are no. I want that record excluded. The same goes if any of the other boxes are checked.

I'm completely in the dark on this one and just can't seem to figure it out. If someone could help out with the sql on this one I'd appreciate it.

Thanks... I hope this made sense.

 

[JoanneM]

This is going to seem daft but here goes

set up your query with the table and the check box fields
together.

In the Criteria cell of the appropriate field type true
in the 'or:' lines of the subsequent fields put true.

so you have a line of true's going diagonally down the grids.

This should bring all the records with yes. I know this is what you don't want - but many times by getting what you don't want can lead you to what you do.

Create another query add the first query you've just created to it. Add the original table; join the two on their relevant key fields selecting all from the query and matching those from the table.

Put the Key field from the table into the grid and put Is null in the criteria grid.

You now have all records that don't have a yes in the check boxes.

hope this helps

JO

 

[scriverb]

This SQL when copied and pasted into a new query will give you the selection that you want:

Select A.*
FROM tblYourTableName as A
WHERE A.[LA Claimant] = False and A.[MS Claimant] = False and A.[Relation] = False and A[Worker];

Just update the red code(table name) with the name of your table.

Post back with any questions that you may have.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[nemmeh]

Thank you very much, it works flawlessly scriverb.
Had to fix a typo, but besides that perfecto..

The correction is listed for anyone that ends up finding this thread through a search.

A[Worker]; should be A.[Worker];

Also many thanks to JoanneM, as well.

 

[scriverb]

nemmeh: After your last posting I see that I also left off the last expression comparison to False. See red. Here should be your final SQL for the query:

Select A.*
FROM tblYourTableName as A
WHERE A![LA Claimant] = False and A![MS Claimant] = False and A![Relation] = False and A![Worker] = False;

Glad that I could help you on this one.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[nemmeh]

hehe, yep.. I completely forgot I had to add that part on.