Limiting results by using another Field

[Perry Kew]

I have two fields called TestScores and ClassGrade in an
Access 2000 database

TestScores are the scores of various students in a Social
Science Test and ClassGrade is the grade these students
belong to. ClassGrade has values from 01 to 12

I would like to get the average score for grades 01-04, 05-
08, and 09-12 in the same query. I am able to get this by
performing three different total queries and using the
Where criteria on ClassGrade.

Anyone know of a way where I can drag TestScore three
times to the Grid and use the ClassGrade field as the
criteria (01 or 02 or 03 or 04 for the first TestScore)?

Thank you.

--Perry

 

[Michel Walsh]

Hi,

The Group By clause accepts computed expressions:


SELECT Switch( ClassGrade IN(1, 2, 3,4), 1,
ClassGrade IN(5, 6, 7,8) 2,
ClassGrade IN(9, 10, 11, 12), 3),
AVG(score)

FROM myTable

GROUP BY ClassGrade IN(1, 2, 3,4),
ClassGrade IN(5, 6, 7,8),
ClassGrade IN(9, 10, 11, 12)



Hoping it may help,
Vanderghast, Access MVP