AVG in reports

[desmur]

I need to find out how to get the average of the number of records in a footer. When I try to use avg, it give me the average of the total number of records in the detail group. I am not showing the detail because I just want totals per person. Let me know if I need to explain a little better. Thanks for your help

 

[jrbarnett]

The access Avg (Average) function is the total value of the field divided by the number of records in the field.
If you want totals per person, use Sum to bring the total number of a particular field, or Count to bring the number of records.

John

 

[desmur]

I understand that AVG, averages the number of records in the field and it is not doing that. This is an example of what I am trying to do. There are four records in the footer and I want an average of these records, not all the records in the detail section. I'm not even showing the detail section, so I don't even want it to consider those records, just the average of the totals.

Example:

This is the name footer

50 John Brown
20 Mary Brown
10 Sue Brown

60 Total 26.6 Avg hours

Mind you the detail may have had this

10 John Brown
10 John Brown
20 John Brown
etc....

Can you help me?

 

[jrbarnett]

Just specify the textbox in the footer as the control.

By the way: how do you get 60 as a total from 50, 20 and 10. Surely this should be 80?

John

 

[desmur]

When I do this, it prompts me like it doesn't know what that text box is.

 

[jrbarnett]

Why not just rewrite the query to generate the totals per person with an Average field and Group By on the rest, then you need not mess around with the footers and other intermediate totals.

John

 

[desmur]

How can I tell it to generate the totals per person in a query?

 

[dhookom]

Select Person, Sum(YourField) as PerSum
FROM tblYourTable
GROUP BY Person;

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