Retaining the ONE most current dupe...??

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
R Haney mcse said:
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.
Click to expand...

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.

--
 
Back
Top