Retaining the ONE most current dupe...??

[Guest]

Anybody know how I can delete multiple duplicate records (except one field is
unique- the time stamp) and only retain the most current one based on that
time stamp cell? Example:

ITEM | QTY | DESC | DATE | LOT
101 5 xxx 09/30/04 k5
101 5 xxx 09/29/04 k5
101 5 xxx 09/24/04 k5
101 5 xxx 09/12/04 k5

I only want to keep the most current record (I already sorted by that
column) and delete the rest. I could do this in Access or SQL if I knew the
specific logic to make it work. Any help would be greatly appreciated!!

~ Ralph

 

[Guest]

Sorry for the dupe postings!! Connectivity issues on this end it appears...

 

[Jamie Collins]

Anybody know how I can delete multiple duplicate records (except one field is
unique- the time stamp) and only retain the most current one based on that
time stamp cell? Example:

ITEM | QTY | DESC | DATE | LOT
101 5 xxx 09/30/04 k5
101 5 xxx 09/29/04 k5
101 5 xxx 09/24/04 k5
101 5 xxx 09/12/04 k5

I only want to keep the most current record (I already sorted by that
column) and delete the rest. I could do this in Access or SQL if I knew the
specific logic to make it work.

Watch those reserved words, DATE and DESC (reserved in ODBC, MS
Jet/Access, SQL Server present and future, etc) <g>.

You don't say which columns you are using for a key to determine a
duplicate, so I'll assume all:

SELECT DISTINCT
MAX([Date]) AS max_date,
ITEM, QTY,
[DESC], LOT
FROM
[Sheet1$]
GROUP BY
ITEM, QTY,
[DESC], LOT
;

If fewer columns comprise your key, simply omit them from the above.

Jamie.

--