Duplicate query query!!!

[npjonze]

Hi all, long time no post. Good to see you all still fighting the good fight!

On to business... I use A97

Got a query which finds duplicate records in a table ( duplicates defined as having same Postcode and Surname as another record) for a given Filename ( each record is assigned a filename value on importing from a CSV file named Filename )

Problem is, the query brings back extra records which arent duplicates for the given Filename ! ( The Filename is chosen by user from a cbo box). The table I am querying could contain records with the same Postcode and Surname as the recordset I am interrogating but these records will have a different Filename . Is this the problem?

Here is the SQL:

( I insert the duplicates from tblMain into tblDuplicate)

strSQL = "INSERT INTO tblDuplicate SELECT DISTINCTROW tblmain.* FROM " & _
"tbMain WHERE Filename = '" & Me.cboFilename & "' AND ((tblMain.Surname) In (SELECT [Surname] FROM " & _
"[tblMain] As Tmp GROUP BY [Surname] HAVING Count(*)>1 )) " & _
"AND ((tblMain.Postcode) In (SELECT [Postcode] " & _
"FROM [tblMain] As Tmp GROUP BY [Postcode] HAVING Count(*)>1)) "

It does find the dupes but it also brings back other records which have no duplicate for the given Filename. Help???!!!

Many thanks

npjonze

 

[pdx_man]

You are not getting the instances where both of them occur in the same record.

ie looking for Blue & Dog
1 Blue, Horse
2 Green, Cat
3 Red, Snake
4 Blue, Dog
5 White, Dog

You query would return rows 1, 4, 5 while you are only looking for row 4. Give this a try:

strSQL = "INSERT INTO tblDuplicate SELECT DISTINCTROW tblmain.* FROM " & _
"tbMain WHERE Filename = '" & Me.cboFilename & "' AND ((tblMain.Surname & tblMain.Postcode) In (SELECT [Surname] & [tblMain] FROM " & _
"[tblMain] As Tmp GROUP BY [Surname], [tblMain] HAVING Count(*)>1 )) "

 

[npjonze]

Too few parameters error

pdx man.

Thanx for the post. Seem to be getting a TOO FEW PARAMETERS error tho'.

Cant see why??

 

[pdx_man]

I tried this out and it worked (I had some typos before and changed the DISTINCT statement) :

strSQL = "INSERT INTO tblDuplicate SELECT DISTINCT tbmain.* FROM " & _
"tbMain WHERE Filename = '" & Me.cboFilename & "' AND ((tbMain.Surname & tbMain.Postcode) In (SELECT [Surname] & [Postcode] FROM " & _
"[tbMain] As Tmp GROUP BY [Surname], [Postcode] HAVING Count(*)>1 )) "

Also, be sure that TblDuplicate is already existing in your DB. This is an Append query, not a Make-Table.