Find Duplicate records with any part of field

[rickyzicky]

I would like to find duplicate records matching 2 fields exactly and "any part of field" on the 3rd field.
An example:

In a 30000 record databse I would like to output duplicate addresses [house number] and [street name] where the last names partially match.

123 Main St - Joe Smith
123 Main St - The Smith Family

666 Damien St - Lucifer Jones
666 Damien St - The Jones Residence.

Thanks in advance.

RZ

 

[PHV]

I'm afraid you have to write your own matching function, playing with the Split and the InStr functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are rickyzicky . . . .

You can also make a Select Query setting your criteria appropriately . . .

See Ya! . . . . . .

 

[rickyzicky]

Can you suggest the sql to perform this function remembering that 2 of the fields should match and 1 field should be a partial match?

Thnanks

 

[TheAceMan1]

rickyzicky . . . . .

If the fields you quoted will be referenced form a form, copy'paste the following into the SQL View of a new query. [blue]You![/blue] substitute proper names in [purple]purple[/purple]:

[blue]SELECT [house number], [street name], [lastname]
FROM [[purple][b]TableName[/b][/purple]]
WHERE (([house number]=Forms![purple][b]FormName[/b][/purple]![house number]) AND ([street name]=Forms![purple][b]FormName[/b][/purple]![street name]) AND ([lastname] Like "*" & Forms![purple][b]FormName![purple][b]txtLastNameFilter[/b][/purple] & "*"));[/blue]

See Ya! . . . . . .

 

[PHV]

In a standard code module create your own match function like this:

Public Function myMatch(ByVal str1, ByVal str2) As Boolean
myMatch = False
If Trim(str1 & "") = "" Or Trim(str2 & "") = "" Then Exit Function
Dim w
For Each w In Split(str1)
  If Len(w) > 3 And InStr(1, " " & str2 & " ", " " & w & " ", vbTextCompare) Then
      myMatch = True
      Exit Function
  End If
Next
End Function

And now your query:
SELECT A.[house number], A.[street name], A.[last name]
FROM yourTable AS A INNER JOIN yourTable AS B
ON A.[house number] = B.[house number] AND A.[street name] = B.[street name]
WHERE myMatch(A.[last name],B.[last name])=True;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886