Handling Duplicates and Changing Values in VBA

[pjgoodison]

I’ve been working with Access and tinkering with VBA for a while but I’ve been given a task that has got me beat. I can’t even thin where to start. I need to take an existing list of product links find duplicates in one column and then change the value in those fields. It needs to be done via VBA as part of another scheduled task. If anyone can suggest a solution I would be most grateful.
For example the original file may look like.

INDEX,LOC,OLD CODE,NEW CODE,CREATED,REASON,END DATE
1,_CORE,4038526299321,3188649815838,18/03/2012,2,29/12/2050
2,_CORE,5452000353863,5452000391568,12/03/2012,2,30/12/2050
3,_CORE,5452000353917,5452000391636,06/03/2012,2,30/12/2050
4,_CORE,5452001072343,5452000655592,03/03/2012,2,30/12/2050
5,_CORE,5452000353795,5452000635921,18/03/2012,2,30/12/2050
6,_CORE,5452000862013,5452000655592,12/03/2012,2,30/12/2050
7,_CORE,4038526320391,3188649811878,06/03/2012,2,30/12/2050


8,_CORE,5452000937797,5452000655639,03/03/2012,2,30/12/2050
9,M058,5452000635815,5452000625632,18/03/2012,2,30/12/2050
10,M058,5452000625731,5452000360625,12/03/2012,2,30/12/2050
11,M058,5452000759252,5452000655622,06/03/2012,2,30/12/2050
12,M058,4038526323392,5452000654106,03/03/2012,2,30/12/2050
13,M058,5452000652485,5452000655622,18/03/2012,2,30/12/2050
14,M058,5452001085237,5452000655622,12/03/2012,2,30/12/2050
15,M058,5452001079670,5452001081222,06/03/2012,2,30/12/2050
16,M058,4038526252623,3188649811687,03/03/2012,2,30/12/2050
17,M044,5452000759245,5452000655615,18/03/2012,2,30/12/2050
18,M044,4038526234261,3188649818235,12/03/2012,2,30/12/2050
19,M044,5452000349897,5452000367211,06/03/2012,2,30/12/2050
20,M044,5452000353887,5452000391575,03/03/2012,2,30/12/2050
21,M044,5452001072497,5452000654564,18/03/2012,2,30/12/2050
22,M044,5452001072077,5452000654168,12/03/2012,2,30/12/2050
23,M044,3188649813858,5452000751829,06/03/2012,2,30/12/2050
24,M044,3188649818235,5452000433367,03/03/2012,2,30/12/2050
25,M044,5452000353924,5452000391629,18/03/2012,2,30/12/2050


I need to find duplicates in the OLD CODE field for each LOC then make changes to the results based on the CREATED field. The most recent CREATED date is to be marked as Parent and remain as is and the others are to be altered.

For example a list of duplicates may look like:

19	_CORE	5452001072077	5452000367211	06/03/2012	2	30/12/2050
20	_CORE	5452001072077	5452000391575	03/03/2012	2	30/12/2050
21	_CORE	5452001072077	5452000654564	18/03/2012	2	30/12/2050
22	_CORE	5452001072077	5452000654168	12/03/2012	2	30/12/2050
19	M044	5452001072077	5452000367211	06/03/2012	2	30/12/2050
20	M044	5452001072077	5452000391575	03/03/2012	2	30/12/2050
21	M044	5452001072077	5452000654564	18/03/2012	2	30/12/2050
22	M044	5452001072077	5452000654168	12/03/2012	2	30/12/2050

Then I need to rearrange this data so that it is changed in the original table to look like this:
Essentially for all but the most recently dated record the NEW CODE field becomes the same as the OLD CODE field on the newest entry.

19	_CORE	5452000367211	5452001072077	06/03/2012	2	30/12/2050
20	_CORE	5452000391575	5452001072077	03/03/2012	2	30/12/2050
21	_CORE	5452001072077	5452000654564	18/03/2012	2	30/12/2050
22	_CORE	5452000654168	5452001072077	12/03/2012	2	30/12/2050
19	M044	5452000367211	5452001072077	06/03/2012	2	30/12/2050
20	M044	5452000391575	5452001072077	03/03/2012	2	30/12/2050
21	M044	5452001072077	5452000654564	18/03/2012	2	30/12/2050
22	M044	5452000654168	5452001072077	12/03/2012	2	30/12/2050

[June7]

You are showing dates in international format. This might be an issue - review http://allenbrowne.com/ser-36.htm

Think will be very complicated.

Will each LOC have only one duplicated OLD CODE value? Will the date not be repeated?

[pjgoodison]

You are showing dates in international format. This might be an issue - review http://allenbrowne.com/ser-36.htm

Think will be very complicated.

Will each LOC have only one duplicated OLD CODE value? Will the date not be repeated?

Thanks for the reply. There could be many different duplicated record in the OLD CODE field for each LOC. Also the date field may duplicated. It does seem to be a complicated problem. I'm really stuck.

[June7]

So, if there are multiples of the latest date for each LOC, the OLD CODE could be different for each.

I have an idea but might take me a day or two before I have a solution or conclude it's beyond my ability.

[Missinglinq]

Fire up Access Help and enter the term

find duplicate Wizard

then click on

Find, hide or eliminate duplicate data

and Help will provide you with some approaches.

Linq ;0)>

[June7]

Finding the duplicate records should be easy. It's the data switcheroo poster is wanting that twists my brain.

See if this gets you started.

Create the Find Duplicates query, like:
SELECT Table1.LOC, Table1.[OLD CODE], Table1.[NEW CODE], Table1.ID, Table1.INDEX, Table1.CREATED FROM Table1
WHERE (((Table1.LOC) In (SELECT [LOC] FROM [Table1] As Tmp GROUP BY [LOC],[OLD CODE] HAVING Count(*)>1 And [OLD CODE] = [Table1].[OLD CODE])))
ORDER BY Table1.LOC, Table1.[OLD CODE];

Then code in a general module:

Sub FixData()
Dim rs As ADODB.Recordset, rsEdit As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rsEdit = New ADODB.Recordset
Dim intID As Integer, strOC As String
rs.Open "SELECT Loc, [Old Code], Max(Created) AS MaxCreated FROM [Find duplicates for Table1] GROUP BY Loc, [Old Code];", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
While Not rs.EOF
    intID = DLookup("ID", "[Find duplicates for Table1]", "LOC='" & rs!LOC & "' AND [Old Code]='" & rs![Old Code] & "' AND Created=#" & rs!MaxCreated & "#")
    strOC = rs![Old Code]
    rsEdit.Open "SELECT * FROM Table1 WHERE LOC='" & rs!LOC & "' AND [Old Code]='" & rs![Old Code] & "' ;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    While Not rsEdit.EOF
        If rsEdit!ID <> intID Then
            rsEdit![Old Code] = rsEdit![New Code]
            rsEdit![New Code] = strOC
        End If
        rsEdit.MoveNext
    Wend
    rsEdit.Close
    rs.MoveNext
Wend
End Sub

I made assumption that the OldCode and NewCode fields are text type. Requires a unique ID field in table, autonumber will serve. Also, be sure to select Microsoft ActiveX Data Objects 6.1 Library in VBA Tools.