Solved Counting multiple checkboxes (2 Viewers)

dobseh

New member
Local time
Today, 05:04
Joined
Jul 9, 2022
Messages
10
Hi

I have a table that has a (text) name field and five checkbox fields in it (representing days of the week Mon-Fri), which is part of a class booking system where people can pick which day(s) they want to attend. I need a way of counting the number of checkboxes each record has ticked in two different ways:

1) Count the number of days for each record individually.

For example:

User A has Monday, Tuesday and Friday checked, I need this to return the count as 3
User B has Wednesday ticked, I need this to return a count as 1
User C has Wednesday and Thursday checked, I need this to return a count as 2.

I can easily enough count records with 1 check box ticked using OR's and 5 checked using AND's, but I don't know the best way to enumerate all the possible permutations of 2, 3 and 4 tick boxes without writing multiple select statements for each variation.

2) Count the total number of people who have each permuation of checkboxs ticked.

For example:
There are X people with Monday and Friday checked
There are Y people with Tuesday, Wednesday and Thursday checked
There are Z people with Monday, Tuesday, Wednesday and Thursday checked

Counting the number of people with all five days checked I can do with AND's, but again the way to simply do all the others is beyond my understanding at this point!

The first of the two parts of this question is by far the more important, if the second is not easily solvable than I can live with that. I'm not a programmer or a SQL expert, my background is IT infrastructure, so please be gentle!

many thanks in advance.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:04
Joined
Sep 21, 2011
Messages
10,853
Your structure is all wrong.
You should have a record for each day, with a field saying what day it is. Then a simple query gets you your information.
 

dobseh

New member
Local time
Today, 05:04
Joined
Jul 9, 2022
Messages
10
Your structure is all wrong.
You should have a record for each day, with a field saying what day it is. Then a simple query gets you your information.
Sorry, I've just realised by post was unclear, the name and the checkboxes are not the only fields in that table, there are fields such as contact info for each individual who has signed up as well. The count of number of days signed up for (the first part of the question) is so that each individual can be billed. I'm not clear how your suggestion fixes my problem, could you expand on what you mean please?
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:04
Joined
Sep 21, 2011
Messages
10,853
OK. It really sounds as if your DB is not normalized.?
You would have a table for contacts. You would also have a table for ContactDays, where a record would exist for each day a contact has signed up for.?

This is all very simplistic, but with a well structured DB, you do not have to jump though hoops getting the information you want, as you are doing now.
Imagine it was not days of the week, but days of the year? Are you going to have 365/366 fields to indicate what day?
DB tables are generally thin and deep, not wide and shallow like an Excel worksheet. If you come from an Excel background, you need to start from scratch.
Others can give more info. Perhaps upload a picture of your relationships?
 

dobseh

New member
Local time
Today, 05:04
Joined
Jul 9, 2022
Messages
10
Oh right, I understand, and I understand how it *should* be, unfortunately I inherited this database from someone who used it like a flatfile so basically everything is in one table. To give you an idea this thing was originally written for Access 97, when that version was new.

I should say though that this is a course that runs for one week a year, so having a checkbox per day isn't world ending like it would be in your example of 365 days. I've also had it dropped on me with an incredibly tight timeframe for getting this working, so redoing it from scratch really isn't an option at this stage. I'm sure everyone has been there with the pain of a "You know IT, could you have a quick look at this for me" type request from family and friends.
 

dobseh

New member
Local time
Today, 05:04
Joined
Jul 9, 2022
Messages
10
I agree that it appears your dB structure could be improved.

I have an example on my website here which might be of interest:-
As mentioned above - appreciate that the structure is not ideal, but there just isn't enough time for me to rewrite the whole thing at this late stage.
The link is interesting but to do what I want to do with that method wouldn't it require going to each record in a form rather than a query I can run against the table as a whole?
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:04
Joined
Sep 21, 2011
Messages
10,853
OK, my best offer is a Union query, one for each checkbox, and count those. Fortunately you only need 5. :)
 

MarkK

bit cruncher
Local time
Yesterday, 21:04
Joined
Mar 17, 2004
Messages
7,947
Presumably your checkbox fields in the underlying table are booleans. If so--and if I understand the problem correctly--you should be able to just subtract them together (because the True values will be -1) like...
Code:
SELECT FirstName & ' ' & Lastname As Fullname,  -IsMonday - IsTuesday - IsWednesday - IsThursday - IsFriday As WeekdayCount
FROM YourTable
 

strive4peace

AWF VIP
Local time
Yesterday, 23:04
Joined
Apr 3, 2020
Messages
921
hi @dobseh

> I inherited this database from someone

while that is the case, doesn't mean you need to keep the same bad structure. Normalizing will reduce development time down the road and make everything in the future go a lot smoother. To convert data:

1. create cross-reference table
2. make and run append queries to add records

> Count the number of days for each record individually

often, instead of storing YesNo, it is better to store a date ... then you should probably have a DateTypes table that defines different date types and the order in which they would normally occur
 

dobseh

New member
Local time
Today, 05:04
Joined
Jul 9, 2022
Messages
10
Presumably your checkbox fields in the underlying table are booleans. If so--and if I understand the problem correctly--you should be able to just subtract them together (because the True values will be -1) like...
Code:
SELECT FirstName & ' ' & Lastname As Fullname,  -IsMonday - IsTuesday - IsWednesday - IsThursday - IsFriday As WeekdayCount
FROM YourTable
You sir, are a star, this answers the first part of the question perfectly, thank you!

I think I'm going to abandon the idea of the second point, I might shove the data into Excel and mangle it that way if it's REALLY needed by the end user.
 

strive4peace

AWF VIP
Local time
Yesterday, 23:04
Joined
Apr 3, 2020
Messages
921
adding on ... while you can use duct tape and bailing wire to get what you want, do you always want to jump through hoops?

question: do you have control over the data structure?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:04
Joined
Feb 19, 2013
Messages
14,696
Check boxes store their values as Boolean (-1=true, 0=false) and cannot be null so your first requirement would simply adding the field names

-chb1-chb2-chb3 etc

use - to convert the -1’s to +1’s

For your 2nd requirement group by all the checkboxes and include a count so you can see the numbers for each permutation
 

dobseh

New member
Local time
Today, 05:04
Joined
Jul 9, 2022
Messages
10
adding on ... while you can use duct tape and bailing wire to get what you want, do you always want to jump through hoops?

question: do you have control over the data structure
I do now, yes. As mentioned above this was dropped on me very late and the original database has evolved(for want of a more derogatory term) over 25 years to the state it is in now. As an example of that state I just did ? CurrentDb.TableDefs("tblFoo").RecordCount on the main table and it has 150 fields. I'm going to suggest to the DB owner that after this course has finished I take a look at either ripping it out and starting again or doing something with the mess.

I'm not a database person or a programmer, but I have spent 20 years in IT, so any resources that would help with that process would honestly be appreciated.
 

dobseh

New member
Local time
Today, 05:04
Joined
Jul 9, 2022
Messages
10
Check boxes store their values as Boolean (-1=true, 0=false) and cannot be null so your first requirement would simply adding the field names

-chb1-chb2-chb3 etc

use - to convert the -1’s to +1’s

For your 2nd requirement group by all the checkboxes and include a count so you can see the numbers for each permutation
Thank you, this chimes with what MarkK wrote and i've tested and it gives me the count I need.

So would that second bit be:

SQL:
select chkbox1, chckbox2, chckbox3, chkbox4, chkbox5, count(*)
from table
group by chkbox1, chckbox2, chckbox3, chkbox4, chkbox5

or have I misunderstood?
 

strive4peace

AWF VIP
Local time
Yesterday, 23:04
Joined
Apr 3, 2020
Messages
921
hi @dobseh

> 150 fields

wow! That definitely indicates a structure that could benefit by being normalized!

I suspect many of the tables were created by importing. The import wizard is OLD! And doesn't set (or not set) properties that are good to use now. Best to examine the table design and adjust field properties.

> not a database person or a programmer, but I have spent 20 years in IT, so any resources that would help with that process would honestly be appreciated

here's a short book on Access -- wrote it many years ago but the basic principles still apply


once you've read this and are ready for more, let me know ...

> group by chkbox1, chckbox2, chckbox3, chkbox4, chkbox5

I doubt this will work efficiently ... or even do what you want either!
 

MarkK

bit cruncher
Local time
Yesterday, 21:04
Joined
Mar 17, 2004
Messages
7,947
You could also store position AND state in a single number using a bitmask like....
Code:
SELECT -IsMonday - IsTuesday * 2 - IsWednesday * 4 - IsThursday * 8 - IsFriday * 16 As Attributes
Each unique combination would yield a unique Attributes value. To perform aggregate functions, GROUP BY Attributes.

To see if a particular day is set, do bitmath like...
Code:
Property Get IsTuesdayOn(Attributes as Long) As Boolean
    IsTuesdayOn = (Attributes AND 2) = 2
End Property

This is tricky if you don't see it, but might offer some simplicities if you do.
 

dobseh

New member
Local time
Today, 05:04
Joined
Jul 9, 2022
Messages
10
hi @dobseh

> 150 fields

wow! That definitely indicates a structure that could benefit by being normalized!

I suspect many of the tables were created by importing. The import wizard is OLD! And doesn't set (or not set) properties that are good to use now. Best to examine the table design and adjust field properties.

> not a database person or a programmer, but I have spent 20 years in IT, so any resources that would help with that process would honestly be appreciated

here's a short book on Access -- wrote it many years ago but the basic principles still apply

once you've read this and are ready for more, let me know ...

> group by chkbox1, chckbox2, chckbox3, chkbox4, chkbox5

I doubt this will work efficiently ... or even do what you want either!
Thank you, it's mainly the normalisation that I need to learn about, so will have a read though and see how I can apply the concepts to this db.

The query as I wrote it gave me this output
Capture.JPG


Which matches up with data actually in the table. Don't ask why Thursday is the only day not abbreviated in the table - I don't know!
 

dobseh

New member
Local time
Today, 05:04
Joined
Jul 9, 2022
Messages
10
You could also store position AND state in a single number using a bitmask like....
Code:
SELECT -IsMonday - IsTuesday * 2 - IsWednesday * 4 - IsThursday * 8 - IsFriday * 16 As Attributes
Each unique combination would yield a unique Attributes value. To perform aggregate functions, GROUP BY Attributes.

To see if a particular day is set, do bitmath like...
Code:
Property Get IsTuesdayOn(Attributes as Long) As Boolean
    IsTuesdayOn = (Attributes AND 2) = 2
End Property

This is tricky if you don't see it, but might offer some simplicities if you do.
I think I need to read up on bitmask/bitmath to understand this to be honest, thank you for the additional idea though.
 

dobseh

New member
Local time
Today, 05:04
Joined
Jul 9, 2022
Messages
10
Thank you to everyone who responded, I didn't expect such a speedy and helpful bunch of people to show up to try to help me :)
 

Users who are viewing this thread

Top Bottom