Select from hundreds of tables at once (.mdb)

Question

We have .mdb file with hundreds of tables: Lesson1, Lesson2, Lesson3, Lesson4, etc. All tables have the same structure:

Lesson<n>
----------------
slide_id
name
description
status
created_date
created_by
updated_date
updated_by

What SQL statement would generate a result like this:

| table_name | slide_id | name                          |
|-----------------------|-------------------------------|
| Lesson1    | 1        | name for slide 1 of lesson 1  |
| Lesson1    | 2        | name for slide 2 of lesson 1  |
| Lesson2    | 1        | name for slide 1 of lesson 2  |
| Lesson2    | 2        | whatever                      |
| Lesson2    | 3        | again whatever                |

etc.

So there are a few points here:

1. table names must be included
2. there are hundreds of tables

Answer 1

Using sql server, I can unfortunately see this only done with a CURSOR X-(.

This should help

DECLARE @Name VARCHAR(50)
DECLARE Cur CURSOR FOR
SELECT  name 
FROM    sysobjects 
WHERE   xtype = 'U' 
and  name like 'Lesson%'

OPEN Cur
FETCH NEXT FROM Cur INTO @Name

DECLARE @RetTable TABLE(
     TableName VARCHAR(50),
     slide_id INT,
     name VARCHAR(100)
)

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @RetTable EXEC ('SELECT ''' + @Name + ''',slide_id , Name FROM ' + @Name)
    FETCH NEXT FROM Cur INTO @Name
END

CLOSE Cur
DEALLOCATE Cur

SELECT  *
FROm    @RetTable

OK, then if you can use a macro/vba code you can create a temp table called AllLessons and run the following code. I tested this from a form with a button.

Private Sub Command0_Click()
Dim iTable As Integer
    For iTable = 0 To CurrentDb.TableDefs.Count - 1
        Dim tableName As String
        tableName = CurrentDb.TableDefs(iTable).Name
        If (Left(tableName, Len("Lesson")) = "Lesson") Then
            CurrentDb.Execute "INSERT INTO AllLessons ([table_name],[slide_id],[name]) SELECT """ & tableName & """, [slide_id],[name] FROM " & tableName
        End If
    Next iTable
End Sub

Answer 2

If the table names are known, you can create a query like:

 SELECT 'Lesson1' AS table_name, slide_id, name, ... FROM Lesson1
 UNION ALL SELECT 'Lesson2', slide_id, name, ... FROM Lesson2
 UNION ALL SELECT 'Lesson3', slide_id, name, ... FROM Lesson3
 UNION ALL SELECT 'Lesson4', slide_id, name, ... FROM Lesson4
 UNION ALL SELECT 'Lesson5', slide_id, name, ... FROM Lesson5

Cursors are only needed if the number of tables is in constant flux. If not, this should do the trick.

Hint: to generate the initial query, paste the names of the table in Excel, and use a formula in the next cell over to create that table's "UNION ALL" statement. Then copy and paste straight back into Access. (Or create it dynamically using a cursor, but copy/paste and a quick formula is easy, and you can save the excel file just in case you need to add tables in bulk, change the columns selected, etc.)

And, obviously, the end solution should be to consolidate the tables, if possible, and add a discriminator field when querying. Heck, if you have to, it's easier to maintain hundreds of queries that each pull one lesson's rows (again, Excel can be a handy batch-update tool), than hundreds of lessons tables that must have identical structures.