Help Beginner with DCOUNT function
Author Message Help Beginner with DCOUNT function

Hi

I'm writing my first db with access and need to know it this is possible.
I have a table of survey questions (option groups) that store  either 1, 2
or 3 when selected with a checkbox. What I wish to do is sum the number of
1's, 2's and 3's for each question over the entire set of records.

In other words I want to tally the number of 1's, 2's and 3's for each
question on every record. I can use the DCount for each response but I can't
figure out how to pass a variable to the expr, domain and criteria  part of
Dcount then loop the variables.

Many thanks
dwight

Mon, 18 Jun 2001 03:00:00 GMT  Help Beginner with DCOUNT function
If you want to get total counts for each question for each response, you
could make a grouped query like the following:

SELECT Sum(Abs([QuestionA]=1)) AS QAis1, Sum(Abs([QuestionA]=2)) AS
QAis2, Sum(Abs([QuestionA]=3)) AS QAis3
FROM tblQuestions;

The idea here is to return the sum of 1's and 0's, which are a logic
comparison that returns a true or false.
I used aliases such as "QAis1" to make the output meaningful. The Abs
function converts a TRUE value to 1. Follow this example through each of
your questions, (QuestionB,C....) and you should have 1 query with
counts for all the responses. Good luck,
-Steve

Quote:

> Hi

> I'm writing my first db with access and need to know it this is possible.
> I have a table of survey questions (option groups) that store  either 1, 2
> or 3 when selected with a checkbox. What I wish to do is sum the number of
> 1's, 2's and 3's for each question over the entire set of records.

>  In other words I want to tally the number of 1's, 2's and 3's for each
> question on every record. I can use the DCount for each response but I can't
> figure out how to pass a variable to the expr, domain and criteria  part of
> Dcount then loop the variables.

> Many thanks
> dwight

Wed, 20 Jun 2001 03:00:00 GMT

 Page 1 of 1 [ 2 post ]

Relevant Pages