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

Help with max function in group by query... possible?

100+
P: 365
Hi guys,

It has come to my attention (thanks NeoPa), that i was doing something wrong...

i have a table of appraisal dates, stored as such
Expand|Select|Wrap|Line Numbers
  1. tblAppraisal
  2. AppID, Auto, PK
  3. StaffID, Num, FK - Linked to tblStaff
  4. AppMan, Num, (FK) - uses a different StaffID from tblStaff table, but is not linked (lazy? lol) it indicates the Staff Member responsible for the appraisal
  5. Date, D/T
  6.  
So obviously, for each member of staff there can be multiple dates for appraisal (used as a record of when performed).

I have a query that is "supposed" to get the last Date, and then only display those from over a year ago, which will then prompt for a new appraisal.

My SQL is as such
Expand|Select|Wrap|Line Numbers
  1. SELECT   tblAppraisal.AppID 
  2.        , tblAppraisal.StaffID 
  3.        , [Forename] & ' ' & [Surname] AS Name 
  4.        , tblAppraisal.Date 
  5. FROM     tblAppraisal 
  6.          INNER JOIN 
  7.          tblStaff 
  8.   ON     tblAppraisal.StaffID = tblStaff.StaffID 
  9. GROUP BY tblAppraisal.AppID 
  10.        , tblAppraisal.StaffID 
  11.        , [Forename] & ' ' & [Surname] 
  12.        , tblAppraisal.Date 
  13.        , tblStaff.Deleted 
  14.        , tblAppraisal.AppMan 
  15. HAVING   (((tblStaff.Deleted)=False) 
  16.    AND   ((tblAppraisal.AppMan)=???) 
  17.    AND   ((Max(tblAppraisal.Date))<DateAdd('yyyy',-1,Date()))) 
  18.  
and i assumed it worked, but never really investiagted it, it seems that the group by occurs first so i dont get the "Max", which is what NeoPa so kindly pointed out to me in another thread.

My question now is, can this be done from a single Query? i can solve it myself by doing two, thats easy, but i don't like to do things the easy way... i can also think of a way it can be done by writing a VBA function to give me the date, but again that seems sloppy if it can be done within the SQL.

Hope my midnight ramblings make sense

Cheers
May 6 '12 #1

✓ answered by Rabbit

I would do something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT someTable.*
  2. FROM someTable
  3. INNER JOIN (
  4.    SELECT IDField, MAX(dateField) AS MaxDate
  5.    FROM someTable
  6.    GROUP BY IDField
  7. ) AS MaxDates ON 
  8.    someTable.IDField = MaxDates.IDField
  9.    AND someTable.dateField = MaxDates.MaxDate

Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Use a subquery that returns just the max date by staff id and then join that back to the table.
May 7 '12 #2

100+
P: 365
Hi, i've never used a sub query, ive just done some googling... quite complicated? or just me? lol

anyways, i tried to add a Date expression field with the following SQL:
Expand|Select|Wrap|Line Numbers
  1. WHERE Date = 
  2.      (SELECT tblAppraisal.StaffID, 
  3.      Max(tblAppraisal.Date) AS MaxOfDate 
  4. FROM 
  5.      tblStaff 
  6. INNER JOIN 
  7.      tblAppraisal 
  8. ON 
  9.      tblStaff.StaffID = tblAppraisal.StaffID 
  10. GROUP 
  11.      BY tblAppraisal.StaffID)
But i couldn't figure out houw to display the StaffID in the query, access gave me an error (use EXISTS in subquery....) and without StaffID in the subquery it just shows 1 record (the last)

Any pointers?
May 7 '12 #3

100+
P: 365
Hello again, tried it a different way which might be more appropraite... but still doesnt work as planned:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblAppraisal.AppID
  2.   , tblAppraisal.StaffID
  3.   , [Forename] & ' ' & [Surname] AS Name
  4.   , (SELECT Max(tblAppraisal.Date) AS MaxOfDate  
  5.         FROM tblStaff 
  6.         INNER JOIN 
  7.         tblAppraisal ON
  8.         tblStaff.StaffID 
  9.         = tblAppraisal.StaffID) AS Date
  10. FROM tblStaff
  11. INNER JOIN 
  12.         tblAppraisal ON tblStaff.StaffID 
  13.         = tblAppraisal.StaffID
  14. GROUP BY tblAppraisal.AppID
  15.   , tblAppraisal.StaffID
  16.   , [Forename] & ' ' & [Surname];
  17.  
I get the required number of rows but the dates are all the same (the absoloute max Date)
May 7 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
I will look at this for you Dan, but short of time for now.

Have a look first though, at Subqueries in SQL. Remember that you can always save a subquery as a QueryDef too, if that makes it easier for you.

** Ooops ** I just noticed you don't want to use the easy way out. Good for you. In that case I'll suggest that you split the requirement up into two steps :
  1. Produce a query, that can later be converted into a sub-query, that returns the data that you need to do your second grouping by. It needs to contain potentially multiple values of [Date], but group by other fields.
  2. A query that returns the Max() value of [Date] within the group of #1 above, as well as the other required data.
May 7 '12 #5

100+
P: 365
Thanks again NeoPa, and Rabbit

I did as you both suggested, created a query to use as the sub query, but I can't figure out how to link StaffID between them or to return it as a field from the subquery in my example above. Hmmmmmm
May 7 '12 #6

Rabbit
Expert Mod 10K+
P: 12,315
I would do something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT someTable.*
  2. FROM someTable
  3. INNER JOIN (
  4.    SELECT IDField, MAX(dateField) AS MaxDate
  5.    FROM someTable
  6.    GROUP BY IDField
  7. ) AS MaxDates ON 
  8.    someTable.IDField = MaxDates.IDField
  9.    AND someTable.dateField = MaxDates.MaxDate
May 7 '12 #7

100+
P: 365
Thanks for the tip Rabbit, I'll give it a whirl tomorrow and post my results
May 8 '12 #8

100+
P: 365
Hi guys, i think i have got it,
Expand|Select|Wrap|Line Numbers
  1. SELECT MaxDates.StaffID
  2.   , [Forename] & ' ' & [Surname] AS Name
  3.   , MaxDates.MaxDate
  4. FROM [SELECT tblAppraisal.StaffID
  5.   , MAX(tblAppraisal.Date) AS MaxDate 
  6.     FROM tblAppraisal GROUP BY StaffID]. 
  7.     AS MaxDates 
  8. INNER JOIN (tblStaff 
  9.   INNER JOIN tblAppraisal 
  10.   ON tblStaff.StaffID = tblAppraisal.StaffID) 
  11.   ON (tblAppraisal.StaffID = MaxDates.StaffID) 
  12.     AND (MaxDates.MaxDate = tblAppraisal.Date)
  13. WHERE (((tblStaff.Deleted)=False) 
  14.   AND ((tblAppraisal.AppMan)=6))
  15. GROUP BY MaxDates.StaffID
  16.   , [Forename] & ' ' & [Surname]
  17.   , MaxDates.MaxDate
  18. HAVING (((MaxDates.MaxDate)<DateAdd('yyyy',-1,Date())));
I had to build it simply and then cheated (using the query builder) to do the rest, but i think i understand it now.

Thanks for the help once again guys, let me know if i've made some other obvious mistake
May 8 '12 #9

Post your reply

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