A Report to count the scores of a review

[endwarde]

Hello everyone. I have a report I am having trouble with putting together in my head and I hope you all can point me down the right path here.

I have a fairly simple table I need to generate a report from. The table represents a manager's review of a certain task that his staff does. The table has a [Name] of employee, [Date] of the review and then [Question1] through [Question31] where each question is given a rating of 1-3, representing below average, average and above average. Simple enough, right?

Now, he would like a summation report which he can specify the date range (no problem) and see, by employee, how many of each rating they have had for each question. So, for John Doe for Question1, there were 22 "1"s, 13 "2"s and 27 "3"s. You would have those results for all 31 questions. This is the part that's hurting my brain...

How do I put this together? Every swing I take at it seems wrong. I understand I will be wanting to use a function of =Sum(IIf([Question1]="1",1,0)) to count values with, but for some reason I can't seem to get my report to not spit errors out at me when I am trying to use it.



I am way out of practice working with reports, so I would love if someone could help me with my basic structure and give me suggestions of how to proceed.

Thanks!
Eric

[Monterey_Manzer]

Hi Eric,

I think it would be helpful if you could post what you currently have for the report's record source as an SQL statement. That way we can test it out with some fake data.

Cheers,
Ryan

[endwarde]

Hi Ryan, do you mean you just want the fields of the table listed out? (Not a SQL or VB coder really)
Simplified (my actual names are longer and there is other data in the table that we don't need to worry about for this)

[Table].[Key#] = The keyed number for each record
[Table].[Date] = The date the record was entered
[Table].[Name] = The name of the staff member the review is about.
[Table].[Question1] = Question 1, will be answered with 1, 2, 3, or 0.
and [Table].[Question2] through [Question31]

Like I said, the structure is very simple. Is this what you're looking for?

[Monterey_Manzer]

Actually I was thinking of the Report Record Source. If you have your report open in Design mode with the Property Sheet visible, you can get this string by first making sure you are viewing the Report properties (the drop down menu should say "Report") and then going to the "Data" tab. The Record Source will be the first property listed on that tab. Copy the string from there and paste it here.

[endwarde]

Oh, the record source... hehe.

It's not much of a string, it's just the Query that I am pulling the data from the table with:
qryClaimReview

[Monterey_Manzer]

Okay so now we go open qryClaimReview and select SQL View. That should be the SQL statement I am thinking of.

[endwarde]

Ahhh.. I hadn't ever worked with that view before. You can ignore the "notes" fields that accompany the ClmRvwQ1 (etc...) as they aren't needed in the report.

SELECT tblClaimReview.ClmRvwClaimNumber, tblClaimReview.ClmRvwAdjusters, tblClaimReview.ClmRvwDateLoss, tblClaimReview.ClmRvwDateReview, tblClaimReview.ClmRvwQ1, tblClaimReview.ClmRvwQ1Notes, tblClaimReview.ClmRvwQ2, tblClaimReview.ClmRvwQ2Notes, tblClaimReview.ClmRvwQ3, tblClaimReview.ClmRvwQ3Notes, tblClaimReview.ClmRvwQ4, tblClaimReview.ClmRvwQ4Notes, tblClaimReview.ClmRvwQ5, tblClaimReview.ClmRvwQ5Notes, tblClaimReview.ClmRvwQ6, tblClaimReview.ClmRvwQ6Notes, tblClaimReview.ClmRvwQ7, tblClaimReview.ClmRvwQ7Notes, tblClaimReview.ClmRvwQ8, tblClaimReview.ClmRvwQ8Notes, tblClaimReview.ClmRvwQ9, tblClaimReview.ClmRvwQ9Notes, tblClaimReview.ClmRvwQ10, tblClaimReview.ClmRvwQ10Notes, tblClaimReview.ClmRvwQ11, tblClaimReview.ClmRvwQ11Notes, tblClaimReview.ClmRvwQ12, tblClaimReview.ClmRvwQ12Notes, tblClaimReview.ClmRvwQ13, tblClaimReview.ClmRvwQ13Notes, tblClaimReview.ClmRvwQ14, tblClaimReview.ClmRvwQ14Notes, tblClaimReview.ClmRvwQ15, tblClaimReview.ClmRvwQ15Notes, tblClaimReview.ClmRvwQ16, tblClaimReview.ClmRvwQ16Notes, tblClaimReview.ClmRvwQ17, tblClaimReview.ClmRvwQ17Notes, tblClaimReview.ClmRvwQ18, tblClaimReview.ClmRvwQ18Notes, tblClaimReview.ClmRvwQ19, tblClaimReview.ClmRvwQ19Notes, tblClaimReview.ClmRvwQ20, tblClaimReview.ClmRvwQ20Notes, tblClaimReview.ClmRvwQ21, tblClaimReview.ClmRvwQ21Notes, tblClaimReview.ClmRvwQ22, tblClaimReview.ClmRvwQ22Notes, tblClaimReview.ClmRvwQ23, tblClaimReview.ClmRvwQ23Notes, tblClaimReview.ClmRvwQ24, tblClaimReview.ClmRvwQ24Notes, tblClaimReview.ClmRvwQ25, tblClaimReview.ClmRvwQ25Notes, tblClaimReview.ClmRvwQ26, tblClaimReview.ClmRvwQ26Notes, tblClaimReview.ClmRvwQ27, tblClaimReview.ClmRvwQ27Notes, tblClaimReview.ClmRvwQ28, tblClaimReview.ClmRvwQ28Notes, tblClaimReview.ClmRvwQ29, tblClaimReview.ClmRvwQ29Notes, tblClaimReview.ClmRvwQ30, tblClaimReview.ClmRvwQ30Notes, tblClaimReview.ClmRvwQ31, tblClaimReview.ClmRvwQ31Notes
FROM tblClaimReview;

[endwarde]

Monterey_Manzer, is there any other info you need from me?