calculating with yes/no boxes

[Kath]

I have used a simple yes/no box for some information in one of my tables. I want to write a query that can be included in a report that tells me how many of a group of selected records have "yes" checked. When I used "sum," I came up with negative numbers or zeros. These are not correct values. What do I need to do in the query to get a numeric values that equals the number of "yes" answers?

[orange]

Please tell us about your tables and the database generally in plain English.

[June7]

Maybe like:

SELECT Sum(IIf([fieldname]=True,1,0)) AS CountYes FROM tablename;

[Kath]

Two tables are involved in the query. The first lists basic information about the people who use our service. The yes/no box is in this table. The second table is in a subform, and tracks what day in a given month a client used our service. We must report certain information on the number of clients in a given month sorted according to city. The required information includes how many qualify as "yes" in the Yes/No field.

[Kath]

I must start by explaining that I created this database using the Microsoft Press Step by Step guide for Access 2010. I know next to nothing outside what is in the book. That said, I'm not sure where or how I would enter that formula when creating the query. Does it go in the "Criteria" row?

[June7]

It goes on the Field row. This is creating a field in query with expression but now I can see it's probably not what you want. Maybe something more like:

SELECT ClientID, Count(ClientID) AS CountClients, Format([datefield], "yyyymm") AS MoYr, City FROM Clients INNER JOIN Services Clients.ClientID=Services.ClientID GROUP BY ClientID, Format([datefield], "yyyymm"), City HAVING Qualify = True;

Or build a report and use its Sorting & Grouping features with aggregate calcs in group footer. This will allow display of detail records as well as summary calcs.

Access Help has more guidelines on aggregate queries and building report.

[Kath]

I'm afraid what you have above is very confusing to me. I wouldn't even know where to write it and how to tell it I'm referencing two different tables. What I was hoping for was a "quick fix" that somehow translated the Boolean negatives into a "regular number." From the results in the test group I'm working with, it appears that the absolute value Boolean negative number is one less than the correct number. But before I try to write a formula to make this calculation, I wanted to know if there was something simpler and/or if what my test group shows does, in fact, make some logical sense to people who understand Boolean notation.

[June7]

What I showed is the SQL statement of a query. Use the Query Designer to build a query that joins tables. This is basic Access functionality. So is designing a report. If your book neglected these basics, then definitely need a better book.

Could use Abs() function to eliminate the negative.

Abs([fieldname])

[Kath]

I do know how to build queries and reports accessing multiple tables, but only using Query Designer. Unfortunately, I don't know how to follow your SQL statement in query designer.

[June7]

The example is an aggregate (Totals) GROUP BY query. Query in design view, click the Totals button on the ribbon. Your book should have more details.

Or switch to SQL View and type SQL statement.

Or build the suggested report.