mato01
asked on
Remove Duplicates based on Date Added
I have an Access Table named tbl_TestAll that I need to delete duplicates based on the Date Added date.
DtA is the Date Added field
The column names are liks such.
Cha, Allo, Tar, Cons, Opt, Des, Pct, Len, Cur, Com, DtA, For, Mar.
The rows are as such:
Row 1 Ca1, Allo1, Tar2, Cons1, Opt 1, Des1, Pct 3, Len1, Cur2, Com 2, 7/14//2001, For1, Mar1
Rpw 2 Ca1, Allo1, Tar3, Cons1, Opt 1, Des2, Pct 4, Len2, Cur3, Com 2, 9/14/2001, For1, Mar1
So basically, Cha, Allo, Cons, Opt, For, MarI are duplicated in both rows.
I need a query that will delete Row 1, and keep Row 2 based on the latest date.
DtA is the Date Added field
The column names are liks such.
Cha, Allo, Tar, Cons, Opt, Des, Pct, Len, Cur, Com, DtA, For, Mar.
The rows are as such:
Row 1 Ca1, Allo1, Tar2, Cons1, Opt 1, Des1, Pct 3, Len1, Cur2, Com 2, 7/14//2001, For1, Mar1
Rpw 2 Ca1, Allo1, Tar3, Cons1, Opt 1, Des2, Pct 4, Len2, Cur3, Com 2, 9/14/2001, For1, Mar1
So basically, Cha, Allo, Cons, Opt, For, MarI are duplicated in both rows.
I need a query that will delete Row 1, and keep Row 2 based on the latest date.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In MS Access, it may be easiest to INSERT the rows you want into a new table, DELETE all rows, then put back the rows you want. You can try once you have done the insert into new table to DELETE where there is no match, so guess that is the key...
So, what I would try is to build a query that gets you the MAX DtA column that corresponds to Cha, Allo, Cons, Opt, For, MarI combinations.
Open in new window
If that looks like what you want, you can then JOIN that back to your original data, you may have to put it in an intermediate query first, but try:
Open in new window
That should get you the records you want. Let me know if you have any questions from there as it is just a straight-forward, INSERT and DELETE operation.
And I can't remember if this works in MS Access, but you can try:
Open in new window
The change is not equal Dta.