Update duplicate record (vba)

[slenish]

Hello all,

Im looking for some help with duplicate records. I have a table that gets updated on a daily basis but each day the same record could show up on the table creating a duplicate.

On the table i have a load date field so i can tell which record is old and new. What im trying to do is carry over the info from the new record to the old one keeping the original load date and then delete the new duplicated record. I have writen some code that can remove the duplicates with no problem but i am having trouble getting it to edit the old record. I can get it to work with static data but having trouble getting it to look at the table data.

any help would be greatly appreciated.

thanks in advance,

Slen

here is what i have so far

 
Dim myDB As DAO.Database
Dim rst As DAO.Recordset
Dim strFind As String
 
Set myDB = CurrentDb
Set rst = myDB.OpenRecordset("tableName", dbOpenDynaset)
 
With rst
  Do While Not .EOF
    If Not IsNull(![ID]) And ![Status] = "Open" Then  'Check for Duplication
        strFind = DCount("*", "tableName", "[Status] ='" & "Open" & "' AND [ID] = '" & ![ID] & "' And [Status2] = '" & ![Status2] & "' And [LoadDate] <=#" & ![LoadDate] & "#") > 1
       If strFind Then
           .Edit
             'here is where im trying to figure out what to insert
           .Update
      End If
    End If
     .MoveNext
  Loop
End With
 
rst.Close
Set rst = Nothing
Set myDB = Nothing
 
 

[Seth Schrock]

This is totally air code and untested.

Dim myDB As DAO.Database
Dim rst As DAO.Recordset
Dim intRecCount As Integer
Dim strCriteria As String
Dim varBookmark As Variant
Dim strID As String
Dim strStatus As String
Dim dteLoadDate as Date
 
Set myDB = CurrentDb
Set rst = myDB.OpenRecordset("tableName", dbOpenDynaset)
 
With rst
    Do While Not .EOF
        If Not IsNull(![ID]) And ![Status] = "Open" Then  'Check for Duplication
            strCriteria = "[Status] ='" & "Open" & "' AND [ID] = '" & ![ID] & _
            "' And [Status2] = '" & ![Status2] & "' And [LoadDate] <=#" & ![LoadDate] & "#"
 
            intRecCount = DCount("*", "tableName", strCriteria)
            If intRecCount > 1 Then
                varBookmark = .Bookmark
                .FindNext strCriteria
 
                strID = !ID
                strStatus = !Status2
                dteLoadDate = !LoadDate
 
                .Delete
                .Bookmark = varBookmark
               .Edit
                    !ID = strID
                    !Status2 = strStatus
                    !LoadDate = dteLoadDate
               .Update
            End If
        End If
        .MoveNext
    Loop
End With
 
rst.Close
Set rst = Nothing
Set myDB = Nothing

I changed the way your criteria worked in your lines 12 & 13 to make it a little easier if you would need to loop through the copying code more than once (would be needed if if were possible for there to be three or more records that were the same to delete two records after copying them to the "master" record). The main part of my code bookmarks the current record, goes to the duplicate record, gets its values, deletes the record, goes back to the original record and then makes the value changes.

[Rabbit]

What you should do is load the new data into another table. Run a query to update the duplicates. Then run another query to insert the ones not in the main table.

[slenish]

Hi Seth,

You are amazing! Thank you very much for your reply and help! That worked great!

Now i just need to adjust this to delete the original. I saw where you had the line rstShippers.Delete but i removed it because i was getting an error due to it not being declared. Is there a way to create two bookmarks one for old and new then delete the old or delcare the old as OldValue then remove it?


Thanks again!!

[zmbd]

slenish/Seth:

WOW talk about a Sledge Hammer to put in carpet tack.

The easier method is as Rabbit pointed out.

[Seth Schrock]

To fix my code, remove the rstShippers so that just .Delete is left. I will fix this in my previous post for future reference.

However, Rabbits idea would probably be easier.

[slenish]

Hi Seth,

That adjustment did the trick. I have noticed an issue due to the .FindNext. Lets say i have two records if the old date is second and the new date is first in the record order than it works but if its the other way around it does not.I also tried .FindFirst ect and i get the same issue.

I tried Rabbits idea but was not having any luck. If either of you could post an example i would apprecaite it.

thanks again,
Slen

[Seth Schrock]

To solve the order problem, create a query (either a querydef or in VBA) that sorts the data by LoadDate. That will make sure that all the dates will come in the correct order.

I would help you with Rabbit's idea, but after studying it, the only thing that I can come up with is even more complicated than my solution, so I don't think that I would be of much help there.

[zmbd]

Rabbit's method (with my tweak):
For duplicates in the final data table, [Tbl_final]:
Removing duplicates (exact duplicated information), frankly, the access wizard does a fairly good job at finding them…. Use it. Once the duplicates with the [Tbl_final] are found you can decide which to delete. Hopefully there is a primary key that will help you to decide which has to go.

To get the new information:

1. Import your information into a temporary table, [tbl_temp]
   1. The table should have fields with the same data types as the final table. The names do not matter (so long as they are not reserved words); however, I would use something slightly different from the final table.
   2. The temporary table and the final table MUST have a field in common, the Primary Key, in that, the record with a given primary key value in one table will have the same or updated information in the other. (i.e. [tbl_temp]![pk]=1; [tbl_temp]![tmpphone]=”123-555-1212” ::: [tbl_final]![pk]=1; [tbl_final]![home_phone]=”123-555-0000)”
2. Verify that the information is correct in the temporary table.
3. Create a query.
   1. Show both the temporary table and the final table
   2. Drag a relationship between the temporary table and the final table – ON THE RELATED primary key: (i.e. [tbl_temp]![pk] ::: [tbl_temp]![pk])”
   3. Edit the relationship so as that all results from the temporary table show and matching from the final table
   4. Drag the fields from the temp/final tables down to the design grid.
4. Save this as a select query first
5. Run this query, make sure that the query returns all of the correct information…
   1. If not, determine why
   2. If true, the save a copy of this query as “qry_updatefinaltablename” or whatever you like
6. Open “qry_updatefinaltablename” in design view.
   1. In the ribbon, select “UpDate”
   2. in the grid, remove all of the [tbl_temp] fields, retain only the [tbl_final] fields
   3. Do NOT include the primary key in the design grid from either table… doing this will allow the update to add new records to [tbl_final]; however, if you have this field from either table, the update query may either choke, or fail to append new records.
   4. in the “update to row” in the design grid, select the fields from the [tbl_temp] that match the fields in [tbl_final] that you need to update the values in.
7. SAVE
8. Click that funky HUGE RED exclamation point – you will get a warning about how many records will be updated etc…

Say… Z: what about those records in [tbl_final] that are not in [tbl_temp]?
Once again, the access wizard doesn’t do a half bad job at finding unmatched records between tables… just run it and have it look for records in [tbl_final] without a match in [tbl_temp]. Now you need to decide it those records in [tbl_final] either didn’t have an update to their information or they should be removed from the data set because they are of no value. Only the end user’s business model for how the updated record set is being pushed out will provide that answer.

This should be an insight article with as often as I’ve given this out…

[Rabbit]

Note that this is only to set it up initially. The steps for future runs is import and run the query.

@Z, if you do an update with an outer join, does it insert records that don't match? If not, they will need to create one more query to insert records that don't exist.

[zmbd]

It has been my experience that the unmatched will be added.

[slenish]

Hi Seth / zmbd / Rabbit

Thank you for all of your help. I ended up going with both options. I found benefits for using both in different ways. Is there a way i can select both answers to be the best answer?

Thank you again and sorry it took me a little while to respond back.

Slen

[Seth Schrock]

Unfortunately only one answer may be selected, but just select the one that best matches your original question (even if you happened to change the way you did things) for future viewers to get the most benefit from it. The thanks is all we need :)