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.
Address: 123 Main St
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.