Check for duplicates before running append query

[Ihussein]

Hello,
I have qryDistReg which it filters the data from tblDistReg, then I'm appending the filtered data to tblDistRegData. As am appending the data one monthly basis, I want to check the data of tblDistRegData before running the append query/code. I'm using the following code to check for the duplicates.
If (DMax("RC_IDFK", "qryDistRegAscFilter") >= _
DMax("RC_IDFK", "tblDistRegData")) Then
msgbox "Duplicate"
Exit Sub
End If
The question is how to to check for duplicates using more than one field. in my case I would like to you RC_IDFK field and RegisterID both are numeric fields.
hope it clarifies.
Thanks

 

[sneuberg]

In the CREATE Tab, click Query Wizard and then Find Duplicates Query Wizard. This will probably give you what you want.

 

[Ihussein]

Thanks for your prompt reply, query wizard of duplicates will check duplicates of one field in the table.It does not give the option of comparing multiple table and fields.
Regards

 

[sneuberg]

Sorry, wasn't reading your question well enough.

In your case you could just make a query with the qryDistReg and tblDistRegData joined on the fields in question. Save that query, let's say qyrDups and then to determine if there are duplicates your code would be

If DCount("*", "qyrDups") > 0 Then
   msgbox "Duplicates"
End If

 

[sneuberg]

On the other hand if you want to avoid appending duplicates then what you want is an unmatched query that wouldn't append the records in qryDistReg that are not in tblDistRegData. The query wizard has unmatched queries but you are probably better off creating your own. http://datagnostics.com/dtips/findunmatched.html explains how to do this.

 

[Ihussein]

All right
Thanks man, it looks similar to the code I'm looking for.
Regards