Access SQL union query

Question

I'm using Access 2007 and am relatively new to this, so bear with me.

The Situation

I've created a simplified example to share.

I have a set of tables in my database that change frequently. Sometimes there are a dozen tables and sometimes only 4 or 5. There is a set of important information that is shared by all of the tables and I want to gather this data into a new table.

Example: Suppose we have the following tables and fields:

 Table1 : Name, PhoneNumber, PostalCode,...
 Table2 : Name, Phone, PostalCode, Address,...
 Table3 : Name, PhoneNo, Address, DateOfBirth,...
 Table4 : Name, PhoneNumber, Favorite Food, ...
 Etc....

I have written a query to take in the important information(in this case: Name and Phone Number) into a sort of 'master list':

 SELECT Name, PhoneNumber 
 FROM Table1
 UNION
 SELECT Name, Phone AS PhoneNumber
 FROM Table2
 UNION 
 SELECT Name, PhoneNo AS PhoneNumber
 FROM Table3
 UNION
 etc...
 ;

The Problem

I am trying to find a way to compile this same 'master list' even when some tables aren't in the database. Suppose we have only Table1 and Table3. Is there anyway to add the table to the union query only if it exists? As bad as I am at SQL, I'm even worse at VBA. Somehow I suspect it's possible there, but I thought I'd ask.

Basically, I'm trying to convert this into SQL:

 SELECT Name, PhoneNumber 
 FROM Table1 (IF IT EXISTS)
 UNION
 SELECT Name, Phone AS PhoneNumber
 FROM Table2 (IF IT EXISTS)
 UNION 
 SELECT Name, PhoneNo AS PhoneNumber
 FROM Table3 (IF IT EXISTS)
 UNION
 etc...
 ;

I get an error message saying that Access cannot find the input table. I figured out that I can use the following code to determine if a table exists:

 SELECT Count(*) AS Exists, "Table1" From MsysObjects
 WHERE type=1

Any chance the solution involves this?

Thanks in advance!!

Answer 1

Parameterizing Table Names or conditionally selecting from a table based on table existence doesn't work in database that I know of at the sql level. Instead you have to dynamically create the SQL.

Below is an example that creates a two-dimensional array, that contains a list of table names with it's phone number column name. Builds a sql string dynamically and sets a query definition to that sql string. Presumably you'd call this on start up or whenever else you need it.

It uses Vadim's implementation of Contains but you could have queried MsysObjects instead. It also requires an existing MasterList query def.

Sub Test()

    Dim tableNames(3, 2) As String
    tableNames(0, 0) = "Table1"
    tableNames(0, 1) = "PhoneNumber"

    tableNames(1, 0) = "Table2"
    tableNames(1, 1) = "Phone"

    tableNames(2, 0) = "Table3"
    tableNames(2, 1) = "PhoneNo"

    Dim i As Integer
    Dim sql As String


    For i = 0 To UBound(tableNames, 1)

         If Contains(CurrentDb.TableDefs, tableNames(i, 0)) Then

            sql = sql + " SELECT Name, " & tableNames(i, 1) & " as PhoneNumber "
            sql = sql + " FROM Table1 " & tableNames(i, 0)
            sql = sql + "  UNION"
         End If
    Next

    If Len(sql) >= Len(" UNION") Then
        sql = Left(sql, Len(sql) - Len(" UNION"))
    Else
        sql = ""
    End If

    If sql <> "" Then
        CurrentDb.QueryDefs("MasterList").sql = sql
    End If


End Sub

Public Function Contains(col As Variant, key As Variant) As Boolean
Dim obj As Variant
On Error GoTo err
    Contains = True
    obj = col(key)
    Exit Function
err:

    Contains = False
End Function