Finding table names

[wish24bone]

Hi all I have an issue where I'm trying to find an id in a table and then also pull back that table name the id is in..

I have about 6 tables in my database, I have to search through each table looking for an ID value.. I would like to be able to have my query retrieve the ID as well as the table it was found in.. Any ideas?

Thanks in advance

 

[pbaldy]

Your problem implies a normalization problem, but one solution would be to create a UNION query and query that:

SELECT IDField, "Table1" As SourceTable
FROM Table1
UNION ALL
SELECT IDField, "Table2" As SourceTable
FROM Table2
UNION ALL
...