Yes No Count Example For MS Access 2000, 2002, 2003, 2007
We will start this by creating our Yes/No Field and another text field to test our final count. Right click your table and select design view. In an empty space give your two new fields names ( USE and YESNO are mine). Set the USE field data type to text and the YESNO field to Yes/No.
|
Once you have your fields you now need to populate them as shown below. 
|
|
I populated the Use field on the same records as I did Yes/No field. What this will do is let me see, with a little clarity, which Values equal Yes and which values equal no when I count them. In this case Checked or -1 equals Yes. Unchecked or 0 equals no. To pull all checked records we need to use either "<>" or "-1" in our query.
Create a new query in design view and add your Table (Customers is mine). Double click the USE and YESNO fields. Now in the "Field:" row add a third by simply placing two empty quotes("") in an open space. MS Access will Automatically add "Expr1:" in front of the double quotes as soon as you click another area. In place of Expr1 you can type your own field name. In the "Criteria:" row under the YESNO field place a "-1" or "<>0". My query will use -1. Lastly Select "Group By" in the "Totals:" Row for the YESNO and USE fields. In the Manually named field select Count. Click the Red !.
Your finished and ran query should look something like the below picture.
For further help or suggestions go here: MS Access tutorials, Tables, Queries and later: Forms
You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.
For further MS Access tutorials go here: MS Access tutorials
| Did you find what you were looking for? What would you suggest? mail@eraserve.com |
