Solved

Access Query to Select the 2 Most Recent Records of Each ClientID.

Posted on 2013-01-25
7
392 Views
Last Modified: 2013-01-29
Good afternoon Experts,

I need a query that will select the 2 most recent records for each Client_ID, based on the date in the update_date_utc field. There may be 1, 2, 3, or more instances of each Client_ID. If only 1 instance exists, I don't want it selected. If 2 instances exist, I want both instances selected, and if 3 or more instances exist, I want the 2 most recent instances based on the date in the update_date_utc field. See attached spreadsheet.

Thanks in advance for your help.
Craig
ChangeOwner.xls
0
Comment
Question by:CraigMan
  • 3
  • 2
  • 2
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38820995
Try this:

SELECT t.client_id, t.user_name, t.update_date_utc
FROM tblClients t
WHERE t.client_id IN (
        SELECT TOP 2 cdupe.client_id 
        FROM tblClients cdupe 
        WHERE cdupe.client_id  = t.client_id  AND  cdupe.client_id  IN 
               (SELECT c.client_id
                FROM tblClients c
                GROUP BY c.client_id
                HAVING Count(c.user_name) > 1) 
        ORDER BY cdupe.update_date_utc DESC
)

Open in new window


And take a look at the following article under the heading "top N records per group":
http://allenbrowne.com/subquery-01.html

This is a variation on that, with the HAVING clause limiting the output to groups of two or more.

You may have to replace occurrences of tblClients with your own table name.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38822998
Removed comment, not for this question.
0
 

Author Comment

by:CraigMan
ID: 38831204
mbixup,

Your solution did eliminate the single instances of client_id, but did not limit the result to the 2 most current records per client_id, if more than 2 instances exist. How do I limit output to 2 most current records?

Craig
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38831450
Here you go...

SELECT t.client_id, t.user_name, t.update_date_utc
FROM tblClients AS t
WHERE (t.client_id &  t.update_date_utc) IN  
(
        SELECT TOP 2 cdupe.client_id & cdupe.update_date_utc 
        FROM tblClients cdupe 
        WHERE cdupe.client_id  =  t.client_id  and  cdupe.client_id  IN 
               (SELECT c.client_id
                FROM tblClients c
                GROUP BY c.client_id
                HAVING Count(c.user_name) > 1) 
        ORDER BY cdupe.update_date_utc DESC
) 

Open in new window


Try Query1 in the attached  DB
db2.mdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38832067
Try this:
For table a(aid, f1, f2) - aid is the primary key.
aID      f1      f2
1      1      1
2      1      2
3      1      3
4      2      2
5      4      3
6      4      5

Required output:
aid      f1      f2 - recent as higher f2 means more recent
2      1      2
1      1      1
6      4      5
5      4      3

SELECT a.aid, a.f1, a.f2
FROM a
WHERE ((((select count(x.f1) from a x where a.f1=x.f1))>1) AND (((select count(x.f1) from a x where a.f1=x.f1 and a.aid>=x.aid))<3))
ORDER BY a.f1, a.f2 DESC;

Open in new window

Output:
aid      f1      f2
2      1      2
1      1      1
6      4      5
5      4      3
0
 

Author Closing Comment

by:CraigMan
ID: 38832074
Just what I needed

Thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38832135
Glad to help :)
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now