Conditional count in report?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to do something that seems like it should be very easy, but I can't make it work. I've got a database in Access 2000 that contains quiz information. I've got results on a variety of reports, but what I'm trying to do now is create a report that lists each question on the quiz, and then each possible answer (either "Yes", "No", or "N/A") and how many people answered that way. The reults I'm looking for are something like this -

1) Question 1 text goes here YES: 4 NO: 5 N/A:
2) Question 2 text goes here YES: 7 NO: 2 N/A:
3) Question 3 text goes here YES: 2 NO: 2 N/A:

etc

I could probably create a seperate query for each question and do it that way, but it seems like I should be able to have an unbound text box with a control source of something like "SELECT Count(*) FROM qryMySourceQuery WHERE QuestionID=" & [TheID] & AND Answer='YES'

Every variation like that I try either results in a "#NAME?" error on the report itself, or I get a pop up box saying I have invalid syntax in my query expression

Any thoughts?
 
The query is missing a quote after the [TheID]:

"SELECT Count(*) FROM qryMySourceQuery WHERE QuestionID=" & [TheID] & AND
Answer='YES'"

try

"SELECT Count(*) FROM qryMySourceQuery WHERE QuestionID=" & [TheID] & " AND
Answer='YES'"

MDW said:
I'm trying to do something that seems like it should be very easy, but I
Click to expand...
can't make it work. I've got a database in Access 2000 that contains quiz
information. I've got results on a variety of reports, but what I'm trying
to do now is create a report that lists each question on the quiz, and then
each possible answer (either "Yes", "No", or "N/A") and how many people
answered that way. The reults I'm looking for are something like this -
1) Question 1 text goes here YES: 4 NO: 5 N/A: 1
2) Question 2 text goes here YES: 7 NO: 2 N/A: 1
3) Question 3 text goes here YES: 2 NO: 2 N/A: 6

etc.

I could probably create a seperate query for each question and do it that
Click to expand...
way, but it seems like I should be able to have an unbound text box with a
control source of something like "SELECT Count(*) FROM qryMySourceQuery
WHERE QuestionID=" & [TheID] & AND Answer='YES'"
Every variation like that I try either results in a "#NAME?" error on the
Click to expand...
report itself, or I get a pop up box saying I have invalid syntax in my
query expression.
 
or instead of creating separate queries, use the dcount function .

YesCnt = DCount([fieldname], [tablename], [criteria])

HTH
MDW said:
I'm trying to do something that seems like it should be very easy, but I
Click to expand...
can't make it work. I've got a database in Access 2000 that contains quiz
information. I've got results on a variety of reports, but what I'm trying
to do now is create a report that lists each question on the quiz, and then
each possible answer (either "Yes", "No", or "N/A") and how many people
answered that way. The reults I'm looking for are something like this -
1) Question 1 text goes here YES: 4 NO: 5 N/A: 1
2) Question 2 text goes here YES: 7 NO: 2 N/A: 1
3) Question 3 text goes here YES: 2 NO: 2 N/A: 6

etc.

I could probably create a seperate query for each question and do it that
Click to expand...
way, but it seems like I should be able to have an unbound text box with a
control source of something like "SELECT Count(*) FROM qryMySourceQuery
WHERE QuestionID=" & [TheID] & AND Answer='YES'"
Every variation like that I try either results in a "#NAME?" error on the
Click to expand...
report itself, or I get a pop up box saying I have invalid syntax in my
query expression.
 
Back
Top