SQL problem - aggregate function

[skygoodwin]

I have a database with multiple records during each month.

Let's say in March, there is data for March 1, March 15, and March 22.

I need a query that can return the average student test scores for March, but also return only the average for the scores on March 22.

Is there a way to combine the functions:

Month([Date of Data Export])=3

along with something that returns the LAST date within the month of March?

Here's what I have so far:

SELECT   Last(TT_TestData.[Date of Data Export]) AS [LastOfDate of Data Export],
         TT_TestData.[Class ID],
         Avg(TT_TestData.Average) AS AvgOfAverage,
         TT_TestData.[Test Form],
         TT_TestData.[School Year],
         TT_TestData.[School Name]

FROM     TT_StudentData RIGHT JOIN TT_TestData
  ON     TT_StudentData.SSID = TT_TestData.SSID

GROUP BY TT_TestData.[Class ID],
         TT_TestData.[Test Form],
         TT_TestData.[School Year],
         TT_TestData.[School Name],
         TT_TestData.[Date of Data Export]

HAVING   (((TT_TestData.[Class ID])="182")
   AND   ((TT_TestData.[Test Form])="B")
   AND   ((TT_TestData.[School Year])=[Forms]![F_Start_SchoolSelection]![SchoolYearCOMBO])
   AND   ((TT_TestData.[School Name])=[Forms]![F_Switchboard]![SchoolNameTextBox])
   AND   ((Month([Date of Data Export]))=3));

This returns the averages for March, but gives me all dates within March.

Thank you for your help!

[TheSmileyCoder]

Last will take the Last record as far as I know (My knowledge is more in the VBA section), but have you tried to replace Last with Max?

[skygoodwin]

Changing it to Max has no effect.

[NeoPa]

You are GROUPing by date ([Date of Data Export]), but you need to GROUP by Month([Date of Data Export]).

Welcome to Bytes!

[skygoodwin]

I'm sorry...I still don't follow. I really appreciate your help.

[NeoPa]

If you look at line #15 of your Original Post, you will see that you are GROUPing by the date. If every separate date then falls into a separate group, it makes little sense to look for either the Last() or the Max() of the date within any of the groups. They will clearly always all be the same date.

What I think you want there is to replace line #15 with (suitably indented of course) :

Month(TT_TestData.[Date of Data Export])

Does that make it clearer?