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

Filtering unique values, with a bit of a twist

P: 12
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?
May 14 '12 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,355
Use an aggregate query that returns the MAX date and groups by the other fields.
May 14 '12 #2

NeoPa
Expert Mod 15k+
P: 31,411
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT tYT.*
  2. FROM   [YourTable] AS [tYT]
  3.        INNER JOIN
  4.        (SELECT   [Num]
  5.                , [Occu]
  6.                , Max([Date]) AS [MaxDate]
  7.         FROM     [YourTable]
  8.         GROUP BY [Num]
  9.                , [Occu]) AS [subQ]
  10.   ON   tYT.Num=subQ.Num
  11.  AND   tYT.Occu=subQ.Occu
  12.  AND   tYT.Date=subQ.MaxDate
May 14 '12 #3

P: 12
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!
May 15 '12 #4

NeoPa
Expert Mod 15k+
P: 31,411
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.
May 16 '12 #5

Post your reply

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