Trying to make a query that's based on a variable table name

[Micah]

I use Access 2007. So essentially I'm creating a database that will be
updated once a month. Every month a new table will be added to it with the
data from that month (the data is irrelevant) and will be title appropriately
(tablename: 0409Data for April of 09). I have a query that calculates
"scores" for each row in each table for the past three months, and a report
that displays those scores along with some other information.

My question is, how can I make the query draw the information from a
variable table name, so that I don't have to go and change the query every
time I add a table? Essentially, I want the end of my query to read "FROM
tblVariable" as opposed to "FROM 0409Data". I've tried setting a global
variable in a module and pulling that, but it didn't work. I also tried to
set it to a variable all within SQL and that didn't work either.

So how do I do it? If it's not possible to set a variable table name, would
it be possible to write a script in VB that would automatically update the
query when a new table was added to change the name of the table it's pulling
from? The field names not working isn't an issue, as each table has
identically named fields (they're automatically generated every month).

Thank you.

 

[KARL DEWEY]

how can I make the query draw the information from a variable table name,
so that I don't have to go and change the query every time I add a table?

Redo your database table structure so that you use ONE TABLE and not one per
month. Just add a DateTime field in the ONE TABLE to tell you when the data
is for.

To get from where you are to the ONE TABLE use a union query like this --
SELECT Field1, Field2, Field3, #1/1/2009# AS MyDate
FROM [0109Data]
UNION ALL SELECT Field1, Field2, Field3, #2/1/2009# AS MyDate
FROM [0209Data]
UNION ALL SELECT Field1, Field2, Field3, #3/1/2009# AS MyDate
FROM [0309Data]
....
UNION ALL SELECT Field1, Field2, Field3, #6/1/2009# AS MyDate
FROM [0609Data];

Once you have created your ONE TABLE then append from the union query to it.

 

[Micah]

That works, thank you.

so that I don't have to go and change the query every time I add a table?

Redo your database table structure so that you use ONE TABLE and not one per
month. Just add a DateTime field in the ONE TABLE to tell you when the data
is for.

To get from where you are to the ONE TABLE use a union query like this --
SELECT Field1, Field2, Field3, #1/1/2009# AS MyDate
FROM [0109Data]
UNION ALL SELECT Field1, Field2, Field3, #2/1/2009# AS MyDate
FROM [0209Data]
UNION ALL SELECT Field1, Field2, Field3, #3/1/2009# AS MyDate
FROM [0309Data]
....
UNION ALL SELECT Field1, Field2, Field3, #6/1/2009# AS MyDate
FROM [0609Data];

Once you have created your ONE TABLE then append from the union query to it.

I use Access 2007. So essentially I'm creating a database that will be
updated once a month. Every month a new table will be added to it with the
data from that month (the data is irrelevant) and will be title appropriately
(tablename: 0409Data for April of 09). I have a query that calculates
"scores" for each row in each table for the past three months, and a report
that displays those scores along with some other information.

My question is, how can I make the query draw the information from a
variable table name, so that I don't have to go and change the query every
time I add a table? Essentially, I want the end of my query to read "FROM
tblVariable" as opposed to "FROM 0409Data". I've tried setting a global
variable in a module and pulling that, but it didn't work. I also tried to
set it to a variable all within SQL and that didn't work either.

So how do I do it? If it's not possible to set a variable table name, would
it be possible to write a script in VB that would automatically update the
query when a new table was added to change the name of the table it's pulling
from? The field names not working isn't an issue, as each table has
identically named fields (they're automatically generated every month).

Thank you.