Multiple criteria in queries

[Idjit]

I need to calculate counts for different variables from the same table, each with different criteria, and I'm wondering if this can be done in 1 query or if I need separate queries for each. Here's what I mean:

I need the total number of people who completed an interview during a certain month. No problem, I have a parameter query set up to enter in the date and include the ID variable as a "count" field. I also want to know how many of those people answered "no" to Q1. I can add Q1 to the query as a count field with criteria="no", but then ID only counts people who answered Q1=no. Is there any way to get the information I need in one query?

It may not seem like a big deal to have 2 queries, but I also need a count of the number of people who answered "yes" to Q2 (which is completely independent from the answer to Q1), and I need all of this information for each of several interviews.

I also need percentages of the total participants who answered Q1 and Q2 as specified (i.e. Q1%=(CountOfQ1=no)/(CountOfTotalParticipants). Access doesn't seem to like it when I try to run a calculation using variables that are defined within the same query - it seems I need to create a separate query based on the Count query and use that to calculate the percentages.

So you can see that I quickly end up in query hell. I don't even mind doing all of these separate queries, but then when I try to pull the information into one main query to use in a report, I get the error message I described yesterday ("cannot open any more databases"). And searching Access Help doesn't tell me why I'm getting this or how many databases can be opened in one query.

One solution I came up with was to have several summary queries instead of one master query. But then for the report each would need a separate subreport, which means I end up in report hell as well as query hell.

Sorry for the length of the message. Any help you all can give would be appreciated.

p.s. Please go easy on the SQL - I'm definitely a macro-and-expression-builder kinda person

 

[Jake]

One option to calculate the response-criteria totals in one query would be to use "sum" instead of "count" in the query. For example, for the total of responses where q1="no", use this field (with total type = "sum"):

Q1NoTotal: abs(q1="no")

The expression (q1="no") will evaluate to 0 (false) or -1 (true), and abs will convert the -1 to 1. Thus, the sum of this field will equal the total number of responses with q1 = "no". Since no record-limiting criteria is used to achieve this result, you can create similar fields (Q2YesTotal, for example) in the same query and calculate those totals across all responses as well.