Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2018, 07:00 AM   #1
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 543
Thanks: 129
Thanked 38 Times in 37 Posts
AOB is on a distinguished road
Delete all but most recent record per user (AC2013)

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.



Any suggestions?

Thanks

AOB

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 04-17-2018, 07:29 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,513
Thanks: 10
Thanked 1,239 Times in 1,179 Posts
MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 04-17-2018, 07:38 AM   #3
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 543
Thanks: 129
Thanked 38 Times in 37 Posts
AOB is on a distinguished road
Re: Delete all but most recent record per user (AC2013)

Quote:
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

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 04-17-2018, 07:56 AM   #4
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,513
Thanks: 10
Thanked 1,239 Times in 1,179 Posts
MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 04-17-2018, 08:03 AM   #5
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 543
Thanks: 129
Thanked 38 Times in 37 Posts
AOB is on a distinguished road
Re: Delete all but most recent record per user (AC2013)

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?
__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 04-17-2018, 08:07 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,051
Thanks: 120
Thanked 1,384 Times in 1,356 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Delete all but most recent record per user (AC2013)

What doesn't a simple sort of

SELECT Distinct UserID, Max(LoginTimeStamp)

should return the last record.
__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
AOB (Yesterday)
Old 04-17-2018, 08:15 AM   #7
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,513
Thanks: 10
Thanked 1,239 Times in 1,179 Posts
MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light
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 <> <the RowID from the previous query>
hth
Mark

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
AOB (Yesterday)
Old Yesterday, 03:13 AM   #8
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 543
Thanks: 129
Thanked 38 Times in 37 Posts
AOB is on a distinguished road
Re: Delete all but most recent record per user (AC2013)

Thanks guys

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

Quote:
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;

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add Yes/ No to Dialogue - When user clicks Yes delete Record when user clicks no keep Sue22 Modules & VBA 5 10-08-2015 05:13 AM
Access FE user auto copy most recent file AccessUser4 General 4 06-04-2013 06:56 AM
Delete all records with most recent date NZArchie Queries 2 01-16-2012 07:22 PM
Delete particular record if user of form the record creator? kate10123 Forms 2 12-05-2008 09:10 AM
Find most recent invoice and most recent funding for client Niyx Queries 3 02-19-2008 07:15 AM




All times are GMT -8. The time now is 05:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World