Remove old duplicates

[phonic]

Hi, I a table in access. Sometimes when I do a mass upload, I can return some duplicate entries to what was already in the database. I know about append query to remove duplicates using primary keys.

My problem is, this append query will delete the newly uploaded data and keep the old stuff. I want to delete the old data and keep the new via the append query.

If there is another means to doing this, please let me know.

I don't mind overwriting old data with new data in the mass upload, but think this may get a bit too complex.

Please let me know if you need to know anything else to help me.

Thanks in advance!

Phonic

 

[MSAccessRookie]

Hi, I a table in access. Sometimes when I do a mass upload, I can return some duplicate entries to what was already in the database. I know about append query to remove duplicates using primary keys.

My problem is, this append query will delete the newly uploaded data and keep the old stuff. I want to delete the old data and keep the new via the append query.

If there is another means to doing this, please let me know.

I don't mind overwriting old data with new data in the mass upload, but think this may get a bit too complex.

Please let me know if you need to know anything else to help me.

Thanks in advance!

Phonic

It should be possible to do what you want. If you are able to show the SQL that you are using now, we should be able to steer you in the right direction.

 

[phonic]

Here is my sql:

INSERT INTO qry_appendDuplicates
SELECT tbl_MasterFundReview_raw.*
FROM tbl_MasterFundReview_raw;

The tbl_MasterFundReview_raw is the original table.

This is simply the sql from the query, so not sure it will help much???

 

[MSAccessRookie]

Here is my sql:

INSERT INTO qry_appendDuplicates
SELECT tbl_MasterFundReview_raw.*
FROM tbl_MasterFundReview_raw;

The tbl_MasterFundReview_raw is the original table.

This is simply the sql from the query, so not sure it will help much???

You Said:

My problem is, this append query will delete the newly uploaded data and keep the old stuff. I want to delete the old data and keep the new via the append query.

The query above Deletes no existing entries. It only adds new ones. Is there another query to clean up?

 

[phonic]

I found some code on the internet, and its perfect to remove duplicates. however, it removes the most recent entry. I want it to remove the older entry. Been playing with it all day, and still can't seem to find a conclusion. Any ideas?

Option Compare Database
Option Explicit
Sub Remove_Duplicates()
Dim str_Duplicate_column(3) As String
str_Duplicate_column(1) = "Period of Review"
str_Duplicate_column(2) = "Year"
str_Duplicate_column(3) = "Name of Fund"
Call Remove_Duplicates_Entries("tbl_MasterFundReview", str_Duplicate_column)
End Sub
Sub Remove_Duplicates_Entries(pstr_Target_Table As String, pstr_Duplicate_Column() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim str_sql As String
Dim var_Current_Value(3) As Variant
Dim var_Previous_Value(3) As Variant
Set dbs = Access.currentdb
pstr_Target_Table = "[" & pstr_Target_Table & "]"
pstr_Duplicate_Column(1) = "[" & pstr_Duplicate_Column(1) & "]"
pstr_Duplicate_Column(2) = "[" & pstr_Duplicate_Column(2) & "]"
pstr_Duplicate_Column(3) = "[" & pstr_Duplicate_Column(3) & "]"
'Orders recordset so duplicates are sequential
str_sql = _
"SELECT " & _
pstr_Duplicate_Column(1) & "," & _
pstr_Duplicate_Column(2) & "," & _
pstr_Duplicate_Column(3) & " " & _
"FROM " & _
pstr_Target_Table & " " & _
"ORDER BY " & _
pstr_Duplicate_Column(1) & "," & _
pstr_Duplicate_Column(2) & "," & _
pstr_Duplicate_Column(3)
Set rst = dbs.OpenRecordset(str_sql, dbOpenDynaset)
Do While Not rst.EOF
var_Previous_Value(1) = rst(pstr_Duplicate_Column(1))
var_Previous_Value(2) = rst(pstr_Duplicate_Column(2))
var_Previous_Value(3) = rst(pstr_Duplicate_Column(3))
rst.MoveNext
Delete_Another_Duplicate_Maybe:
If rst.EOF Then Exit Do
var_Current_Value(1) = rst(pstr_Duplicate_Column(1))
var_Current_Value(2) = rst(pstr_Duplicate_Column(2))
var_Current_Value(3) = rst(pstr_Duplicate_Column(3))
If _
var_Previous_Value(1) = var_Current_Value(1) _
And _
var_Previous_Value(2) = var_Current_Value(2) _
And _
var_Previous_Value(3) = var_Current_Value(3) _
Then
rst.Delete
rst.MoveNext
If Not rst.EOF Then
var_Current_Value(1) = rst(pstr_Duplicate_Column(1))
var_Current_Value(2) = rst(pstr_Duplicate_Column(2))
var_Current_Value(3) = rst(pstr_Duplicate_Column(3))
'var_Previous_Value will maintain original value
GoTo Delete_Another_Duplicate_Maybe:
End If
End If
Loop
End Sub

 

[geno]

Hi,

I'm trying to use this code to remove some duplicate files.
Some of the code gets highlighted in red:

If _
var_Previous_Value(1) = var_Current_Value(1) _
And _
var_Previous_Value(2) = var_Current_Value(2) _
And _
var_Previous_Value(3) = var_Current_Value(3) _
Thenrst.Delete

Looks like the If statement is not complete.
Does any one know what should go there?

Thanks