Filtering unique values, with a bit of a twist

[James Armstrong]

Hi everybody, here's an example of what I'm trying to do with an MS Access query.

Num |Name | Occu | Date
111 |Jon Smith | Painter | 01/01/2012
111 |Jon Smith | Painter | 01/02/2012
111 |Jon Smith | Painter | 01/08/2012
111 |Jon Smith | Artist | 01/04/2012
111 |Jon Smith | Artist | 01/05/2012
111 |Jon Smith | Artist | 01/06/2012
222 |Ted Smith | Baker | 01/01/2012
222 |Ted Smith | Baker | 01/02/2012
222 |Ted Smith | Cop | 01/03/2012
222 |Ted Smith | Cop | 01/04/2012


I want to create a query in MS Access that will filter out all of the duplicate occupations and leave me with the most recent dated occupation for each. So my query would return:

111 |Jon Smith | Painter |01/08/2012
111 |Jon Smith | Artist |01/06/2012
222 |Ted Smith | Baker |01/02/2012
222 |Ted Smith | Cop |01/04/2012

I'm having a heck of a time figuring it out. Anybody have any suggestions?

[Rabbit]

Use an aggregate query that returns the MAX date and groups by the other fields.

[NeoPa]

Your grouping appears to be by [Num] and [Occu]. You need an aggregate query that finds the maximum date within the group, then to use that query as a subquery of another query that does an INNER JOIN to the table on those three fields. Something similar to :

SELECT tYT.*
FROM   [YourTable] AS [tYT]
       INNER JOIN
       (SELECT   [Num]
               , [Occu]
               , Max([Date]) AS [MaxDate]
        FROM     [YourTable]
        GROUP BY [Num]
               , [Occu]) AS [subQ]
  ON   tYT.Num=subQ.Num
 AND   tYT.Occu=subQ.Occu
 AND   tYT.Date=subQ.MaxDate

[James Armstrong]

Thanks NeoPa and Rabbit. I think I have it working correctly. The first suggestion by Rabbit actually produced the results I was looking for. But it would only work if those were the only three fields in the query. So then I moved on to NeoPa's suggestion and created two separate queries. To save some hassle I added a primary key to the table and used the inner join based on that. Thanks again!

[NeoPa]

You're welcome James. My post was simply a clarification of Rabbit's. There was no real difference between the two suggestions.

I'm not sure what you mean by linking on the PK from your explanation. It may be clearer with the SQL posted, but you have a working solution and I guess that's all that matters.