callstate
asked on
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?
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?
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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).
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).
ASKER
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?
LSM - You said you're doing something similar. Do you have an Access DB that I can see work?
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.
ASKER
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
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.
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.
ASKER
Close to what I need
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