Help finding duplicate of reversed fields

[luke999]

Hi

This is my first post and i searched to try and find an answer on the forums but can't seem to find one so thought i'd ask to see if anyone is able to help. Sorry if this has been answered before and i've been a bit thick not been able to find it. anyway here's my question...

I have 2 fields i want to check in my database DOB1 and DOB2 (date of birth of applicant 1 and date of birth of applicant 2.

i've written a query that looks for any records where the data in DOB1 and DOB2 match the data in another record for DOB1 and DOB2. i hope that makes sense. this is what i've written.

In (SELECT [App1 DOB] FROM [Data] As Tmp GROUP BY [App1 DOB],[App2 DOB] HAVING Count(*)>1 And [App2 DOB] = [Data].[App2 DOB])

this seems to work but please feel free to correct me.

Now the part i'm struggling with is trying to find duplicates where the fields have been reversed. eg App1 DOB and App2 DOB are the same as App1 DOB and App2 DOB in another record but applicant 1's Date of birth has been stored as Applicant 2 and Applicant 2 is now stored as Applicant 1. i hope thats not to confusing.

Basically i'm trying to check for duplicate mortgage applications in the database but sometimes applicant 1 and applicant 2 are reversed.

Any help would be greatly appreciated.

Thanks

 

[jdraw]

Hi

This is my first post and i searched to try and find an answer on the forums but can't seem to find one so thought i'd ask to see if anyone is able to help. Sorry if this has been answered before and i've been a bit thick not been able to find it. anyway here's my question...

I have 2 fields i want to check in my database DOB1 and DOB2 (date of birth of applicant 1 and date of birth of applicant 2.

i've written a query that looks for any records where the data in DOB1 and DOB2 match the data in another record for DOB1 and DOB2. i hope that makes sense. this is what i've written.

In (SELECT [App1 DOB] FROM [Data] As Tmp GROUP BY [App1 DOB],[App2 DOB] HAVING Count(*)>1 And [App2 DOB] = [Data].[App2 DOB])

this seems to work but please feel free to correct me.

Now the part i'm struggling with is trying to find duplicates where the fields have been reversed. eg App1 DOB and App2 DOB are the same as App1 DOB and App2 DOB in another record but applicant 1's Date of birth has been stored as Applicant 2 and Applicant 2 is now stored as Applicant 1. i hope thats not to confusing.

Basically i'm trying to check for duplicate mortgage applications in the database but sometimes applicant 1 and applicant 2 are reversed.

Any help would be greatly appreciated.

Thanks

At first glance it appears you may have some table structure issues.
You may want to normalize your tables.
Here's a reference
http://r937.com/relational.html

Usually you would not have 2 fields in a table with names DOB1 and DOB2. Perhaps I'm misunderstanding what you have.

Could you provide some Tables/ fields to show what you have?

 

[gemma-the-husky]

are you designing queries in sql, or using the query design window.

a few thoughts

why use DOBs - instead of names?

the normalisation issue is that you shouldnt really have an applicant 1 and applicant 2 in the same table, as this is not normalised (although it may well be a standard treatment). you ought to have an applicants table, with applicationref, and applicantname for each applicant, although this wouldnt necessarily make your problem any easier


to avoid this problem, couldnt you ensure the applcants are always stored in dob order, or name order for consistency.

 

[luke999]

Hi

thanks for your replies.

yeah i used the query design window to create the sql then i was tring to rewrite it to do what i wanted but i have to admit i'm far from great at sql.

with regard to the structure issues. i know there is a problem.

Basically what has happened is we have an old excel spreadsheet with lots of data on it which we wanted to use a bit better so i imported it as one table into access. maybe not the best thing to do but it was a quick fix. Anyway now i was trying to find the duplicates in the data to avoid faudulant applications. if its going to be to tricky to do i think i'll scrap the idea and start from scratch with a new database with a proper structure. which is probably what i should have done from the start.

thanks anyway.

 

[gemma-the-husky]

i wouldnt have thought this was hard - especially if there are only 2 applicants per mortgage or more?

can you provide a sample of the data with names changed etc to protect the innocent

 

[luke999]

Hi

Thanks here's a example of the data its just in a table called Data.

i'm wanting to check for duplicates in entries in App1 DOB and App2 DOB even if App1 DOB and App2 DOB have been reversed. hope this makes sense.

ID Date App Out Date App In Name App1 DOB App2 DOB Lender
4968 24/01/2008 29/01/2008 smith 30/06/1965 16/07/1970 Halifax
4969 24/01/2008 01/02/2008 jones 15/06/1978 27/02/1973 Nationwide
4970 25/01/2008 14/02/2008 green 10/08/1971 11/10/1974 FNMC
497 25/01/2008 31/01/2008 khan 11/10/1947 23/12/1951 FNMC

I'm away for the weekend now so if you respond and i don't reply i'm not ignoring you. i really apreciate your replies.

Thanks. Have a great weekend.

 

[gemma-the-husky]

ive copied your data into a table (note that i had to save it as a csv file for some reason)

and generated a few queries to do different things, matchnig dates.

is this the sort of thing you wanted?

 

[luke999]

Hi

Yeah thats what i was trying to do. thanks a lot, thats great.

 

[luke999]

Hi

i've just finished doing all the work i was doing with this database and presented it to my boss. she was really pleased with it. i just wanted to say a big thanks again for your help in the duplicates section. Its really apreciated.