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.
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.