Matching Records

[GriffyNJ]

Greetings all:

So I have a project which requires the help of you wonderful people. What I'm am trying to accomplish is this:

I have a query with information on Date of Birth and Full Name (format lastname, firstname). I have the wonderful task of going through this query and deleting records where date of birth is a dead on match AND the full name is NOT. In the end i'm am trying to end up with only records where DOB is a dead on match AND the full name is similiar (one record has a middle initial while the other doesn't, first name is the same but last isn't due to maiden name change, etc). With these record I then must investigate them further via another database, or another department does 'field work'.

What I want to accomplish to make this task easier, being as I have 50,000 records to look through, is a query that somehow looks at records and sees and that the DOB matches up, and then amongst this population looks at LASTNAME, FIRSTNAME and sees that nothing matches within the string be it the lastname doesn't match nor the firstname.

Any suggestions?

 

[GriffyNJ]

Just to clarify,
are you are looking for a query that will delete all records where all the following tests are all true:
date equals X
surname does not equal Y
firstname does not equal Z

Yes, this is correct. Of course before I run it I'd look at the results. But essentially I want to get rid of records meeting this criteria

 

[spaddhu]

Sorry, but I couldn't get a clear picture of what is your requirement.
As per my understanding, you have table where certain records need to be deleted.
The criteria is DOB = X, where you enter the date X everytime you run the query.
Another criteria is Name = Y, where again you enter Y and you want the records with that 'exact name' to be deleted.
Please correct if my understanding is correct, so that I can help you.

 

[GriffyNJ]

Sorry, but I couldn't get a clear picture of what is your requirement.
As per my understanding, you have table where certain records need to be deleted.
The criteria is DOB = X, where you enter the date X everytime you run the query.
Another criteria is Name = Y, where again you enter Y and you want the records with that 'exact name' to be deleted.
Please correct if my understanding is correct, so that I can help you.

I have a table with peoples information in it. It contains their DOB, and their full name (lastname, firstname). What I need as a final result is a table where record 1 and record 2 have the same DOB and similiar full names (maybe the last name is different but the first name is the same). In order to get to this, it's best for me to work the opposite way which is delete records where record 1 and record 2 have the same DOB BUT their names don't match any way whatsoever. That way once they are all deleted, i'm left with what I originally stated.

I'm thinking some kind of a string function might work. maybe some kind of nesting as well. Just need some guidance on what you all might think. In the end, what i'm left with (records where record 1 and record 2 have the same dob and similiar names) will be a list that I can investigate further tthrough other means, whether or not they are truly the same person. So i'm trying to eliminate those that I already know from eyeballing that they are indeed not the same person because their names are so unrelated

 

[GriffyNJ]

maybe I can get this done by somehow writing a query that says selects info where record 1 and record 2 have the same dob and the first 3 letters of last name match for both records. how would I do that? you think an if statement would work?

 

[spaddhu]

maybe I can get this done by somehow writing a query that says selects info where record 1 and record 2 have the same dob and the first 3 letters of last name match for both records. how would I do that? you think an if statement would work?

This puts your question in a more precise way. And your other explanation too was better put than your original.
Here is your query...

SELECT tempQry1.DOB, (tempQry1.FName & ", " & tempQry1.LName) as Name1, (tempQry2.FName & ", " & tempQry2.LName) as Name2
FROM myQry as tempQry1 INNER JOIN myQry as tempQry2
ON tempQry1.DOB = tempQry2.DOB and
Mid(tempQry1.LName,1,3)=Mid(tempQry2.LName,1,3)

 

[GriffyNJ]

This puts your question in a more precise way. And your other explanation too was better put than your original.
Here is your query...

SELECT tempQry1.DOB, (tempQry1.FName & ", " & tempQry1.LName) as Name1, (tempQry2.FName & ", " & tempQry2.LName) as Name2
FROM myQry as tempQry1 INNER JOIN myQry as tempQry2
ON tempQry1.DOB = tempQry2.DOB and
Mid(tempQry1.LName,1,3)=Mid(tempQry2.LName,1,3)

Ok here is my code below. Now this code is basically grouping DOB together that has a count greater than one, which allows me to evaluate the other information and determine whether or not I need to keep it. What I want to add to the below SQL code is something that says if the first 3 letters of lastname_name_local1 are NOT the same, then show this to me.

So in essence just for clarity sake, the SQL already says give me these fields where DOB count is greater than 1, and we want to add AND where lastname_name_local1 does NOT equal on their first 3 letters

SELECT PERSON.Dob, PERSON.Person_id, PERSON.Stateno, PERSON.Dob2, PERSON_NAME.Lastname_name_local1 INTO [PERSON-DOB]
FROM PERSON INNER JOIN PERSON_NAME ON PERSON.Person_id = PERSON_NAME.Person_id
WHERE (((PERSON.Dob) In (SELECT [Dob] FROM [PERSON] As Tmp GROUP BY [Dob] HAVING Count(*)>1 )))
ORDER BY PERSON.Dob;

 

[GriffyNJ]

or a delete query?

 

[spaddhu]

...
WHERE (((PERSON.Dob) In (SELECT [Dob] FROM [PERSON] As Tmp GROUP BY [Dob] HAVING Count(*)>1 )))
AND (((PERSON_NAME.Lastname_name_local1) In (SELECT mid([Lastname_name_local1],1,3) FROM [PERSON_NAME] As TMP1 GROUP BY Mid([Lastname_name_local1],1,3) HAVING Count(*)>1)))
ORDER BY PERSON.Dob;

 

[GriffyNJ]

...
WHERE (((PERSON.Dob) In (SELECT [Dob] FROM [PERSON] As Tmp GROUP BY [Dob] HAVING Count(*)>1 )))
AND (((PERSON_NAME.Lastname_name_local1) In (SELECT mid([Lastname_name_local1],1,3) FROM [PERSON_NAME] As TMP1 GROUP BY Mid([Lastname_name_local1],1,3) HAVING Count(*)>1)))
ORDER BY PERSON.Dob;

It's counting the records that have 3 letters in it. What i'm trying to accomplish is finding records where there are 2 indentical dob's and between let's say record 1 and 2, the first 3 letters match therefore leaving me with something like:

smith, james 09/09/09
smithzed, james 09/09/09


thanks for your patience and help. truly appreciate it

 

[spaddhu]

I'm sorry, I oversaw that query.. Here is the one that should work...

...
WHERE (((PERSON.Dob) In (SELECT [Dob] FROM [PERSON] As Tmp GROUP BY [Dob] HAVING Count(*)>1 )))
AND ((mid(PERSON_NAME.Lastname_name_local1,1,3) In (SELECT mid([Lastname_name_local1],1,3) FROM [PERSON_NAME] As TMP1 GROUP BY Mid([Lastname_name_local1],1,3) HAVING Count(*)>1)))
ORDER BY PERSON.Dob;