I currently have a database that looks for record matches between two tables. It is looking for same FirstName, LastName, MidName, and DOB.
Here is the query:
SELECT [Beaver A&D Waiver].LASTNAME, [Beaver A&D Waiver].FIRSTNAME, [Beaver A&D Waiver].MIDNAME, [Beaver A&D Waiver].DOB, [Beaver A&D Waiver].SSN
FROM [Beaver A&D Waiver] INNER JOIN [Updated Table] ON ([Beaver A&D Waiver].MIDNAME=Left([Updated Table].MIDNAME,1)) AND ([Beaver A&D Waiver].FIRSTNAME=[Updated Table].FIRSTNAME) AND ([Beaver A&D Waiver].LASTNAME=[Updated Table].LASTNAME) AND ([Beaver A&D Waiver].DOB=[Updated Table].DOB)
ORDER BY [Beaver A&D Waiver].LASTNAME, [Beaver A&D Waiver].FIRSTNAME, [Beaver A&D Waiver].MIDNAME;
The query cuts the middle name to only match the first letter and so far it has been flawless. All of the sudden I have a record match of exact first, middle, last, and DOB and it's not detecting the match. I changed match on the midname to look for a whole match instead of the first letter and it worked. I need the search of only the first letter on the middle name because the one table typically only has the first letter of the middle name. I can't seem to figure out why all of the sudden that one record is having an issue with just matching the first letter of the middle name. Other matches with the middle name Robert and R seem to still work.
Any help or changes to the query are greatly appreciated. I was wondering if there is a way to search for full middle name and/or first letter of middle name in the query?
Here is the query:
SELECT [Beaver A&D Waiver].LASTNAME, [Beaver A&D Waiver].FIRSTNAME, [Beaver A&D Waiver].MIDNAME, [Beaver A&D Waiver].DOB, [Beaver A&D Waiver].SSN
FROM [Beaver A&D Waiver] INNER JOIN [Updated Table] ON ([Beaver A&D Waiver].MIDNAME=Left([Updated Table].MIDNAME,1)) AND ([Beaver A&D Waiver].FIRSTNAME=[Updated Table].FIRSTNAME) AND ([Beaver A&D Waiver].LASTNAME=[Updated Table].LASTNAME) AND ([Beaver A&D Waiver].DOB=[Updated Table].DOB)
ORDER BY [Beaver A&D Waiver].LASTNAME, [Beaver A&D Waiver].FIRSTNAME, [Beaver A&D Waiver].MIDNAME;
The query cuts the middle name to only match the first letter and so far it has been flawless. All of the sudden I have a record match of exact first, middle, last, and DOB and it's not detecting the match. I changed match on the midname to look for a whole match instead of the first letter and it worked. I need the search of only the first letter on the middle name because the one table typically only has the first letter of the middle name. I can't seem to figure out why all of the sudden that one record is having an issue with just matching the first letter of the middle name. Other matches with the middle name Robert and R seem to still work.
Any help or changes to the query are greatly appreciated. I was wondering if there is a way to search for full middle name and/or first letter of middle name in the query?
