By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

SQL problem - aggregate function

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT   Last(TT_TestData.[Date of Data Export]) AS [LastOfDate of Data Export],
  2.          TT_TestData.[Class ID],
  3.          Avg(TT_TestData.Average) AS AvgOfAverage,
  4.          TT_TestData.[Test Form],
  5.          TT_TestData.[School Year],
  6.          TT_TestData.[School Name]
  7.  
  8. FROM     TT_StudentData RIGHT JOIN TT_TestData
  9.   ON     TT_StudentData.SSID = TT_TestData.SSID
  10.  
  11. GROUP BY TT_TestData.[Class ID],
  12.          TT_TestData.[Test Form],
  13.          TT_TestData.[School Year],
  14.          TT_TestData.[School Name],
  15.          TT_TestData.[Date of Data Export]
  16.  
  17. HAVING   (((TT_TestData.[Class ID])="182")
  18.    AND   ((TT_TestData.[Test Form])="B")
  19.    AND   ((TT_TestData.[School Year])=[Forms]![F_Start_SchoolSelection]![SchoolYearCOMBO])
  20.    AND   ((TT_TestData.[School Name])=[Forms]![F_Switchboard]![SchoolNameTextBox])
  21.    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!
Mar 12 '10 #1
Share this Question
Share on Google+
5 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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?
Mar 12 '10 #2

P: 4
Changing it to Max has no effect.
Mar 12 '10 #3

NeoPa
Expert Mod 15k+
P: 31,769
You are GROUPing by date ([Date of Data Export]), but you need to GROUP by Month([Date of Data Export]).

Welcome to Bytes!
Mar 13 '10 #4

P: 4
I'm sorry...I still don't follow. I really appreciate your help.
Mar 15 '10 #5

NeoPa
Expert Mod 15k+
P: 31,769
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) :
Expand|Select|Wrap|Line Numbers
  1. Month(TT_TestData.[Date of Data Export])
Does that make it clearer?
Mar 15 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.