Grouping problem

[Mi]

Hello,
I have the following table:

Person Item Type Date
ABC 1Z IN 2/15/05
ABC 1Z RO 2/16/05
ABC 2Z RI 2/15/05
ABC 3Z RI 2/15/05
ABC 3Z RO 2/16/05

I need a query that will return those Items with the most current date and the type for that record. I tried the use Max in the groupby under Date but can not get it right. I want to return the following:

ABC 1Z RO 2/16/05
ABC 2Z RI 2/15/05
ABC 3Z RO 2/16/05

Any help would be appreciated.

Mi

 

[Andrew Fergus]

Hi Mi
If you include the 'Type' field then it is trying to group by that too, before working out the max value. You might need to do this with 2 queries. The first query has the person, item and date (max) fields. The second query links the first query to the table (link all 3 fields & select all from the query with matching records from the table) and the 2nd query returns the person, item and date fields from query 1 and the 'type' field from the table.
HTH, Andrew.

 

[Mi]

Thanks,
While I could not get the qry's to link, I was able to get it to work. After looking further into the table, I found a method to their tracking.

Thanks anyways.
Mi