Count Multiple Checkboxes, then Output Highest

[Tezcatlipoca]

I have a table, tblLogs, which - as wella s the ID primary key - is comprised of 10 checkbox fields which represet a Subject.
When records are logged into the database, the user can selectively tick or untick any of these Subjects from the form, so that one record (thus one row in the table) may contain no ticks, one tick or many ticks.

I am trying to now write a statistics form, which will show - amongst other things - the most popular Subject(s). I'd prefer to code the entire thing in VB, but am happy to use a query if that's any easier.

I know, or rather think, the answer lies in either the Count or Sum functions within the query. I can write a test query and count up the number of ticks in a single column no problem, but I need 10 counted within the same query.

Once that's done, I then need to be able to pass the most numerous Subject (or Subjects if there is a tie) back to an unbound textbox on my form.

Does anyone have a neat way to accomplish this, or am I going to have to write 10 single-field count queries, embed them on the form, then do the calculation of the most popular from there?

 

[Rabbie]

You could use the fact that No is stored as a zero and Yes as -1 to calculate howmany ticks you have in a particular record. Just sum all the the tick fields and that should give you your answer. Use something like

TickCount = Abs(tick1 + tick2 + tick3 ...

 

[Tezcatlipoca]

You could use the fact that No is stored as a zero and Yes as -1 to calculate howmany ticks you have in a particular record. Just sum all the the tick fields and that should give you your answer. Use something like

TickCount = Abs(tick1 + tick2 + tick3 ...

Hmmm...I get the idea, but don't see how I can use it to practically solve my issue. My table has eleven fields, one primary key ID field and one field for each of the ten subjects.

The query or code or whatever should be able to count how many ticks appear in each column, not row, then eithewr feed that information back to the form so I can manipulate it with VB, or, even better, also automatically work out which column has the most tickets, then return that field name to my form.

An example using half the number of subject fields, where Y is a tick and N is not, might be:

ID---Subj1----Subj2----Subj3----Subj4----Subj5
1------Y--------N-------N--------N--------Y
2------N--------Y-------N--------N--------Y
3------Y--------N-------N--------N--------N
4------Y--------N-------Y--------N--------Y
5------Y--------Y-------N--------N--------N

So the query should return the results:

Subj1----Subj2---Subj3----Subj4----Subj5
--4--------2-------1--------0--------3

then automatically return 'Subj1' to the form, since it has most ticks. If there's a tie, the field names of all those in front should get returned.

 

[allan57]

use a total's query ie

SELECT Sum(IIf([subj1]=True,1,0)) AS Subj1_Total, Sum(IIf([subj2]=True,1,0)) AS Subj2_Total, Sum(IIf([subj3]=True,1,0)) AS Subj3_Total, Sum(IIf([subj4]=True,1,0)) AS Subj4_Total, Sum(IIf([subj5]=True,1,0)) AS Subj5_Total
FROM Your_Table_Name_Here;

 

[Tezcatlipoca]

use a total's query ie

SELECT Sum(IIf([subj1]=True,1,0)) AS Subj1_Total, Sum(IIf([subj2]=True,1,0)) AS Subj2_Total, Sum(IIf([subj3]=True,1,0)) AS Subj3_Total, Sum(IIf([subj4]=True,1,0)) AS Subj4_Total, Sum(IIf([subj5]=True,1,0)) AS Subj5_Total
FROM Your_Table_Name_Here;

Embedded where? In the Criteria of a Query? Throwing that into the Control Source of an unbound field just returns a syntax error.

 

[allan57]

Make a Query and use either in a Form or Report to display results.

 

[Tezcatlipoca]

Make a Query and use either in a Form or Report to display results.

Sorry, but I'm not familiar enough with Queries to be sure of this. Putting that code anywhere within a query just returns a syntax error. Putting it in a form either does the same, or does nothing. And surely if I were calling a query from a form, the query name should be somewhere in that code?

 

[allan57]

I've attached an example for you to look at:

View attachment db1.mdb

Created in Access 97

 

[Tezcatlipoca]

I've attached an example for you to look at:

View attachment 24813

Created in Access 97

Aahhh, I see how it works. Wasn't familiar enough with queries to know you could fiddle with the Field section.

Giving it some thought, it would seem as though I can achieve the same result without queries at all by simply placing multiple unbound textboxes on the form, each with the code

=DCount("[ID]","<tablename>","[<subjectfieldname>] = -1")

which returns the same result.

The problem, and the reason I disregarded the above and started the thread, is that I really wanted some way to also have the query or code automatically work out which field had the most ticks, then return the name of the field.
If that's not possible, it looks as though I'm going to have to add a pile of hidden textboxes to the form, then write extra code to work out which has the highest value and show a visible reference. I'd just prefer to have avoided all the extra code!

 

[allan57]

Another example, this returns the highest selection:

View attachment db1.mdb

 

[Tezcatlipoca]

Thankyou very much, allan57. I have a couple of queries though. I can convert the returned integer into the text I need using additional If statement that simply identifies the number, then uses it to alter the value of an unbound textbox to give the field name. however, is there a cleaner way to achieve the same by having your excellent code return the name of the field with the most ticks, rather than a number? If not, it's not a major issue, as I can get around it with my additions.

Secondly, is it possible to return multiple results when there are ties? At the moment, a tie results in just the first subj being returned.

 

[allan57]

The attached database should do what you require:

View attachment db1.mdb

 

[Tezcatlipoca]

Thankyou very much for all your help and advice, allan57; thanks to you I now have exactly what I was trying to achieve.