Expression Question

[LadyDiss]

What I would like to do in my report as opposed to building a ton of queries is this . . . I have several columns that rate classes, for example, Excellent, Good, Fair, Poor are drop downs to choose from. In my report, I would like to total that column which I can do =Count([Value]) . . . but I would also like to have the numbers for Excellent, Good, Fair, Poor. How do I write this expression? Thank you!

 

[Pat Hartman]

Create the following query and use it to populate a subreport that you place in the report footer.

Select ClassScore, Count(*) As ScoreCount
From YourTable
Group by ClassScore;

 

[LadyDiss]

It works to a point . . . it will only show the total for the largest . . . I think I am missing a small detail in the properties? Can you help?

 

[Pat Hartman]

If Excellent, Good, Fair, Poor, etc. are all possible values for a single field, the query I posted will produce a list with a count of the number of occurances of each value as in:

Excellent 45
Good 90
Fair 8
Poor 5
etc.

If these values come from different columns, you need a separate query to count each of them. I strongly recommend a table design change in this case

 

[LadyDiss]

Thank you for all your help. I did get it to work. For some reason, I had to delete the info in Child Link in the properties for the subreport? Not even sure what that does but regardless, now it works! Thanks so much!