Counting TRUEs of checkboxes across multiple columns

  • Thread starter Thread starter Dave R.
  • Start date Start date
D

Dave R.

Hi, I have a table which looks like:
Question 1 Question 2 Question 3
X X
X X
X X X
X X


etc. where the Xs are checked checkboxes.

I am trying to make a query which simply shows for each question, how many
checks there were. The result should appear like:

Question 1 Question 2 Question 3
3 4 2

I have tried the summation button, then using count or group by, but can't
seem to get it to do this. Any help appreciated.
 
Dave wrote:

--------------------
| From: "Dave R." <[email protected]>
| Subject: Counting TRUEs of checkboxes across multiple columns
| Date: Wed, 14 Jan 2004 15:58:00 -0800
| Lines: 21
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
| X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.queries
| NNTP-Posting-Host: eecclient.dhs.ca.gov 205.163.191.5
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.
phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:186181
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi, I have a table which looks like:
| Question 1 Question 2 Question 3
| X X
| X X
| X X X
| X X
|
|
| etc. where the Xs are checked checkboxes.
|
| I am trying to make a query which simply shows for each question, how many
| checks there were. The result should appear like:
|
| Question 1 Question 2 Question 3
| 3 4 2
|
| I have tried the summation button, then using count or group by, but can't
| seem to get it to do this. Any help appreciated.
|
|
|
|

Why not use a Report and use the DCount function, example:

=DCount("[Question1]","[table name]","[Question1]=Yes")
=DCount("[Question2]","[table name]","[Question2]=Yes")
=DCount("[Question3]","[table name]","[Question3]=Yes")


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
 
Hi, I have a table which looks like:
Question 1 Question 2 Question 3
X X
X X
X X X
X X


etc. where the Xs are checked checkboxes.

I am trying to make a query which simply shows for each question, how many
checks there were. The result should appear like:

Question 1 Question 2 Question 3
3 4 2

I have tried the summation button, then using count or group by, but can't
seem to get it to do this. Any help appreciated.
Click to expand...

Question1Checked:ABS(Sum([Question1]))
Question2Checked:ABS(Sum([Question2]))
etc.

A Check box field has a value of either -1 (Checked) or 0 (Unchecked)

If you add the above Question1 values, the result is -3.
The ABS Function returns the actual number value of the result, 3.

If you ever wish to count the No answers (unchecked), then you can
use:
Question1Unchecked:Sum([Question1]+1)

Again, each checked value is -1, so -1 + 1 = 0.
Each No value is 0 + 1, or 1.
So, summing question1 unchecked results in
0 + 1 + 0 + 0 = 1.

You can just as easily do this in a report.
 
Fred's response is more efficient and flexible. If the report gets opened
with a "Where" clause in the OpenReport command then using DCount() will
reference the entire table while using Fred's suggestion will total only the
records included in the report. Also, each domain aggregate function such as
DCount() or DSum() will create its own recordset. If you have lots of
questions and results, this will slow down dramatically. Using the recordset
of the report or calculating totals in the query will result in creating
only one recordset.

--
Duane Hookom
MS Access MVP


"Petrucci2000" said:
Dave wrote:

--------------------
| From: "Dave R." <[email protected]>
| Subject: Counting TRUEs of checkboxes across multiple columns
| Date: Wed, 14 Jan 2004 15:58:00 -0800
| Lines: 21
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
| X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.queries
| NNTP-Posting-Host: eecclient.dhs.ca.gov 205.163.191.5
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.
phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:186181
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi, I have a table which looks like:
| Question 1 Question 2 Question 3
| X X
| X X
| X X X
| X X
|
|
| etc. where the Xs are checked checkboxes.
|
| I am trying to make a query which simply shows for each question, how many
| checks there were. The result should appear like:
|
| Question 1 Question 2 Question 3
| 3 4 2
|
| I have tried the summation button, then using count or group by, but can't
| seem to get it to do this. Any help appreciated.
|
|
|
|

Why not use a Report and use the DCount function, example:

=DCount("[Question1]","[table name]","[Question1]=Yes")
=DCount("[Question2]","[table name]","[Question2]=Yes")
=DCount("[Question3]","[table name]","[Question3]=Yes")


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
Click to expand...
 
Hi Eric, Duane and Fred. Thanks for your replies thus far.

Unfortunately I am very new to access, and while I think I see what the
functions and formulas are doing -- I have no idea where to put them! Can
someone shed some light for me?

With these functions, can I just create a blank report or query that's
linked to my table, then insert these functions somewhere?

Thanks again
 
If you don't want the details, create a query based on the table. Select
View|Totals or click the sigma button. Then add your question fields to the
grid and change them like
Qstn1True:Abs([Question1])
set the totals to Sum
Do this for each question field and then run the query to see your results.


--
Duane Hookom
MS Access MVP


Dave R. said:
Hi Eric, Duane and Fred. Thanks for your replies thus far.

Unfortunately I am very new to access, and while I think I see what the
functions and formulas are doing -- I have no idea where to put them! Can
someone shed some light for me?

With these functions, can I just create a blank report or query that's
linked to my table, then insert these functions somewhere?

Thanks again




"Petrucci2000" said:
Dave wrote:

--------------------
| From: "Dave R." <[email protected]>
| Subject: Counting TRUEs of checkboxes across multiple columns
| Date: Wed, 14 Jan 2004 15:58:00 -0800
| Lines: 21
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
| X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.queries
| NNTP-Posting-Host: eecclient.dhs.ca.gov 205.163.191.5
| Path:
Click to expand...
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.
phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:186181
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi, I have a table which looks like:
| Question 1 Question 2 Question 3
| X X
| X X
| X X X
| X X
|
|
| etc. where the Xs are checked checkboxes.
|
| I am trying to make a query which simply shows for each question, how many
| checks there were. The result should appear like:
|
| Question 1 Question 2 Question 3
| 3 4 2
|
| I have tried the summation button, then using count or group by, but can't
| seem to get it to do this. Any help appreciated.
|
|
|
|

Why not use a Report and use the DCount function, example:

=DCount("[Question1]","[table name]","[Question1]=Yes")
=DCount("[Question2]","[table name]","[Question2]=Yes")
=DCount("[Question3]","[table name]","[Question3]=Yes")


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
Click to expand...
Click to expand...
 
Back
Top