SQL: How to merge result sets based on a shared attribute?

Data and Databases
1

In MS Access 2010, I have several tables that contain names (as primary keys) alongside their ID numbers, along with a lookup table that includes various name variations (primary keys) and their corresponding last names. For example:

TableA
name        ID
"Jones, B" 1
"Smith, A" 2

TableB
name        ID
"Jones"    4
"Smith, Abe" 5

LookupTable
name        Lastname
"Smith, A"  "Smith"
"Smith, Abe" "Smith"

I need a query that will provide the IDs for each unique last name from the lookupTable. The desired output would look like this:

Lastname    TableA.ID    TableB.ID
"Smith"    2            5

I’m struggling to figure out how to construct this query. Currently, I have a query that looks something like this:

SELECT * FROM 
(lookupTable LEFT JOIN TableA ON lookupTable.name = TableA.name) 
LEFT JOIN TableB ON lookupTable.name = TableB.name
2

Hey, JumpingBear! Have you tried idxentifying common substrings for merging? Maybe using pattern matching or string functions could help match the names better. What if the names don’t align perfectly due to typos, though? Would love to know how you’re tackling that!