How to ID a table source being used in a Union Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a Union Query that uses 3 tables to pull data from. I need to
see identify which of the three tables the data came from. I tried adding a
column to the original tables with a default value "FP 07-09" which would ID
the table for me but my problem is: I cannot fill in this column with that
value on the existing rows of data all at once. I would have to paste it in
one row at a time unless someone can help me understand how to fill down the
column with this value or paste this in the new column on all the existing
rows. Someone else created these tables and I cannot change them. I can add
to them which is what I am trying to do. Each of the three tables will have a
column added titled ID Table and the 3 will each have it's own unique name,
"FP 07-09", "FP 04-05", and "FP 06". If anyone can help with this what seems
like a simple thing to do I would really appreciate it!
 
Add a calculated field to the query that holds the text string that
identifies the table (you'd need to "hard-write" this string into the
query):

SELECT A.*, "TableA" AS TableSource
FROM TableNameA AS A
UNION ALL
SELECT B.*, "TableB" AS TableSource
FROM TableNameB AS B
UNION ALL
SELECT C.*, "TableC" AS TableSource
FROM TableNameC AS C;
 
It looks like Ken's given you an approach to help identify which table
provides the data.

Now my question is why do you have three tables? Often, when someone needs
to use a UNION query to combine data from multiple tables, their tables may
not be well-normalized. Perhaps I'm reading too much into your description,
but tables with names like "FP 07-09" and "FP 04-05" and "FP 06" suggest
that you are capturing data in the name of your tables.

If that's the case, why not have a single table with one additional field to
hold the "type" (or "time period" or ... whatever the titles are telling
you)?

(and a single table means a simpler query!)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I didn't create the original tables that I am using in my Union query. They
are still being added to and I cannot change them. I can only add to them as
they are. Thus the reason for the need for the Union query.
 
Please forgive me as I am pretty new to queries and I really was proud that I
was able to write the Union query, but is what you are saying as follows?
This my union query that I wrote with what you suggested on the end. Will I
need to insert columns in the 3 tables using the names listed? It doesn't
seem to be working for me. Can you help?

SELECT
Code: 
, [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
FROM [FINISHED PRODUCT RETAINS 2002-2003]
UNION ALL
SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
FROM [FINISHED PRODUCT RETAINS 2004]
UNION ALL SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
FROM [FINISHED PRODUCT RETAINS 2007-2009]
ORDER BY [CODE];
SELECT A.*, "TableA" AS TableSource
FROM [FINISHED PRODUCT RETAINS 2002-2003]
UNION ALL
SELECT B.*, "TableB" AS TableSource
FROM [FINISHED PRODUCT RETAINS 2004]
UNION ALL
SELECT C.*, "TableC" AS TableSource
FROM [FINISHED PRODUCT RETAINS 2007-2009]
 
What Ken was suggesting was

SELECT
Code: 
, [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
, "FINISHED PRODUCT RETAINS 2002-2003" as TableSource
FROM [FINISHED PRODUCT RETAINS 2002-2003]
UNION ALL
SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
, "FINISHED PRODUCT RETAINS 2004" as TableSource
FROM [FINISHED PRODUCT RETAINS 2004]
UNION ALL SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
, "FINISHED PRODUCT RETAINS 2007-2009" as TableSource
FROM [FINISHED PRODUCT RETAINS 2007-2009]
ORDER BY [CODE];

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

[QUOTE="Barb H"]
Please forgive me as I am pretty new to queries and I really was proud
that I
was able to write the Union query, but is what you are saying as follows?
This my union query that I wrote with what you suggested on the end. Will
I
need to insert columns in the 3 tables using the names listed? It doesn't
seem to be working for me. Can you help?

SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
FROM [FINISHED PRODUCT RETAINS 2002-2003]
UNION ALL
SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
FROM [FINISHED PRODUCT RETAINS 2004]
UNION ALL SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
FROM [FINISHED PRODUCT RETAINS 2007-2009]
ORDER BY [CODE];
SELECT A.*, "TableA" AS TableSource
FROM [FINISHED PRODUCT RETAINS 2002-2003]
UNION ALL
SELECT B.*, "TableB" AS TableSource
FROM [FINISHED PRODUCT RETAINS 2004]
UNION ALL
SELECT C.*, "TableC" AS TableSource
FROM [FINISHED PRODUCT RETAINS 2007-2009]



[QUOTE="Ken Snell (MVP)"]
Add a calculated field to the query that holds the text string that
identifies the table (you'd need to "hard-write" this string into the
query):

SELECT A.*, "TableA" AS TableSource
FROM TableNameA AS A
UNION ALL
SELECT B.*, "TableB" AS TableSource
FROM TableNameB AS B
UNION ALL
SELECT C.*, "TableC" AS TableSource
FROM TableNameC AS C;
[/QUOTE][/QUOTE]
 
Boolean!!!!!!!!!!!!!! John you rock! I wish I knew more about all this but
I am learning slowly. You saved my life today, well at least the couple of
hours I would have spent trying to figure this one out without you. Thanks a
million!

John Spencer said:
What Ken was suggesting was

SELECT
Code: 
, [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
, "FINISHED PRODUCT RETAINS 2002-2003" as TableSource
FROM [FINISHED PRODUCT RETAINS 2002-2003]
UNION ALL
SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
, "FINISHED PRODUCT RETAINS 2004" as TableSource
FROM [FINISHED PRODUCT RETAINS 2004]
UNION ALL SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
, "FINISHED PRODUCT RETAINS 2007-2009" as TableSource
FROM [FINISHED PRODUCT RETAINS 2007-2009]
ORDER BY [CODE];

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

[QUOTE="Barb H"]
Please forgive me as I am pretty new to queries and I really was proud
that I
was able to write the Union query, but is what you are saying as follows?
This my union query that I wrote with what you suggested on the end. Will
I
need to insert columns in the 3 tables using the names listed? It doesn't
seem to be working for me. Can you help?

SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
FROM [FINISHED PRODUCT RETAINS 2002-2003]
UNION ALL
SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
FROM [FINISHED PRODUCT RETAINS 2004]
UNION ALL SELECT [CODE], [LOT NUMBER], [QTY], [PACKAGING], [FILE BOX]
FROM [FINISHED PRODUCT RETAINS 2007-2009]
ORDER BY [CODE];
SELECT A.*, "TableA" AS TableSource
FROM [FINISHED PRODUCT RETAINS 2002-2003]
UNION ALL
SELECT B.*, "TableB" AS TableSource
FROM [FINISHED PRODUCT RETAINS 2004]
UNION ALL
SELECT C.*, "TableC" AS TableSource
FROM [FINISHED PRODUCT RETAINS 2007-2009]



[QUOTE="Ken Snell (MVP)"]
Add a calculated field to the query that holds the text string that
identifies the table (you'd need to "hard-write" this string into the
query):

SELECT A.*, "TableA" AS TableSource
FROM TableNameA AS A
UNION ALL
SELECT B.*, "TableB" AS TableSource
FROM TableNameB AS B
UNION ALL
SELECT C.*, "TableC" AS TableSource
FROM TableNameC AS C;

--

Ken Snell
<MS ACCESS MVP>




I have created a Union Query that uses 3 tables to pull data from. I
need
to
see identify which of the three tables the data came from. I tried
adding
a
column to the original tables with a default value "FP 07-09" which
would
ID
the table for me but my problem is: I cannot fill in this column with
that
value on the existing rows of data all at once. I would have to paste
it
in
one row at a time unless someone can help me understand how to fill
down
the
column with this value or paste this in the new column on all the
existing
rows. Someone else created these tables and I cannot change them. I can
add
to them which is what I am trying to do. Each of the three tables will
have a
column added titled ID Table and the 3 will each have it's own unique
name,
"FP 07-09", "FP 04-05", and "FP 06". If anyone  can help with this what
seems
like a simple thing to do I would really appreciate it!
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUM in a UNION query 2
Union queries and crosstab... 1
Union Query of Queries 4
Union Query 4
VBA - retrieve data on last create named range from previous inserted named range 0
use tables from union query 1
Union Query 0
Union Query Truncates Results? 2

Back
Top