Discussion Locked

Access Report Averaging

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to create a report where the report footer will use totals and then average these totals. I have fields that calulates the total of my responses, the score given to me and then their average. I believe that I need to use and Expression Builder. I have used this one, but it does not calculate correctly.

=[Quality Total]/[Quality Responses]

Any help would be appreciated.

6 replies

by john_wills

One of the annoying things about Access reports is that calculated controls cannot be used to calculate further controls. I am not quite sure what you are trying to do, but probably what you need for your grand average is an expression like this:
=(Total(thing1)+Total(thing2)+...+Total(thingn))/n

by lovelexie

I would like to send you a copy of this database. I believe if you saw what I was trying to accomplish, you could help me more. Let me know if that is okay with you.

by bob sellman

If I understand you correctly, your report should be displaying at a grouping footer the subtotal, number of responses, and the average for those (calculated as subtotal/responses) and you want the same kind of display for the entire report.

The first thing to remember is that to get a proper average you must compute the total and the count of the number of all responses, and then the average is the total/count. You can't "average the averages".

Access gets really funny when computing subtotals and totals. Let's assume your report shows each response in the detail, then the subtotals for a group of responses in a group footer, then the grand totals for all responses.

I would set up in VBA code some variables for the subtotals for responses and number of responses and for the totals and the count of responses. Set them to 0 at the beginning of your report (OnOpen). The real trick is to do the computation of these variables using the OnPrint property (of the Details section and the Group footer and header). (If you do the computation in the OnFormat property, Access very nicely double counts most of the values.)

In the Details section, do something like this:
SubtotalResponse = SubtotalResponse + me.response
SubtotalNumber = SubtotalNumber + 1
TotalResponse = TotalResponse me.response
TotalCount = TotalCount + 1

Since each detail is one response, you can just increment the SubtotalNumber by 1 each time in the detail. For safety, you should probably use the Nz() function, which replaces nulls with the value 0 so the addition doesn't fail.

Then, in the GroupHeader (which you can make a height of 0 if you want) do the following:
SubTotalResponse = 0
SubTotalNumber = 0

In the GroupFooter, set the controls as follows:
me.SubTotalResponse = SubTotalResponse
me.SubTotalNumber = SubTotalNumber
me.SubTotalAverage = SubTotalResponse/SubTotalNumber

by bob sellman

I couldn't fit this at the end of my response, so here it is:

(Don't forget to check for division by 0.)

When you get to the report footer, for the OnPrint function put the values in the controls like this:

me.TotalResponse = TotalResponse
me.TotalCount = TotalCount
me.TotalAverage = TotalResponse/TotalCount

This discussion is closed to new replies.