Matching

[jmriddic]

I guess can someone explain what matching means here. We are comparing people on some government lists to our employee list here. I guess one list will be coming from our system and one from each web sites. HR will be setting up the Excel files for importing to Access. So about 5-6 tables. A Hospital we affliated with for 5 years said this is how they matched people on one list to another. Makes me think maybe a a set of queries where I would connect our employee list table to each table based on the list from each web site from which a report would be construct. But I would only want the list to show anything if the first name and list name is the same between our employee table and the table I am linking to.

This from the Hospital:
The matching process is completed in Access. Each individual table on vendors, employees, physicians, OIG lists, Sams lists, FDA lists and Opt Out lists will need to be imported into Access. Again, someone with technical skills to do with matching in Access is key. The best match will be the first and last name fields. Possible matches must be researched by social security number, birth date or middle names. Do not rely on a middle name for females in the event of a possible maiden name. Also, do not rely on an address as an individual could have moved into the area after having been sanctioned elsewhere.
Titles, professional designators (RN, MD, PHd, JD, etc) are commonly inserted in vendor files and some employee files. Make a good attempt to exclude and remove titles as they generally will not appear in the government listings. In vendor files, periods, comma separators, extra spaces (,L L C or ,L.L.C. or, LLC) should all be formatted the same. Be consistent in both the government files and vendor files as an exact match is required to find a possible match.

 

[CJ_London]

In your scenario, matching simply means matchup up records between two or more tables (doesn't really matter what the source of those tables are).

The problem with exact matches are typos and spelling variations. There is some prework that can be done to ensure formatting is the same (for example to include or exclude a period after an initial, common treatment of apostrophes in names such a O'Leary).

You might want to investigate Simil and Metafone techniques which will match for example McDonald to Macdonald on the basis they sound the same when spoken.

If you are trying to make complete matches then you will need to compare on a number of fields - name variations plus postcode plus address plus NI number etc etc

If you are only interested in exact matches of first and last name then your query is very simple:

Select * FROM Table2 WHERE Table1.FirstName=Table2.FirstName and Table1.LastName=Table2.LastName

If you have a Simon Smith in table 1 this will return all the Simon Smiths in Table 2