Query Using Multiple Tables Based On A Parameter

  • Thread starter Thread starter Kirk
  • Start date Start date
K

Kirk

I have a database that contains several tables that all
begin with the same string, but end in a date. For example:

reference_093003
reference_083103
reference_073003

The field layout for each table is identical, the only
variable is the date at the end of each table name (each
table contains the data that was current as of its
respective date). I want to create a query that can look
up the data from whichever table corresponds to a user-
entered date. For example, the user will be prompted
to "Enter Month-End Date:" and based on whether the user
enters 083103 (August 31, 2003) or 073003, the query will
refer to the correct table to retrieve the data. I think
this is possible using an alias, but I haven't been able
to find a solution. Can anyone help?
Thanks!
 
I have a database that contains several tables that all
begin with the same string, but end in a date. For example:

reference_093003
reference_083103
reference_073003
Click to expand...

You realize that this is a very bad design, I hope? Storing data in
tablenames is unwise, for exactly the reason you're experiencing. It
would be MUCH better to have a single large table, with an indexed
Date/Time field containing this date. You could then easily search
over all the data in your table, or over a single day's data, or over
a range of dates.
The field layout for each table is identical, the only
variable is the date at the end of each table name (each
table contains the data that was current as of its
respective date). I want to create a query that can look
up the data from whichever table corresponds to a user-
entered date. For example, the user will be prompted
to "Enter Month-End Date:" and based on whether the user
enters 083103 (August 31, 2003) or 073003, the query will
refer to the correct table to retrieve the data. I think
this is possible using an alias, but I haven't been able
to find a solution. Can anyone help?
Thanks!
Click to expand...

The only way to do this is to write VBA code to construct the SQL
string for the query, and then set the Recordsource property of a form
or report to that generated string.
 
John,
That's exactly what we ended up doing. Thanks for helping
us out!
Click to expand...

WHAT did you end up doing - doing it right (normalizing your tables)
or digging yourself deeper in the hole (writing the code)?
 
Back
Top