Solved

Writing a query for yes/no check marks.

Posted on 2011-03-03
7
486 Views
Last Modified: 2013-11-28
I have a voting database I need to find out total of yes check marks for each voter. I then need to place that total on a form that just shows total of yes checks. Do I have to create a button on the form to find the total, or is there a way I can just show the total number on my form. Example:

total Yes for Martin is: 3    and so on
0
Comment
Question by:gigifarrow
  • 3
  • 2
  • 2
7 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35027222
try this query

select [name], sum(iif([checkfield]=-1,1,0))
from tableX
group by [name]
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 35028189
Make this the controlsource of a new textbox on the form:

=DCount("YourYesNoField","YourTable","YourYesNoField=-1")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35028373
Sorry, I just re-read your requirement:
=DCount("YourYesNoField","YourTable","YourYesNoField=-1" & " AND " & "YourNameField='Martin'")
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35028448
why too many unnecessary "" and &

=DCount("YourYesNoField","YourTable","YourYesNoField=-1  AND [YourNameField]='Martin'")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35029690
...Just so I can separate out each criteria segment while building the complete Criteria argument...
Thus making it easier to troubleshoot.
As you know these can get quite complex...
So for a person starting out this may make more sense.



0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 35047172
Here is a little Totals query that counts True and False values for a Yes/No field:
qtotPreferredCount-Design-view.jpg
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 35047175
And here it is in datasheet view:
qtotPreferredCount-Datasheet-vie.jpg
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now