Is there a function that returns the table name in a union query?

[JT]

I am using a union query to join three tables with the same structure. The
tables are named "H_F", "B_D", and "Croydon". I have used the following SQL
syntax:

Select * from [B_D] UNION ALL
Select * from [Croydon] UNION ALL
Select * from [H_F]

As part of the query I would like to add a field saying which table each of
the rows originated from. Would any of you be able to help me with this?

 

[Dirk Goldgar]

I am using a union query to join three tables with the same structure. The
tables are named "H_F", "B_D", and "Croydon". I have used the following
SQL
syntax:

Select * from [B_D] UNION ALL
Select * from [Croydon] UNION ALL
Select * from [H_F]

As part of the query I would like to add a field saying which table each
of
the rows originated from. Would any of you be able to help me with this?

You have to hard-code the name as a calculated field, like this:

Select *, "B_D" AS SourceTable From [B_D]
UNION ALL
Select *, "Croydon" AS SourceTable From [Croydon]
UNION ALL
Select *, "H_F" AS SourceTable from [H_F]

 

[Tom van Stiphout]

On Wed, 27 Aug 2008 06:18:01 -0700, JT <[email protected]>
wrote:

No, but nothing prevents you from writing:
Select *, "B_D" as Tablename from [B_D] UNION ALL
Select *, "Croydon" as Tablename from [Croydon] UNION ALL
Select *, "H_F" as Tablename from [H_F]

-Tom.
Microsoft Access MVP