Monthly year to date average

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have scores for given records for a particular date:

3/15/07 67
4/1/07 43
1/15/07 56

I would like to be able to average these scores by month, but have a running
total:

January 56
February 56
March 61.5 (average 56 & 67)
April 55.3 (average 56, 67, and 43)

I think I need to use a DAvg function, but it isn't working yet.
 
*IF* you have only ONE record per month, you can try:


SELECT a.date, LAST(a.qty), AVG(b.qty)
FROM tableName As a INNER JOIN tableName As b
ON a.date >= b.date
GROUP BY a.date



that would give:

1/15/2007 56 56
3/15/2007 67 61.5
4/1/2007 43 53.3



you can then format the first column as: Format( fieldName, "yyyy mmm") :

? Format(now, "yyyy mmm")
2007 Apr


You can replace LAST by SUM, MAX, MIN, FIRST, ... since, anyhow, it operates
on a single row, HERE, that does not really matter, as long as there is an
aggregate.

If there is no record for a given month (no record for February), it won't
appear in the result.



Hoping it may help,
Vanderghast, Access MVP
 
You can do that using two queries
1. Sum Per month

SELECT Month([DateField]) AS MyMonth, Year([DateField]) AS MyYear,
Sum(TableName.scores) AS SumOfScore
FROM TableName
GROUP BY Month([DateField]), Year([DateField])

2. The Avg and accumlative query based on the first query

SELECT MyQueryName.MyYear, MyQueryName.MyMonth,
DSum("SumOfScore","MyQueryName","MyMonth <=" & [MyMonth] & " And MyYear<=" &
[MyYear]) AS RunningSum, MyQueryName.SumOfScore,
DAvg("SumOfScore","MyQueryName","(MyMonth <=" & [MyMonth] & " And MyYear=" &
[MyYear] & ") Or MyYear < " & [MyYear]) AS MonthAvg
FROM MyQueryName
ORDER BY MyQueryName.MyYear, MyQueryName.MyMonth
 
Back
Top