Need help aggregating and using WHERE

[Sandy S]

Hi,

I hope you may help me by providing the proper syntax to
perform the following query (either in design view or in
SQL).

I have a table that lists associate records by ID and
month. For each month the associate receives a numeric
score in a field labeled MonthlyScore.

I need to devise a way to average scores based on the
month for the Quarter they are in, and only display the
average on the month that ends the quarter. For example,
Susie has the following scores:

Month MonthlyScore

January 3.5
February 4.0
March 3.1
June 2.5
July 3.2
August 3.7

I want an average for January, February, and March and
display it on the record where the Month = March.

I want an average score for June, July, and August and
display it on the record where the Month = August.

The reason I need to do this is that I have a monthly
report that needs to show quarterly results in the same
report. It is not really preferred to display in a
subreport so any help you may provide is MUCH appreciate!

Thank you in advance!

Sandy S.

 

[Peter Hoyle]

Assuming you have 4 fields in the query: -

ID Qtr Mth Score
23 1 1 3.5
23 1 2 4.0
23 1 3 3.1

You could try something like

SELECT ID, Qtr, Max(Mth) As Month, Avg(Score) As QtrlyScore
FROM MyTableOrQuery
GROUP BY ID, Qtr

This could be linked back with the original monthly query using a left join

SELECT ID, Qtr, Mth, Score, QtrlyScore
FROM MyOriginalGroupedMthlyQuery
LEFT JOIN
MyNewQtrlyQuery
ON ID = ID AND
Month = Month


Cheers,
Peter

 

[Guest]

Peter,

Thank you SO much! I believe this will work just
perfectly!

Cheers to you as well,

Sandy