Solved

# Writing a query for yes/no check marks.

Posted on 2011-03-03
490 Views
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
Question by:gigifarrow
• 3
• 2
• 2

LVL 120

Expert Comment

ID: 35027222
try this query

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

LVL 74

Accepted Solution

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

ID: 35028373
=DCount("YourYesNoField","YourTable","YourYesNoField=-1" & " AND " & "YourNameField='Martin'")
0

LVL 120

Expert Comment

ID: 35028448
why too many unnecessary "" and &

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

LVL 74

Expert Comment

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

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

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.