Using a count function on a report

[voltarei]

Hi everyone,
I have very basic knowledge of Access, so please bear with me.
I have a database which is used as a small questionnaire.
The only information collected (in one table) is Employee Name, Employee Number and then the answers to Question 1 to Question 30. Each Question field is linked to a second table that contains either A, B or C which is selected from a combi box on the form.

I have managed to create a basic report which lists each Question field, and it's answer (A,B or C) for an individual (linked to their employee number).
The report is populated by a query containng the same fields as the main table.

The report looks something like:

Employee Name:
Employee Number:

Question 1: A
Question 2: C
Question 3: A
Question 4: B

And so on.....

What I am trying to do is add a text box to the report which will count the number of "A" answers on the report, as second text box to count the "B's" and a third for the "C's".

Is this possible?

Many thanks in advance
Phil

 

[lameid]

What you could do is in the query use the IIF function to display 1 if it is a and 0 if not. Then you could sum that field...

In the query where you put the field something like:

ACounter: IIF([AnswerField] = "A",1,0)

Then in your report you could sum on Acounter which would be the count of A's. Note that AnswerField is the field that has the answer to your questions.

 

[TheAceMan1]

How are ya voltarei . . .

As an example counting A's, in the reports header or footer section add an [blue]unbound textbox[/blue] with the following [blue]control source[/blue] (you'll have to set the names properly):

[blue]=DCount("[EmployeeNo]","TableName","[EmployeeNo]=" & [EmployeeNo] & " AND [Answer]='A'")[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[voltarei]

Hi both LameID and TheAceMan1 and thanks for your suggestions.
I did warn you that I was very basic at this, but I have tried both your ideas, and not had much luck with either.

I think the problem that I am having, is that there are 30 fields in my table, all of which can be populated with an A,B or C (one field for each question).

When I tried the suggestion from TheAceMan1 I was prompted to enter the employee number when the report opened (which is what I had set it to do in the query), and then a second time for the unbound text box containing the formula.
When the report was displayed the unbound text box just said #Error.

Any other ideas?
Please remember that on the report I will need a total for all the A's, all the B's and all the C's, and I would like it to look something like:

Your A Answers are: 10
Your B Answers are: 6
Your C Answers are: 14

Thanks once again for taking the time to respond.
Phil

 

[dhookom]

IMHO: your table structure isn't correct. Have you considered normalizing so that each response for each question is a separate record? There is a complete survey application at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

which is normalized and would easily allow for summarizing As, Bs,...

You could normalize your existing table with a union query like

SELECT [Employee Number], 1 as Question, [Question1] as Answer
FROM tblNoNameGiven
UNION ALL
SELECT [Employee Number], 2, [Question2]
FROM tblNoNameGiven
UNION ALL
--- etc ---
SELECT [Employee Number], 30, [Question30]
FROM tblNoNameGiven;

You can then create a totals query based on the union query that would group by Answer and count the Employee Number.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lameid]

Duane is right...

You really ought to have a table where each question ends up on one row. Using the Union query that he suggested is a workaround. Ideally your tables would be structured differently. For information on data normalization checkout:

http://www.datamodel.org/NormalizationRules.html

You may want to use the Union depending on how much time and effort you have in your database. Keep in mind if you use the database extensively, your will only have more and more problems in the future.