Query Table Name

[bluenewport]

I union several tables by query. And I plan to use table name to identify data source. Any one can help?

Thnaks

[nico5038]

Just use the UNION query in a maketable and/or createtable query.

Nic;o)

[bluenewport]

Just use the UNION query in a maketable and/or createtable query.

Nic;o)

My main problem in to get Table Name as a result of query.

[nico5038]

Hmm, guess the way then is to use:

select "tblA" as Origin, * from tblA
UNION
select "tblB" as Origin, * from tblB
UNION
select "tblC" as Origin, * from tblC
UNION
etc.

Is the way. To automate the fill of the field [Origin] you'll have to construct the query in code.

Nic;o)

[bluenewport]

Hmm, guess the way then is to use:

select "tblA" as Origin, * from tblA
UNION
select "tblB" as Origin, * from tblB
UNION
select "tblC" as Origin, * from tblC
UNION
etc.

Is the way. To automate the fill of the field [Origin] you'll have to construct the query in code.

Nic;o)

You are righ. That will fill in fixed value "tblA",... in to the field Origin. But what I'm after is sql get "tblA",... itself without my input, just read it from From table. In Excel, there is a function to recall current worksheet name into the cel.

Thanks for help.

[nico5038]

There's a hidden "Msys" system table holding the tablenames, but I don't know a way to dynamically get the name of a query.
I would use VBA to construct the UNION query from code and just itterate through the querydef collection to get the tablename and string the fixed value in the select statement.
The querydef can be managed from code like:

dim qd as querydef

set qd = currentdb.querydefs("qryUNION")

' here create the SQL e.g. in strSQL and use:

qd.SQL = strSQL

' Now the query is saved and can be activated.

Nic;o)

[MMcCarthy]

This query will at least give you the list of table names.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0));

[NeoPa]

I union several tables by query. And I plan to use table name to identify data source. Any one can help?

Thnaks

I've read all three of your posts so far and I'm really not sure what you want exactly.
Can you try to explain clearly exactly what you are trying to achieve.
This way we can stop guessing and answer your question directly.

MODERATOR.