Show duplicates where names may have been transposed

[akaRose]

Hello,

I'm try to use a execute a query from a form to show all duplicate patients in my database. I have the basic code to get it to work, however due to the nature of our patients we sometimes get reversal of first and last name, they will get tested multiple times over the course of their treatment and sometimes they give their names differently and I would like to also include this possibility of firstname=surname AND surname=firstname but I'm not sure how to do this.

eg. The first time they come in to be tested they give the name Harvey George, and the next time they may give George Harvey. (To complicate things further we don't always get DOB and Healthcard number)

Here's the code I'm currently using:

SELECT ENTRYTABLE.FirstName, ENTRYTABLE.Surname, ENTRYTABLE.DOB, ENTRYTABLE.HEALTHCARD_No, ENTRYTABLE.RECDATE, ENTRYTABLE.PatientAddress
FROM ENTRYTABLE
WHERE (((ENTRYTABLE.FirstName) In (SELECT [FirstName] FROM [ENTRYTABLE] As Tmp GROUP BY [FirstName],[Surname],[DOB],[HEALTHCARD_No] HAVING Count(*)>1 And [Surname] = [ENTRYTABLE].[Surname] And ( [DOB] = [ENTRYTABLE].[DOB] or [HEALTHCARD_No] = [ENTRYTABLE].[HEALTHCARD_No]))))
ORDER BY ENTRYTABLE.FirstName, ENTRYTABLE.Surname, ENTRYTABLE.DOB, ENTRYTABLE.HEALTHCARD_No,ENTRYTABLE.RECDATE;

 

[CaptainD]

I would think that the Health Card Number would be unique to each person, why not just use the health card number to pull all of their records?

Doing so would even catch the errors in the name fields.

 

[akaRose]

As mentioned in the post we don't always get this information. The patients don't always have one (refugees, immigrants) are homeless and don't provide or have one. Or for some reason this information was not passed along to us. Unfortunately we don't have anything completely unique to the patients which is why I need to use names in combination with DOB or HC to make matches, and I will use vba to generate unique ID #'s.

 

[Remou]

You could try something like this:

SELECT a.Name1 & " " & a.Name2 AS Fullname, Count(a.Name1 & a.Name2) AS CountOfName
FROM (SELECT t.ID, t.Firstname As  Name1, t.Lastname As Name2 
      FROM tblTable AS t
      UNION ALL 
      SELECT t.ID, t.Lastname As Name1, t.Firstname As Name2
      FROM tblTable AS t 
      INNER JOIN tblTable AS t2 ON t.Field2 = t2.Firstname AND t.Firstname = t2.Lastname) 
AS a
GROUP BY [Name1] & " " & [Name2]

This will produce:

[tt]Fullname CountOfName 'Actual records
Anne Brown 1 'No duplicates
George Harvey 4 '2 x George Harvey, 1 x Harvey George
Harvey George 3 ' ditto
Joe Verdi 2 '1 x Joe Verdi, 1 x Verdi Joe
Verdi Joe 2 ' ditto[/tt]

http://lessthandot.com