Trying to insert table name as field in Union Query

[tubbers]

I'm using the following SQL to create a Union Query that I will eventually append to a table (tblMaster). What I need to do though is to add a field to tblMaster which relates to the tables from which the original data is from.

Select * from Table1
UNION
Select * from Table2
UNION
Select * from Table3

The results of tblMaster should be like this

ID PosX PosY PosZ TableName
1 2 3 4 Table1
2 54 23 33 Table1
3 7 23 66 Table2
4 10 41 64 Table3
5 17 3 43 Table3


I've tried searching to find out how to include the table name in the query but have come up with nothing.

Can anyone point me in the right direction?

 

[tubbers]

I should also mention that I would hope to be able to include the original table name automatically without having to hard code the value in as there are actually more than 3 tables (50+) and the names are not all in the same format.

Thanks.

 

[SkipVought]

Select *, 'Table1' from Table1
UNION
Select *, 'Table2' from Table2
UNION 
Select *, 'Table3' from Table3

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[tubbers]

That worked for all intensive purposes but that means that I'm going through and individually typing in the table name for every single table.

Is there anyway to automatically lookup the table name and include it? Like a function?

What I'm hoping to eventually build up to is a little something like this...

tblMaster (table the union query is appended to)
ID (autonumber)
Source (name of original table)
PosX
PosY
PosZ

tblBatchImport(list of table names that need to be used in the Union/Append query)
SourceType
FilePath
SourceName
FileName (the field that needs to be carried over to tblMaster)

table1(or table2, table3, table4...)
PosX
PosY
PosZ

Do Until tblBatchImport.EOF
SELECT * from [i]'1st table that is listed in tblBatchImport'[/i]
UNION
SELECT * from [i]'next table that is listed in tblBatchImport'[/i]
UNION 
SELECT * from [i]'next table'[/i]
etc., etc.
Loop

Once all of the tables have been appended to tblMaster, I then want to delete the original tables.

I know the code is patchy, just trying to get my thoughts across - hope it makes some kind of sense

 

[SkipVought]

i = 0 
for each tbl in currentdatabase.tables
  i = i + 1
  sql = sql & " SELECT *,'" & tbl.name & "' from " tbl.name 
  if i < currentdatabase.tables.count then
    sql = sql & " Union "
  end if
next

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[tubbers]

Okay, thanks. I've gotten it working.