Need Access VBA routine to search and match records - HELP!

I have a customer database in Access that starts with your standard fields:

 firstname, lastname, address, city, state, zip, phone, email, etc ...

I have a need to update the customer record from Excel files that contain additional data, but do not have a common ID field.  But they always have firstname, lastname, address, city, state, zip.  One spreadsheet may have these five fields, and then have other data such as DOBs, account number, etc.  and another spreadsheet has the same five fields and then email address, HHD incomer, etc.

I have written VBA routines that can open the Excel file, and update a field in the Customers table, but the issue I am having is matching the spreadsheet record to the table record without using an unique ID field (because it simply doesn't exist).

Looking for a routine that looks at the first record in the Excel file, and searches the customer file using some sort of matching routine that returns as close to one record as possible.  It's OK for it to not be able to match exactly sometimes and prompt the user with a couple of possibilities, but for the most part it needs to be able to match the record as closely as possible without user intervention.

ex:
  FirstName: John
  LastName: Doe
  Address: 123 Main St
  City: Anytown
  State: OH
  Zip: 44123

Unable to match record ... please choose from the following possibilities ...

Jane Doe  123 Main St, Anytown OH 44123
John Do  123 Main St, Anytown OH 44123
John Doe 234 Second St, Anothertown, OH 44321

I can't seem to figure out how to approach the query that will 'match' or attempt to match the Excel record to the Access database.  

Any ideas?
callstateAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
In the most basic sense you should import all the Excel files into Access.
Then you can run queries to match the names (or find unmatched names).
There is a query wizard for both Matched and UnMatched records.

As far as your: "choose from the following"
...requirement, ...
This is much more complicated as it would need to do a "Fuzzy" match (seemingly on more than one field per record).
(Research "Soundex" here)

JeffCoachman
0
callstateAuthor Commented:
The Excel files will be submitted at different times ... i.e. maybe today, maybe two months from now ... and be an ongoing process - so the import code needs to be able to browse to the file, open it, etc..

I can write that ... I'm having trouble with the query part.  What is a 'fuzzy' match and how do you create a query or code to create one?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I believe what Jeff means is you should import your Excel data into "staging" tables, and then perform your queries there. You'll find that to be much easier to manage than looping through Excel sheets line-by-line.

You can use TransferSpreadsheet to move your Excel files in:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "StagingTable", "Path to Excel File"

Once you do that, you can then query StagingTable as needed.

You might consider a "scored" approach - that is, query StagingTable and assign a "score" based on how well each record matches the various criteria. For example:

First, add a field named "Score" to the table.

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM StagingTable WHERE FirstName LIKE '*" & Me.txFirstName & "*'")

If Not (rst.EOF and rst.BOF) Then
  Do Until rst.EOF
    rst.Edit
    rst("Score") = Nz(rst("Score"),0) + 10 ' assign 10 points for a first name match
    rst.Upate
    rst.MoveNext
  Loop
End If

rst.Close
Set rst = Currentdb.OpenRecordset("SELECT * FROM StagingTAble WHERE LastName LIKE '*" & Me.txLastName & "*'")

If Not (rst.EOF and rst.BOF) Then
  Do Until rst.EOF
    rst.Edit
    rst("Score") = Nz(rst("Score"),0) + 15 ' assign 15 points for a last name match
    rst.Upate
    rst.MoveNext
  Loop
End If

And so on ...

When  you've finished, you could then present the user with the most likely matches:

SELECT * FROM StagingTable WHERE Nz(Score,0)>0 ORDER BY Score DESC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

callstateAuthor Commented:
Hmmmmm...... I see.  I'll have to play around with the scored approach and see how that works.  There are 600-1000 names everytime I do an import, and what I'd like to do is limit the number of times I need to manually match the data from the spreadsheet to the record.

Yes, I agree - much easier to import the file first and work with the imported table.  I missed that was the point he was making.  

How to decide what 'matches' is the tough part
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm doing something similar with Invoice imports to an ERP system, and this seems to work well for them. They're able to see "intelligent" choices for their import, and it helps them to narrow things down. It's not perfect - and a system like this will never be - but it eliminates about 95% of the previous work they were doing.

I'd also include exact matches - for example, the FIRST SELECT statement I did would be to match on firstname + lastname + address + city + state + zip. If I matched on those, I wouldn't go any further.

You may also find that you need to use logic like that for your matching. For example, a match on lastname + address + city + zip might be ranked a 95, while a match on firstname + address + city + zip might rank a 70 (since FirstNames are generally more common than LastNames).
0
callstateAuthor Commented:
Jeff - Thanks for the insight into Soundex, got me thinking but not sure that it's what I'm after

LSM - You said you're doing something similar.  Do you have an Access DB that I can see work?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No, the database is proprietary and I'm not able to provide you with it (or with specific code samples). The gist of it, however, is very much like what I posted above. Try working with that and adapting it to your application, and report back here with troubles.
0
callstateAuthor Commented:
LSM - Thanks!  I am an end-user, not a full-time programmer, that modifies my db as my needs dictate.  Although I can understand many code snipets, I am not an 'expert'.  I will see if I can apply your example to my needs - thanks for sharing! - Jim
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The concept of matching records in this fashion would certainly be higher level stuff.

Take your time and try to understand exactly what you're doing when you write the code. If you get stuck, let us know and we'll try to help.
0
callstateAuthor Commented:
Close to what I need
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.