?
Solved

Writing a query for yes/no check marks.

Posted on 2011-03-03
7
Medium Priority
?
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 2000 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

801 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