The Problem (Q-score 5, ranked #47th of 67 in the Access VBA archive)
The scenario as originally posted in 2009
Why doesn’t the average of the score of an employee of each month, when summed, equal the average of the employees score (ever)?
Average
SELECT Avg(r.score) AS rawScore
FROM (ET INNER JOIN Employee AS e ON ET.employeeId = e.id) INNER JOIN (Employee AS a INNER JOIN Review AS r ON a.id = r.employeeId) ON ET.id = r.ETId
WHERE (((e.id)=@employeeId))
Returns 80.737
Average By Month
SELECT Avg(r.score) AS rawScore, Format(submitDate, 'mmm yy') AS MonthText, month(r.submitDate) as mm, year(submitDate) as yy
FROM (ET INNER JOIN Employee AS e ON ET.employeeId = e.id) INNER JOIN (Employee AS a INNER JOIN Review AS r ON a.id = r.employeeId) ON ET.id = r.ETId
WHERE (((e.id)=@employeeId))
GROUP BY month(r.submitDate), year(submitDate), Format(submitDate, 'mmm yy')
ORDER BY year(submitDate) DESC, month(r.submitDate) DESC
Returns
Average Score : Month
81.000 : Oct 09
80.375 : Sep 09
82.700 : Aug 09
83.100 : Jul 09
75.625 : Jun 09
I know 80.737 is correct because I have tallied up the records by hand and done the average. But the average of this table (at 3 decimal places), is 80.56 which is too far off. Does group by mess with the rounding at each step?
Why community consensus is tight on this one
Across 67 Access VBA entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — niche answer (below median) (+7)
Verbal answer — walkthrough without a code block
Note: the verified answer is a prose walkthrough. If you need a runnable sample, check Access VBA entries ranked in the top 10 of the same archive.
An average of average values will not return the same result as a single average over all values, unless all the groups averaged have the same number of items.
If there are different numbers of employees rawScore each month it will be skewing your results.
Consider this example: if we calculate the average of the numbers 1 through 10 the average is 5.5.
Calculating the average of the numbers from 1 through 5 the average is 3, and of 6 through 10 is 8. Both groups have 5 items so the average of 3 and 8 = 5.5.
However, if you take the first average as 1 and 2 = 1.5, and the second average as 3 through 10 = 6.5, then average 1.5 and 6.5 gives 4. This is skewed because the first group has 2 items, and the second has 8.
In addition to this will be the cumulative effects of rounding that Robert Harvey noted.
When to Use It — vintage (14+ years old, pre-2013)
Ranked #47th in its category — specialized fit
This pattern sits in the 77% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Access VBA archive for a higher-consensus alternative.
What changed between 2009 and 2026
The answer is 17 years old. The Access VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.