Comparing Possible Like Records

[sherriross81]

Hello,

I have two tables from two different data sources that somewhat match up.
The first name, last name, and date of service should be the same but I am
noticing that some of the records in one table are a little different in the
spelling of the name so when I compare on these three fields its saying that
they don't exist in the other file although they really do.

Table 1:
first_name
last_name
dos

Table 2:
first name
last name
dos

I want to compare these in a query but where it does not have to be an exact
match on the first name and last name but a likely match. Is there a way to
do this in an Access query (2003) using wild cards or something? I can't
seem to find any examples that have helped me so far.

Thanks in advance!

Sherri

 

[Lord Kelvan]

to put it simply no

you can do a likly match but you have to define the actual result the
best method would be to go throught the table with all the records
that do not return and fix the spelling. this is a reason you should
match things up on a string field becuase of people like me who A
cannot type and B cannot spell.

Hope this helps

Regards
Kelvan

 

[sherriross81]

I agree 1+1 has to equal 2 it can't equal anything else. I want them to
match up I just am not mannually going through and comparing over 4000
records to find the misspellings. Thank your your thoughts.

 

[Access101]

I could see using a method that at least reduces manual processing at the end
(most likely a necessity).

By running your first comoparison of all 3, you weed out the first step.

Then you could compare last names and dates, and do a little manual
adjustment.

Then compare dates, and sort by last, sort by first, and do a little more
manual tweeking.

then hopefully, you are left with only 500 records to go through fully
manually

Good luck.

 

[sherriross81]

I was able to bring both of my tables into SSMS and create something that
joins on dos and dob and then looks for like first and last names and that
seemed to have made a big difference. They will still have to check some
stuff manually but its alot less records. Thanks again for your thoughts.

 

[Access101]

Now that I think about it, you could compare dates, and the first 4 chars of
first, first 4 of last, and the list would be smaller this way as well.

Work your way down first 3 chars, etc. or up, first 5 chars

Glad it's now less work.