Query on multiple tables

[mbemis]

Here is a some what complicated question dealing with queries.

I have several tables with data and every month I create a new table with similar data. I am trying to build a query to keep track of certain statistics with in the monthly tables. However I do not want to have to update query each month linking the tables together. What I really would like to do is have a table with the names of each of the tables, which I could up date easily, which links the query to each table to determine the statistics. If this is making any sense, I would appreciate the help.

Thanks!
Mike

 

[AlanJ10]

Right, I think the first answer is that if you're having to add a new table on a monthly basis, then your design is all wrong and you should look at having one table that you add to on a monthly basis.

However, if you're anything like me, you don't want to hear that, you just want someone to answer your question.

I don't know how familiar you are with vba, but you could use something like this to automatically rewrite your query on a monthly basis

dim db as dao.database
dim qryname as querydef

set db = currentdb()
set qryname as db.querydefs("your query name here")

'Get the appropriate table names from your list of table names here

qryname.sql = "the sql of your query here, subbing in the table names"

Hopefully you should be able to write this to make the job of re-writing the query a bit easier.

 

[pbaldy]

Having monthly tables is a normalization problem, and I'd highly recommend not doing it. I'd have one table with a date field.