Deleting all but oldest record per ID

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), there is a small access
database called "RemoveDuplicates.mdb" which should do what you want.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[rgrantz]

I am using ODBC to populate an Access table. The linked tables are in a
separate ERP software table set.

The Linked table in question tracks the various phases of an order and who
made the transaction (transaction being an event in the status/location of
an order). For example, when a person enters an order, their ID and a time
stamp is added to the transaction. Then, a separate person scans the order
into the manufacturing plant, then a person scans it as complete, etc.

Example:

OrderID Status Employee
Time
1234 OrderEntered Tom 020305
12:34:44
1234 In Mfg. Dan
020305 13:24:55
1234 Completed Steve
020505 07:31:22

However, it sometimes happens that an order is scanned back to a previous
process in order for a change to be made (the software won't allow certain
changes to be made unless an order is scanned into the right location or
process). So the ERP table may have:

OrderID Status Employee
Time
1234 OrderEntered Tom 020305
12:34:44
1234 In Mfg. Dan
020305 13:24:55
1234 Modified Tony
020405 01:33:13
1234 In Mfg. Tony
020405 01:44:35
1234 Completed Steve
020505 07:31:22

So, for any OrderID, an order might be scanned to the same location more
than once. What I want to do is update the Access table, but then also
delete DUPLICATE scanpoints/status entries and retain the FIRST one. So,
when the ERP table has:

OrderID Status Employee
Time
1234 OrderEntered Tom 020305
12:34:44
1234 In Mfg. Dan
020305 13:24:55
1234 Modified Tony
020405 01:33:13
1234 In Mfg. Tony
020405 01:44:35
1234 Completed Steve
020505 07:31:22

My Access table would have:

OrderID Status Employee
Time
1234 OrderEntered Tom 020305
12:34:44
1234 In Mfg. Dan
020305 13:24:55
1234 Modified Tony
020405 01:33:13
1234 Completed Steve
020505 07:31:22

(keeping the FIRST instance of Status="In Mfg" but deleting the subsequent
ones.

How would I set up a query to do this? It's basically saying "For every
Status Entry, if there is more than one of the same status in the same
OrderID, keep the earliest TimeStamp one and delete the others"

Thanks for any help, it is appreciated.

 

[John Vinson]

How would I set up a query to do this? It's basically saying "For every
Status Entry, if there is more than one of the same status in the same
OrderID, keep the earliest TimeStamp one and delete the others"

I'd use an Append query with a criterion on TimeStamp which selects
the earliest value for that OrderID and Status: i.e. on the criteria
line of the grid put

=(SELECT Min([Timestamp]) FROM yourtable AS X WHERE X.OrderID =
Yourtable.OrderID AND X.Status = yourtable.Status)


John W. Vinson[MVP]

 

[Roger Carlson]

This is exactly what the sample does. It does not remove ALL the
duplicates, I keeps the first one(or last one, depending on which query you
choose). Try it out on a copy of your table.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[rgrantz]

Roger:

Thanks for the link, there's lots of great stuff on your site! However, I
not only need to delete duplicates, I need to keep the earliest entry of the
duplicate. If there are 4 duplicate status data for one particular UnitID,
I need to keep the one with the earliest tim stamp, and delete the others.