Query - Choosing Scores to Average

[Guest]

I have a list of items and scores. Some items have more scores than others.
Each score corresponds to a date (there can only be one score per date). For
items with more than 3 scores, I would like to calculate the average of the 2
most recent prior scores to compare to the most recent (current) score.

Any suggestions?

Thank you!

 

[Ken Snell \(MVP\)]

Probably will help if you can post some sample data and tell us what the
results should be. Your description gives me a rough idea, but.... seeing
some examples will help.

 

[Guest]

Thank you for replying.
Here is an example
ID Item Date Score
1 a 1/15/2006 0.75
2 a 3/31/2006 0.88
3 a 6/29/2006 0.90
4 d 3/18/2006 0.87
5 d 6/17/2006 0.93
6 e 1/3/2006 0.84
7 e 2/26/2006 0.88
8 e 4/24/2006 0.94
9 e 6/7/2006 0.97
10 f 8/4/2006 0.88

For a, I would like the average of .88 and .75 (.82) so that I can compare
to .9
For d, no average is needed b/c only 2 scores are available
For e, I would like the average of .94 and .88 (.91) so that I can compare
to .97
For f, no average is needed b/c only 1 score is available

 

[Ken Snell \(MVP\)]

You'll need two separate queries.

The first query will obtain the averages of the second and third most recent
scores for each item when there are at least three scores in the table.
Create and save the query below (change TabName to your real table name, and
any other field names to the real names) and save it as "qry_Avgs":

SELECT T1.Item, Avg(T1.Score) AS AvgOfScore
FROM TabName AS T1
WHERE (((T1.Date) In (SELECT TOP 2 TZ.[Date]
FROM TabName AS TZ
WHERE TZ.[Item] = T1.[Item]
AND TZ.[Item] IN
(SELECT TT.[Item]
FROM TabName AS TT
GROUP BY TT.[Item]
HAVING Count(TT.[Score]) >2)
AND TZ.[Date]<
(SELECT Max(TB.[Date])
FROM TabName AS TB
WHERE TB.[Item] = T1.[Item])
ORDER BY TZ.[Date] DESC)))
GROUP BY T1.Item;


Then create a second query (name it "qry_Compare") as noted below; again
change the names to the real names:

SELECT TabName.Item, First(TabName.Score) AS FirstOfScore,
First(qry_Avgs.AvgOfScore) AS FirstOfAvgOfScore
FROM TabName LEFT JOIN qry_Avgs
ON TabName.Item = qry_Avgs.Item
WHERE (((TabName.[Date])=
(SELECT Max(TG.[Date])
FROM TabName AS TG
WHERE TG.[Item]=TabName.[Item])))
GROUP BY TabName.Item;


If there are not at least three scores in the table, then qry_Compare will
return a Null value for the Average for that item.


Also, do not use Date as the name of a field in a table. It and many other
words are reserved words in ACCESS and should not be used for field names,
etc. See these Knowledge Base articles for more information about reserved
words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

 

[Guest]

Worked beautifully. Thank you so much!

Also, thank you for the references. I'll make sure to check them in the
future!

You'll need two separate queries.

The first query will obtain the averages of the second and third most recent
scores for each item when there are at least three scores in the table.
Create and save the query below (change TabName to your real table name, and
any other field names to the real names) and save it as "qry_Avgs":

SELECT T1.Item, Avg(T1.Score) AS AvgOfScore
FROM TabName AS T1
WHERE (((T1.Date) In (SELECT TOP 2 TZ.[Date]
FROM TabName AS TZ
WHERE TZ.[Item] = T1.[Item]
AND TZ.[Item] IN
(SELECT TT.[Item]
FROM TabName AS TT
GROUP BY TT.[Item]
HAVING Count(TT.[Score]) >2)
AND TZ.[Date]<
(SELECT Max(TB.[Date])
FROM TabName AS TB
WHERE TB.[Item] = T1.[Item])
ORDER BY TZ.[Date] DESC)))
GROUP BY T1.Item;


Then create a second query (name it "qry_Compare") as noted below; again
change the names to the real names:

SELECT TabName.Item, First(TabName.Score) AS FirstOfScore,
First(qry_Avgs.AvgOfScore) AS FirstOfAvgOfScore
FROM TabName LEFT JOIN qry_Avgs
ON TabName.Item = qry_Avgs.Item
WHERE (((TabName.[Date])=
(SELECT Max(TG.[Date])
FROM TabName AS TG
WHERE TG.[Item]=TabName.[Item])))
GROUP BY TabName.Item;


If there are not at least three scores in the table, then qry_Compare will
return a Null value for the Average for that item.


Also, do not use Date as the name of a field in a table. It and many other
words are reserved words in ACCESS and should not be used for field names,
etc. See these Knowledge Base articles for more information about reserved
words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

Thank you for replying.
Here is an example
ID Item Date Score
1 a 1/15/2006 0.75
2 a 3/31/2006 0.88
3 a 6/29/2006 0.90
4 d 3/18/2006 0.87
5 d 6/17/2006 0.93
6 e 1/3/2006 0.84
7 e 2/26/2006 0.88
8 e 4/24/2006 0.94
9 e 6/7/2006 0.97
10 f 8/4/2006 0.88

For a, I would like the average of .88 and .75 (.82) so that I can compare
to .9
For d, no average is needed b/c only 2 scores are available
For e, I would like the average of .94 and .88 (.91) so that I can compare
to .97
For f, no average is needed b/c only 1 score is available