Include a field not in aggregate function

[HomeGrowth]

Is there a way to include a field that is not in the aggregate function, for example I have the following records:

Name Task Date RecordID
Ann 1 1/1/05 Ann1
Ann 1 1/3/05 Ann2
Ann 1 1/28/05 Ann3
Ann 1 1/31/05 Ann4
Ann 2 1/3/05 Ann5
Ann 2 1/13/05 Ann6
Ann 2 1/24/05 Ann7
Ann 2 1/30/05 Ann8
Brian 1 2/5/05 Bri9
Brian 1 2/13/05 Bri10
Brian 1 2/25/05 Bri11
Brian 1 2/28/05 Bri12
Brian 2 2/4/05 Bri13
Brian 2 2/12/05 Bri14
Brian 2 2/21/05 Bri15
Brian 2 2/21/05 Bri16

Find the RecordID for Max/Last Date when the user completed the each task (This is what I want).

Name Task Date RecordID
Ann 1 1/31/05 Ann4
Ann 2 1/30/05 Ann8
Brian 1 2/28/05 Bri12
Brian 2 2/21/05 Bri16

My query is grouping Name & Task and Max the Date and RecordID fields, every one looks fine, except the third on. It returned Bri9 as RecordID (This is what I got).

Name Task Date RecordID
Ann 1 1/31/05 Ann4
Ann 2 1/30/05 Ann8
Brian 1 2/28/05 Bri9
Brian 2 2/21/05 Bri16

How to do this correctly? Can someone help?

 

[PHV]

SELECT A.Name, A.Task, A.Date, A.RecordID
FROM yourTable AS A INNER JOIN (
SELECT [Name], Task, Max([Date]) AS MaxDate FROM yourTable GROUP BY [Name], Task
) AS B ON A.Name = B.Name AND A.Task = B.Task AND A.Date = B.MaxDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HomeGrowth]

Thanks for the reply, I have a difficult time to interpret this SQL statement. Can you explanin it or put in access query method. Thanks!

 

[PHV]

put in access query method
This is access query SQL code.
When in the query window choose the SQL view pane and paste the code.
You have to tweak the table name and perhaps the field names too.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HomeGrowth]

Thanks. It works great!

Gotta go learn more SQL codes...