Multi-way distinct full join

[AnotherJob]

Thank you to those who answered my question about Full Joins in Jet SQL, below. What I actually want to do is a bit more complicated, and is described in the test code I have pasted here. Any suggestions on improving the process through clever Jet SQL would be appreciated. Thank you all again.

'**********************************************************************
' We want to combine several tables into one, joining the tables whereever
' there is a key (TIN) match, but also including all records where the key
' is missing or does not match.
'
' Here is VBA code that does this in several steps, using a temporary table
' to build the full list of key values first.
'
' Table1: Name, TIN
' Table2: City, TIN
' Table3: State, TIN
'
' Producing Table4: Name, City, State, TIN
'**********************************************************************
Public Sub DistinctFullJoin()
   Dim DB As DAO.Database
   Dim SQL As String
   
   Set DB = CurrentDb()
   
   ' Delete the target table's contents.
   SQL = "DELETE FROM Table4;"
   DB.Execute SQL
   
   ' Create a temporary table for the list of TINs.
   SQL = "CREATE TABLE tblTemp (TIN TEXT (255));"
   DB.Execute SQL
   
   ' Add in all the TINs from all the tables, one by one.
   SQL = "INSERT INTO tblTemp SELECT DISTINCT TIN FROM Table1;"
   DB.Execute SQL
   
   SQL = "INSERT INTO tblTemp SELECT DISTINCT TIN FROM Table2;"
   DB.Execute SQL
   
   SQL = "INSERT INTO tblTemp SELECT DISTINCT TIN FROM Table3;"
   DB.Execute SQL
   
   ' Now copy those TINs, distinctly, to the target table.
   SQL = "INSERT INTO Table4 SELECT DISTINCT TIN FROM tblTemp;"
   DB.Execute SQL
   
   ' Drop the temporary table.
   SQL = "DROP TABLE tblTemp;"
   DB.Execute SQL

   ' Drop any initially blank TINs.
   SQL = "DELETE FROM Table4 WHERE TIN IS NULL;"
   DB.Execute SQL
   
   ' Now we'll update the target table with matching values from the other tables.
   SQL = "UPDATE Table4 AS A RIGHT JOIN Table1 AS B ON A.TIN=B.TIN SET A.Name = B.Name;"
   DB.Execute SQL
   
   SQL = "UPDATE Table4 AS A RIGHT JOIN Table2 AS B ON A.TIN=B.TIN SET A.City = B.City;"
   DB.Execute SQL
   
   SQL = "UPDATE Table4 AS A RIGHT JOIN Table3 AS B ON A.TIN=B.TIN SET A.State = B.State;"
   DB.Execute SQL
   
End Sub ' DistinctFullJoin

 

[lameid]

I would make a union query and save it, say query1...

Select TIN 
From Table1
Where TIN Is Not Null

Union

Select TIN 
From Table2
Where TIN Is Not Null

Union


Select TIN 
From Table3
Where TIN Is Not Null

Then you can greatly simplify your code and remove the temp table...

Public Sub DistinctFullJoin()
   Dim DB As DAO.Database
   Dim SQL As String
   
   Set DB = CurrentDb()
   
   ' Delete the target table's contents.
   SQL = "DELETE FROM Table4;"
   DB.Execute SQL
   
    
   ' Add in all the TINs from all the tables
   ' Also copy those TINs, distinctly, to the target table.
   ' Do not include blanks in base query
   SQL = "INSERT INTO Table4 SELECT TIN FROM query1;"
   DB.Execute SQL

   ' Now we'll update the target table with matching values from the other tables.
   'No need to outer join, changed to inner joins

   SQL = "UPDATE Table4 AS A INNER JOIN Table1 AS B ON A.TIN=B.TIN SET A.Name = B.Name;"
   DB.Execute SQL
   
   SQL = "UPDATE Table4 AS A INNER JOIN Table2 AS B ON A.TIN=B.TIN SET A.City = B.City;"
   DB.Execute SQL
   
   SQL = "UPDATE Table4 AS A INNER JOIN Table3 AS B ON A.TIN=B.TIN SET A.State = B.State;"
   DB.Execute SQL
   
End Sub ' DistinctFullJoin

 

[AnotherJob]

The INNER JOIN's will overlook the records that don't have TIN's, won't it? I need to include ALL records from the constituent tables, matching those which pair up, and also keeping those which don't pair up.

What I am doing is creating reconciliation reports, checking data from several sources to report on how it compares. The data is parsed from separate Excel files and text files, each into its own MS Access table, and then the records will be joined into a "super table" for comparision.

 

[lameid]

Then the following shouldn't be in your original code.

   ' Drop any initially blank TINs.
   SQL = "DELETE FROM Table4 WHERE TIN IS NULL;"
   DB.Execute SQL