CODEWITHSUNDEEP
sqlms-access
KurtB89
KurtB89

Reputation: 49

Access SQL query: Return records with two or more checkboxes checked

I have a table in my database that has 11 fields that are in the format of True/False. These are displayed using a checkbox display control as specified in the design view of the table.

I'm trying to write a query that selects all fields that have two or more checkboxes ticked.

The query I have at the moment is as follows:

SELECT * FROM resources 
WHERE literacy=-1 OR numeracy=-1 OR poverty=-1 OR behaviour=-1 OR ani=-1 OR RPAE=-1 OR CAD019=-1 OR leadership=-1 OR curriculum=-1 OR assessment=-1 OR wellbeing=-1 
AND literacy=-1 OR numeracy=-1 OR poverty=-1 OR behaviour=-1 OR ani=-1 OR RPAE=-1 OR CAD019=-1 OR leadership=-1 OR curriculum=-1 OR assessment=-1 OR wellbeing=-1;

However, this simply returns all the records with one or more checkboxes ticked.

Help greatly appreciated.

Thanks!

Upvotes: 2

Views: 550

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123409

You could try adding the values together and then checking the sum: if it is less than -1 then more than one item has been selected. Something like this:

SELECT * FROM resources 
WHERE (literacy + numeracy + poverty + behaviour + ani + RPAE + CAD019 + leadership + curriculum + assessment + wellbeing) < -1

Upvotes: 2

Related Questions