Monthly year to date average

[Stumped]

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.

 

[Michel Walsh]

*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

 

[Stumped]

Unfortunately, I have several entries per month, and sometimes no
entries...any ideas?

 

[Ofer Cohen]

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