Append Remove History

[man199]

Hi Guys,

It been awhile since I have been here and used ms access, but really could use your help please.

I have a list of archive folders with unquie IDs and images files which have a version history.

I need to remove the history and just keep the latest archive version set of files. It seems an append query would be best but just can't think the best way todo it.

Table Example:

Archive: Folder: User: SubFolder: Filename:

01 TA05420010125-01 8769091 WAG 20010125-01.tif
01 TA05420010125-01 8769091 TIM 20010125-02.tif
02 TA05420110324-01 8769091 WAG 20110324-01.tif
02 TA05420110324-01 8769091 TIM 2011032402.tif
02 TA05420110324-01 8769091 PD 20110324-02.tif
03 TA05420150114-01 8769091 VYE 20010125-01.tif
03 TA05420150114-01 8769091 DFU 20010125-02.tif

In the above example I only want to keep user 8769091 latest filename info from archive 3 and exclude the rest. I need todo this for over 3000 users who could have mutiple versions.

Do you have any sugestion todo this?

Luke

 

[richsql]

As a starter for 10... from the sample data , concatenating the user and filename seems to work... you will need to change the select * to DELETE once you happy with the output...

SELECT *
FROM files
WHERE
FileName & User NOT IN
(
SELECT Max(FILENAME) & USER AS OldestFile
FROM Files
GROUP BY USER
);

 

[man199]

As a starter for 10... from the sample data , concatenating the user and filename seems to work... you will need to change the select * to DELETE once you happy with the output...

SELECT *
FROM files
WHERE
FileName & User NOT IN
(
SELECT Max(FILENAME) & USER AS OldestFile
FROM Files
GROUP BY USER
);

Hi Richsql,

thanks for your help, that would only remove some entrys. what I need is to say only keep row entrys for user 8769091 for archive 03 and any filenames for that archive

 

[richsql]

use archive instead of file... ?

SELECT *
FROM files
WHERE
archive & User NOT IN
(
SELECT Max(archive) & USER AS OldestFile
FROM Files
GROUP BY USER
);

 

[man199]

use archive instead of file... ?

SELECT *
FROM files
WHERE
archive & User NOT IN
(
SELECT Max(archive) & USER AS OldestFile
FROM Files
GROUP BY USER
);

Thank you that worked, is there any way to speed this up? im trying to export the data but the processing speed is really slow around 144k an hour. its processing around
4354174 entries to remove history.

 

[richsql]

Possibly have a few suggestions, never sure if its best to let these things just run...

Copy the db locally, create a copy of the table adding a new field [archive & User] deletekey, once created place an index on this field

Run the query above ammending it to use deletekey