I just wanted to check in, for 1, to say thank you so much for all the help today, and 2, to share my final query and explain what I did to get my returns.
I parsed out each part of my last name into it's own field and compared it against our client list. I got about 1500 matches. I then did a parse of my first name and conditioned it to return it if the first name matched any part of the first name as well. I ended up with about 25 results. I copied my code. If i messed up on anything or didn't think it through enough, let me know.
SELECT *
FROM SDNList INNER JOIN ClientList ON SDNList.LastName = ClientList.LastName
WHERE ClientList.FirstName = SDNList.FirstName OR ClientList.FirstName = SDNList.FirstName1 OR ClientList.FirstName = SDNList.FirstName2 OR ClientList.FirstName = SDNList.FirstName3
UNION
SELECT *
FROM SDNList INNER JOIN ClientList ON SDNList.LastName1 = ClientList.LastName
WHERE ClientList.FirstName = SDNList.FirstName OR ClientList.FirstName = SDNList.FirstName1 OR ClientList.FirstName = SDNList.FirstName2 OR ClientList.FirstName = SDNList.FirstName3 AND SDNList.LastName1 is Not Null
UNION
SELECT *
FROM SDNList INNER JOIN ClientList ON SDNList.LastName2 = ClientList.LastName
WHERE ClientList.FirstName = SDNList.FirstName OR ClientList.FirstName = SDNList.FirstName1 OR ClientList.FirstName = SDNList.FirstName2 OR ClientList.FirstName = SDNList.FirstName3 AND SDNList.LastName2 is Not Null
UNION
SELECT *
FROM SDNList INNER JOIN ClientList ON SDNList.LastName3 = ClientList.LastName
WHERE ClientList.FirstName = SDNList.FirstName OR ClientList.FirstName = SDNList.FirstName1 OR ClientList.FirstName = SDNList.FirstName2 OR ClientList.FirstName = SDNList.FirstName3 AND SDNList.LastName3 is Not Null
UNION
SELECT *
FROM SDNList INNER JOIN ClientList ON SDNList.LastName4 = ClientList.LastName
WHERE ClientList.FirstName = SDNList.FirstName OR ClientList.FirstName = SDNList.FirstName1 OR ClientList.FirstName = SDNList.FirstName2 OR ClientList.FirstName = SDNList.FirstName3 AND SDNList.LastName4 is Not Null
UNION SELECT *
FROM SDNList INNER JOIN ClientList ON SDNList.LastName5 = ClientList.LastName
WHERE ClientList.FirstName = SDNList.FirstName OR ClientList.FirstName = SDNList.FirstName1 OR ClientList.FirstName = SDNList.FirstName2 OR ClientList.FirstName = SDNList.FirstName3 AND SDNList.LastName5 is Not Null