Determining Subtotals by formula

[Principal]

I have a table that consists of scores. I have a report that lists the scores by the course they are associated with. What I want to do is to create some stats that would print in the footer for each group. The stats would count the number of scores in five different ranges, for example,

How many scores were in the range of 0 - 50, 51-75 etc.

I am assuming this code would be inserted into the on- format event of the group footer. I was planning on using a case statement and incrementing a counter variable for each range.

Any other ideas or comments would be helpful. THanks,

Steve

 

[scriverb]

It is easiest to create new columns in your RecordSource query for the report. Calculated columns that return either a 1 or 0 depending upon score found can then be added to the detail section as invisible controls that can then be calculated as a Sum in the footer sections.

Example:

Select A.*, IIF(A.[Score]<=50,1,0),1,0) as Score(0-50), IIF(A.[Score]>=51 and A.[Score]<=75,1,0),1,0) as Score(51-75) FROM [red]yourtablename[/red] as A;

Now the two new fields can be the ControlSource for two new controls in the Detail Section. Set their visible property to False. Name the controls the same name as the field names. Delete their labels and shrink them down small as they won't be seen.

Now in the Footer create two controls. Set their ControlSource property equal to the Sum of the Control name in the Detail Section.

Example:

=Sum(Me![Score(0-50)])

and

=Sum(Me![Score(51-75)])

These calculated fields will sum the 1 or Zero(0) values in the detail section controls.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[dhookom]

I would approach this using a more robust/flexible method by creating a table or score ranges:

tblScoreRanges
================================
MinScore   MaxScore   ScoreTitle
---------- ---------- -------------
0          50         0-50
51         75         51-75
76         85         76-85
86         95         86-95
96         100        96-100

you can then add this table to your report's record source and set the criteria under [Score] to
Between MinScore and MaxScore
Add the ScoreTitle field to the query grid so you can use it in the report.

This solution allows you to change the score ranges at any time without touching an expression or code.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Principal]

Thanks for both approaches. I opted for an approach similiar to Bob's because these reports were ad hoc. however, as I build this report and similiar ones for future use, Duane's approach has an appeal for ease of system mantainance and and change. Thank you both.

Steve

 

[dhookom]

I build applications for clients so my solutions are generally maintained in data tables rather than expressions. I have been burned in the past by changes to business rules that were "never going to change".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.