Filtering an append query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to add an excel spreadsheet each week to my existing database. I want
to make sure that none of the records I import each week are duplicates. My
problem is that I only know if it is a duplicate by looking at a combination
of 3 fields. (I do not have 1 primary key and cannot get one because the
spreadsheets are being generated by someone else). Can I set a filter on the
append query that won't allow a new record to be imported based on a
combination of 3 different fields? if not, what can I do to import these
spreadsheets each week without adding duplicates? thanks!
 
Hi Diane,

You can create a multi-field index that uses all three fields, with the
unique property set to Yes. This way, no duplicates will be allowed. Then,
run your append query using VBA code *without* including the optional
dbFailOnError parameter. Add a command button to run your append query. The
code behind your command button will look something like this:

Private Sub cmdAddRecords_Click()
On Error GoTo ProcError

CurrentDb.Execute "NameOfAppendQuery"
MsgBox "Records Added.", vbInformation, "My App."

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdAddRecords_Click event procedure..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
One approach:

- Import the data into a empty "shell" temp table that has the same field
layout as the table you will be appending to.

- Pre-existing: a saved query between the temp & real tables that will
return dupes: "qryCheckForDupes" (just do a standard join on all 3 fields,
all 3 would have to match in both tables for a record to be returned)

- Run a saved query that Deletes any records in the temp table that match
those in qryCheckForDupes (again, join all 3 fields) Once run,
qryCheckForDupes will return no records.

- Run a saved query that Appends the remaining data from Temp table to Real
data.

- Run a saved query that Deletes all records in your temp table (so the
"shell" is ready for next import. This could wait & be done just before the
import next time, but that means you'd be storing all that redundant data in
the meantime)

Once this is all set up, it's pretty simple. You could even number the
queries so that they show in the database window in order. Or you could run
the queries in sequence with the click of a button via code.

HTH,
 
Back
Top