Use same query with different table

[fashoda]

Every month I create an Excel report, then import it as a table into Access. I created queries to pull certain data from that table. Queries work fine until I import the next months report. Every months table has the same column headings but different values. When I put the new table in the query and change the table to the new one and run it the numbers are all wrong. If I delete the old table everything disappears from the query grid. Basically what I want to do is run the same query every month on a different table. Please help

[matthewspatrick]

Every month I create an Excel report, then import it as a table into Access. I created queries to pull certain data from that table. Queries work fine until I import the next months report. Every months table has the same column headings but different values. When I put the new table in the query and change the table to the new one and run it the numbers are all wrong. If I delete the old table everything disappears from the query grid. Basically what I want to do is run the same query every month on a different table. Please help

There are two things you can do:

1) If you just want to use the query designer, then open the query in design view, add the new table, and for each column in your query change the table designation to the new table. Then delete the old table and save the query.

2) If this is part of a larger process that is pretty stable, automate the process. If all that is is changing is the table name, it would be easy to use VBA to either manipulate the QueryDef object, or if you need it as an action query, build the SQL string in code and run it using DoCmd.RunSQL.