Delete all but most recent record per user (AC2013) (1 Viewer)

AOB

Registered User.
Local time
Today, 06:29
Joined
Sep 26, 2012
Messages
613
Hi guys,

I have a table of records, multiple fields but key fields here are a UserID (Text) and a Login timestamp (Date/Time). A UserID may appear across multiple records (different devices).

I want to run a delete query that deletes all but the most recent record for each distinct UserID, based on the Login timestamp.

i.e. If a particular UserID appears 5 times in the table, delete the 4 oldest records according to the Login timestamp and retain just the most recent record. If a UserID appears just once, simply retain that record.

I've been messing with ranks and self joins and either my subquery is returning the wrong subset of data or the engine simply won't allow the operation at all.

I'm pretty sure this should be straightforward enough but I can't figure it out.

:banghead:

Any suggestions?

Thanks

AOB
 

MarkK

bit cruncher
Local time
Yesterday, 23:29
Joined
Mar 17, 2004
Messages
8,178
Why delete this data? It is doing no harm in the table, gives you a historical record of usage patterns, and it's easy enough to query the table to find the latest record. If it not broken, don't fix it.
hth
Mark
 

AOB

Registered User.
Local time
Today, 06:29
Joined
Sep 26, 2012
Messages
613
Why delete this data? It is doing no harm in the table, gives you a historical record of usage patterns, and it's easy enough to query the table to find the latest record. If it not broken, don't fix it.
A fair point, Mark, and duly noted, however let me explain...

The Access database I'm building is for reporting purposes. This is only a temporary copy. The main table (elsewhere) remains unchanged. Once the local table has been populated, there are a series of queries which need to be performed to produce the reporting output. To make these queries more efficient, I want to reduce the local data down to the bare minimum (we're talking a reduction from well in excess of 500k records to around 10k) In SQL Server, I wouldn't really care, but in Access, when the tables get bigger, the performance suffers. I need these reports to be zippy. I only need the most recent records - I don't want the others. Also, once this is finished, I'll be distributing the file to a number of people and I don't want them to have all those records either!

Hope this makes sense? Appreciate your point and normally that is what I would abide by but in this scenario, a "cleanse" will make life much easier from here on in.

Thanks

AOB
 

MarkK

bit cruncher
Local time
Yesterday, 23:29
Joined
Mar 17, 2004
Messages
8,178
If it's just a one-off operation, delete them manually. No need to design a process or query to do so, but also, in an indexed table, I doubt that even in Access you could notice the time difference between finding a row in 10K or 500K records. Yeah, maybe a sort on a non-indexed field you would notice, but...
Anyway, just a few thoughts...
Mark
 

AOB

Registered User.
Local time
Today, 06:29
Joined
Sep 26, 2012
Messages
613
It's not a one-off operation; I'm building the Access database as a quick tool for some folks to run some reporting on an ongoing basis. These reports will be refreshed / regenerated on a weekly basis. I don't want them to have to do anything manually, just click a button on a form and have the reports spit out quickly.

I don't want them staring at an hourglass for 20 minutes while my queries chug through half a million records when they're only interested in 2% of them?
 

Minty

AWF VIP
Local time
Today, 06:29
Joined
Jul 26, 2013
Messages
10,355
What doesn't a simple sort of

SELECT Distinct UserID, Max(LoginTimeStamp)

should return the last record.
 
  • Like
Reactions: AOB

MarkK

bit cruncher
Local time
Yesterday, 23:29
Joined
Mar 17, 2004
Messages
8,178
For a particular UserID you can get the latest RowID like this...
Code:
SELECT TOP 1 RowID FROM tYourTable WHERE UserID = 123 ORDER BY LoginTimeStamp DESC
Then delete all the row IDs for that user that aren't the one from the previous query...
Code:
DELETE FROM tYourTable WHERE UserID = 123 and RowID <> [I]<the RowID from the previous query>[/I]
hth
Mark
 

AOB

Registered User.
Local time
Today, 06:29
Joined
Sep 26, 2012
Messages
613
Thanks guys

I'd prefer to just remove the redundant records entirely but I guess this will work for me

What doesn't a simple sort of

SELECT Distinct UserID, Max(LoginTimeStamp)

should return the last record.
adapted to

Code:
SELECT UserID, Max(LoginTimeStamp) AS MostRecentLogin
FROM tblMyTable
GROUP BY UserID;
 

Users who are viewing this thread

Top Bottom