Problems with select query for duplicate records....

[akaRose]

Hello,

I'm having trouble with a select query. I'll explain what I would like to see and maybe someone can suggest how I get there. I have a database that has duplicate patients, and unfortunately the quality of data is not always great due to the source - nothing I can really do about it. What I'm trying to do is have a form based on a query that will show my the surname, first initial, DOB and Healthcard No for patients that are duplicates (must have the same surname, first initial AND DOB and/or Healthcard No). The problem arrises in that not all records will have DOB and Healthcard No. Some will have one and not the other and some records will have both. So on my form (continuous form) each group will show with the number of duplicates which can be clicked and their full record looked at where I can confirm they are a duplicate and assign a unique ID. The problem here is to open the confirmation form with all duplicates showing since they may have different combinations of healthcard or DOB present.

Eg. Entrytable

FirstName Surname DOB Healthcard_No
Jane Doe 01/01/1970
Jane Doe 123456789
John Doe
Jane Doe 01/01/1970 123456789
Jane Doe

The query and hence the form should show:

FirstInitial Surname DOB Healthcard_No NoDups
J Doe 01/01/1970 123456789 3

Then click on the NoDups 3 and get all the full information(there are many more columns I didn't show here) that would allow for confirmation and assigning a unique ID.

I've been struggling with this for a while, any help is appreciated.

Thanks

 

[BrianLe]

This is very crude but it may help get you started.

First I created a SELECT query to remove the records without a DOB or SSN.

Then use an append query to add records to a new table
tbl1 that have more than 1 DOB for each FirstName and SurName from the first query.

Then use an append query to add records to tbl1 that have more than 1 HealthcardNo for each FirstName and SurName from the first query.

Then use a SELECT DISTRINCTROW query on tbl1 to find the records that have at least one "match".

There is probably an easier way, but this seemed to work.

Let me know if you need more and I'll try to add the code. It got to be too long for this post.

 

[akaRose]

Hi, Thanks for posting. I hadn't checked back recently since no one seemed to be answering. I worked away and came up with a solution - not quite what you suggested. But the problem is its REALLY slow. It uses two queries (both seem to be slow on their own) and then a UNION query to get the final information I need. It appears to be working well it just takes forever. Not sure if there is a way to make it run faster. I would appreciate any suggestions : ) Below is the code I'm currently using

qry020T010DuplicateDOBMatch:

SELECT ENTRYTABLE.Surname, Left([FirstName],2) AS Expr1, ENTRYTABLE.DOB, Max(Left([HEALTHCARD_No],10)) AS Expr2, Count(ENTRYTABLE.TBR_No) AS CountOfTBR_No, Count(ENTRYTABLE.KEY) AS No_Patients
FROM ENTRYTABLE
GROUP BY ENTRYTABLE.Surname, Left([FirstName],2), ENTRYTABLE.DOB
HAVING (((ENTRYTABLE.Surname) In (SELECT [Surname] FROM [Entrytable] As Tmp GROUP BY [Surname],left([FirstName],2),[DOB] HAVING Count(*)>1 And left([FirstName],2) = left([Entrytable].[FirstName],2) And [DOB] = [Entrytable].[DOB])) AND ((Count(ENTRYTABLE.TBR_No))<>Count("No_Patients:Key")))
ORDER BY ENTRYTABLE.Surname, Left([FirstName],2), ENTRYTABLE.DOB;


qry020T010DuplicateHCMatch:

SELECT ENTRYTABLE.Surname, Left([FirstName],2) AS Expr2, Max(ENTRYTABLE.DOB) AS MaxOfDOB, Left([HEALTHCARD_No],10) AS Expr1, Count(ENTRYTABLE.TBR_No) AS CountOfTBR_No, Count(ENTRYTABLE.KEY) AS No_Patients
FROM ENTRYTABLE
GROUP BY ENTRYTABLE.Surname, Left([FirstName],2), Left([HEALTHCARD_No],10)
HAVING (((ENTRYTABLE.Surname) In (SELECT [Surname] FROM [Entrytable] As Tmp GROUP BY [Surname],left([FirstName],2),left([Healthcard_No],10) HAVING Count(*)>1 And left([FirstName],2) = left([Entrytable].[FirstName],2) And left([Healthcard_No],10) = left([Entrytable].[Healthcard_No],10))) AND ((Count(ENTRYTABLE.TBR_No))<>Count("No_Patients:Key")))
ORDER BY ENTRYTABLE.Surname, Left([FirstName],2);


qry020T010DuplicatePatientMatch:

SELECT Surname, Expr1, DOB, Expr2
FROM qry020T010DuplicateDOBMatch
UNION SELECT Surname, Expr2, MaxOfDOB, Expr1
FROM qry020T010DuplicateHCMatch;

 

[BrianLe]

I don't know if this will be any faster, but this is what I used.

Find records with a DOB or HEALTHCARD_No

qryENTRYTABLEwithDOBorHC
 
SELECT ENTRYTABLE.Surname, ENTRYTABLE.FirstName, ENTRYTABLE.DOB, ENTRYTABLE.HEALTHCARD_No, 
WHERE (((ENTRYTABLE.DOB)>1)) OR (((ENTRYTABLE.HEALTHCARD_No)>1));

Find records with DOB match

qry020T010DuplicateDOBMatch
 
INSERT INTO tbl1 (FirstName, Surname,DOB,HEALTHCARD_No)
SELECT DISTINCTROW qryENTRYTABLEwithDOBorHC.FirstName AS FirstName, qryENTRYTABLEwithDOBorHC.Surname AS Surname, qryENTRYTABLEwithDOBorHC.DOB AS DOB,qryENTRYTABLEwithDOBorHC.HEALTHCARD_No AS HEALTHCARD_No
FROM qryENTRYTABLEwithDOBorHC
WHERE ((([qryENTRYTABLEwithDOBorHC].[FirstName]) In (SELECT[FirstName] FROM 
[qryENTRYTABLEwithDOBorHC] As Tmp GROUP BY [FirstName],[Surname],[DOB], HAVING Count(*)>1
And [Surname]=[qryENTRYTABLEwithDOBorHC].[Surname] And [DOB]= [qryENTRYTABLEwithDOBorHC].[DOB])))
ORDER BY qryENTRYTABLEwithDOBorHC.FirstName, qryENTRYTABLEwithDOBorHC.Surname, qryENTRYTABLEwithDOBorHC.DOB;

Find records with HC match

qry020T010DuplicateHCMatch

INSERT INTO tbl1 (FirstName, Surname,DOB,HEALTHCARD_No)
SELECT DISTINCTROW qryENTRYTABLEwithDOBorHC.FirstName AS FirstName, qryENTRYTABLEwithDOBorHC.Surname AS Surname, qryENTRYTABLEwithDOBorHC.DOB AS DOB, qryENTRYTABLEwithDOBorHC.HEALTHCARD_No AS HEALTHCARD_No
FROM qryENTRYTABLEwithDOBorHC
WHERE ((([qryENTRYTABLEwithDOBorHC].[FirstName]) In (SELECT[FirstName] FROM 
[qryENTRYTABLEwithDOBorHC] As Tmp GROUP BY [FirstName],[Surname],[HEALTHCARD_No] HAVING Count(*)>1
And [Surname]=[qryENTRYTABLEwithDOBorHC].[Surname] And [HEALTHCARD_No]= [qryENTRYTABLEwithDOBorHC].[HEALTHCARD_No])))
ORDER BY qryENTRYTABLEwithDOBorHC.FirstName, qryENTRYTABLEwithDOBorHC.Surname, qryENTRYTABLEwithDOBorHC.HEALTHCARD_No;

Find records with either DOB or HC

qry020T010DuplicatePatientMatch:

SELECT DISTINCT tbl1.FirstName, tbl1.Surname, tbl1.DOB, tbl1.HEALTHCARD_No
FROM tbl1;

 

[lespaul]

be sure that you've got indexes on the fields you're using in the where clauses, that should help with the speed issue....so DOB, HealthCard, Name

Leslie

Have you met Hardy Heron?

 

[akaRose]

Thanks for the code, it seems to be running faster then the code I was using.

I'm just not sure how exactly to implement it in the form. Since the Append queries have to be updated first and then the final query to get the newest result and I don't have experience with Append queries so I'm not sure how to work this into my form. What I have been doing is putting my final query as my control source for my continous form.

The help is much appreciate : )

 

[BrianLe]

You could try this.

You will have to create a "delete" query ("tbl1 Delete Record Query")to remove the records from tbl1.

Create a new form. Add command button to run each of your queries.

Add a button to open your form.

If you look at the code for each of the buttons, you will see that they look similar and they can be combined into one command button that will run all the queries and open your form.

Create a button with the code that will be something like the following, and then delete all the other buttons.

Private Sub Command15_Click(1)
On Error GoTo Err_Command15_Click
  Dim stDocName1 As String
  Dim stDocName2 As String
  Dim stDocName3 As String
  Dim stDocName4 As String
  Dim stDocName5 As String
  Dim stLinkCriteria As String

stDocName5 = "tbl1 Delete Record Query"
DoCmd.OpenQuery stDocName5, acNormal, acEdit

stDocName1 = "qryENTRYTABLEwithDOBorHC"
DoCmd.OpenQuery stDocName5, acNormal, acEdit

stDocName2 = "qry020T010DuplicateDOBMatch"
DoCmd.OpenQuery stDocName5, acNormal, acEdit

stDocName3 = "qry020T010DuplicateHCMatch"
DoCmd.OpenQuery stDocName5, acNormal, acEdit

stDocName4 = "yourForm"
DoCmd.OpenForm stDocName4, , , stLinkCriteria

Exit_Command15_Click:
  Exit Sub

Err_Command15_Click:
 MsgBox Err.Description
  Resume Exit_Command15_Click

End Sub

So you end up with two forms. One with a command button to run all the queries and open your form, and your form with the control source of "qry020T010DuplicatePatientMatch".

Hope this helps.