Limit average to 1st 4 Scores

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

Guest

I have a table of individuals with scores & I'd like to average the first 4
(chronologically) scores for each month.

tblScores:

Individual Month CreateDt Score

NM1 Jan 1/1/07 7
NM1 Jan 1/3/07 9
NM2 Jan 1/1/07 5

Many Thanks!
 
Try this ---
SELECT Q.Individual, Format([CreateDt],"mmmm yyyy") AS Score_Date,
Avg(Q.Score) AS Score_AVG
FROM tblScores AS Q
WHERE ((((SELECT COUNT(*) FROM tblScores Q1
WHERE Q1.[Individual] = Q.[Individual]
AND Q1.[CreateDt] < Q.[CreateDt])+1)<=4))
GROUP BY Q.Individual, Format([CreateDt],"mmmm yyyy"), Q.[CreateDt]
ORDER BY Q.Individual, Format([CreateDt],"yyyymm");
 
David127 said:
I have a table of individuals with scores & I'd like to average the first 4
(chronologically) scores for each month.

tblScores:

Individual Month CreateDt Score

NM1 Jan 1/1/07 7
NM1 Jan 1/3/07 9
NM2 Jan 1/1/07 5
Click to expand...
Hi David,

In addition to Karl's sage help,
here might be a slightly different
approach that verifies had 4 scores
from correct dates
in each Individual/M1Y group:

{M1Y is just date adjustment to first of month/year}

SELECT
Q.Individual,
DateSerial(Year(Q.CreateDt),Month(Q.CreateDt),1) AS M1Y,
Min(Q.CreateDt) AS MinGrpDt,
Max(Q.CreateDt) AS MaxGrpDt,
Count(*) AS GrpCnt,
Sum(Q.Score) AS GrpSum,
Avg(Q.Score) AS Score_AVG
FROM tblScores AS Q
WHERE
(
SELECT COUNT(*)
FROM
tblScores AS Q1
WHERE
Q1.Individual = Q.Individual
AND
Q1.CreateDt <= Q.CreateDt
AND
DateSerial(Year(Q1.CreateDt),Month(Q1.CreateDt),1)
=DateSerial(Year(Q.CreateDt),Month(Q.CreateDt),1)
) <= 4
GROUP BY
Q.Individual,
DateSerial(Year([CreateDt]),Month([CreateDt]),1)
ORDER BY
Q.Individual,
DateSerial(Year(Q.CreateDt),Month(Q.CreateDt),1);

I know its more than you wanted, but you don't have to
show all fields in report (or on form), and it should give
you confidence that it is working properly.

On a side note, besides "Month" being an Access reserved word,
I suspect it is also a redundant, unneeded field? If it is just the
month of CreateDt, it can always be derived from CreatDt, can it not?

Keeping M1Y as Date/Time also allows you to easily derive "Month"
in a report or on a form.

good luck,

gary
 
Back
Top