Table selection in query

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

Guest

Hi,
Is there a way in Access to be able to prompt for the table name that you
would like to run a query against? What I'm trying to do is to create one
query to access different tables with the same fields but different values.
Can Access accomplish this?
 
The question is "why do you have multiple tables with the same structure"?
You can stick them together in a union query like:

Select "First" as TableName, Field1, Field2, Field3, Field4,...
FROM tblFirst
UNION ALL
Select "Second", Field1, Field2, Field3, Field4,...
FROM tblSecond
UNION ALL
Select "Third", Field1, Field2, Field3, Field4,...
FROM tblThird;

You can then run a query the sets a criteria by the derived TableName field.
 
Hi Duane,

Thank you for your help! The reason for multiple tables with the same
structure is that we would like to keep history on the fields. I was
thinking that we could run one query against these tables should we require
to obtain historical information. Is there a better way of doing this?

Thank you,
Aaron
 
Hi Duane,

Thank you for clarifying. Maybe I need to rethink the way that database
will work. I was looking at trying to automate the database by creating a
macro to run that will copy the current table into a new table, clear the
data from the current table, import the data into the current table and then
the query could run against either the new table or the current table.

Thank you,
Aaron
 
Back
Top