MS Access 2010 Report Group average

I have grouped my data like this:

   Student
      Class Type
           Class Date

Under class date, I have Date, Test Score, and Class Average columns.

开发者_开发问答

How can I get the class average for a given date? Thanks

---

You would put this in the footer of the class group. I always prefer to bring in the two parts that make up the score, add those up then divide them to get the average.

The reason for doing this is simple maths, if you take an average of an already aggregated figure you are effectively giving each score the same “weight”. This is a problem if a student has taken lots of test in that class and scored well it is giving it the same weight as someone who takes 1 test and scores 10% clearly pulling the average down when it was a one off

---

What I would do is

1. make a query with totals turned on.
2. Do an inner-join of [Class Type] and [Class Date] (btw. if you rename those tables without spaces in their names your queries and code will look a lot cleaner),
3. Group by the primary key for [Class Type], and on [Class Date].[date]; and then on [Class Date].[Test Score], change the GroupBy to Avg.
4. Save the resulting query
5. In a second query, put all the other data you want from [Student], [Class Type] and [Class Date]; and inner-join it to the first query on [Class Type]'s primary key
6. Save the resulting query
7. Build your report based on the second query, group it by date, show the footer and put the Avg value in the group footer.

(you can do sub total's for report groups, but I haven't found a way to do averages nicely)