2

I have a table tblCost which contains: 

RecordID    ProjectID    Cost    AllocationMonthYear    TimeStamp    
112            1         $1000       10/17              10/16/2017 12:09:53 PM 
113            1         $1200       11/17              10/16/2017 01:14:45 PM 
114            1         $1000       10/17              10/16/2017 02:21:23 PM

AllocationMonthYear is the month for which the budget is allocated. My problem is that the software is located on a shared drive and used by multiple users. They often add duplicate record for the same month. I need a query to delete the duplicate record based on the latest timestamp, i.e, record 114 ought to be deleted because it is a duplicate of 112 and is LATER than 112.
Any pointers highly appreciated

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Anup
  • 107
  • 3
  • 11

1 Answers1

5
DELETE FROM tblCost AS C1
WHERE EXISTS (SELECT 1
              FROM tblCost AS C2
              WHERE C1.AllocationMonthYear = C2.AllocationMonthYear    
                AND C1.ProjectID   = C2.ProjectID    
                AND C1.TimeStamp   > C2.TimeStamp)

Also you could add one unique index to avoid this continue happening

Unique constraint on multiple fields in Access 2003

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • OP: this is called a "self join." – MoondogsMaDawg Oct 17 '17 at 18:51
  • Wow that worked. Thanks Juan. I wasted a day trying to move it into a temporary table, creating a join between the temp table , original table, deleting from temp table and then replacing again in the original table. – Anup Oct 17 '17 at 19:48