choosing which to table to be queried

[melder]

I have a table where data gets put into a new table a the beginning of each year. The table name is TurnTable and the new table is TurnTable04 the number at the end is just the last 2 digits of the previous year. What I have is a yearly report that I run each run, put what have to do is manually change which year I want to query. What need is a way to programmatic choose which table to query.

 

[Golom]

I infer that you are wedded to this architecture so my first suggestion is probably not what you want to hear.

You are storing data (i.e. the year) not IN the table but rather as the name of the table. SQL (and relational databases generally) are designed to process what's IN the tables but they are not designed to treat tables as part of the data. The preferred solution is to create a table that has the year as a field and add all your data to that table for all years.

To do this you probably need to write some code along the following lines

Dim SQL As String
Dim TableName As String

TableName = "TurnTable04"

SQL = "Select f1, f2, f3, etc " & _
      "From " & TableName & " As T " & _
      "Where ... "