Add a field to a Make table on the fly

[Guest]

I have a DB with 5 tbl that all have like keys but different data. I need to
be able to search the 5 tbls and return to the user all the find condition
and allow them to select the one they want by dblclick. I want to add a field
on the fly as I build the search query to id what tbl the record came from is
there a simple way? the field can have a constant for each of the different
tbl like tbl1, tbl2, etc...

 

[Guest]

Unfortunately is sounds like you have similar data in different tables. Not
good. You'll need to create a UNION ALL query to join all the tables. It
would look something like:

SELECT "table1" as TheTable, field1, field2, field3
FROM table1
UNION ALL
SELECT "table2" as TheTable, field1, field2, field3
FROM table2
UNION ALL
SELECT "table3" as TheTable, field1, field2, field3
FROM table3
UNION ALL
SELECT "table4" as TheTable, field1, field2, field3
FROM table4
UNION ALL
SELECT "table5" as TheTable, field1, field2, field3
FROM table5 ;

 

[Guest]

Use a union query and add your fields like this ---
SELECT xxx, yyy, "tbl1" AS [Which table]
FROM tbl1
UNION ALL SELECT xxx, yyy, "tbl2" AS [Which table]
FROM tbl2