variable table name

[DrJimmy]

Hi,

I have a set of tables for different locations (5 in total) and 1 for each month. They are all called location.month, I want to be able to run a query that takes the month as the parameter and then runs a simple select and group query on the table matching that month.

Can anyone help?

Cheers

DrJimmy

 

[maxmangion]

Hi,

To be honest i do not understand why do you have a table for each location and for each month. Could you explain better what are you trying to achieve as in my opinion it is time for some re-designing.

 

[Kiwiman]

I won't go down the road as to why you have the table structure like you do, but this may work:

Private Sub cmdOK_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strTable as string

' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("YourQuery")

select case me.yourmonthcontrol
case is = 1
    strTable = "yourTable1"
case is = 2
    strTable = "yourtable2"
etc (complete to month 12)
end select


' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM " & strTable 

' Apply the new SQL statement to the query
    qdf.SQL = strSQL

' Open the query
    DoCmd.OpenQuery "YourQuery"

' Empty the memory
    Set db = Nothing
    Set qdf = Nothing

End Sub

 

[neileg]

I will go down the road as to why you have the table structure like you do. It's wrong. You need feilds that identify the location and month, not table names.

 

[DrJimmy]

Sure. We have 5 warehouses in our company and I store stock figures for each month in a table. I then append these to a master table where all of the data is for each warehouse. I then translate the currency into GBP and run some further queries.

Right now I can't actaully think of a good reason why I designed the database that way! Most likely a past problem that I've now overcome so a quick re-design could be helpful - although if you know how to run queries or code with variable table names that would be pretty useful as well!

Cheers

 

[gemma-the-husky]

different ways of achieving this

a) write your queries in vba, sql, and form them on the fly
then you could parameterize the table name to suit
tricky for anything other than simple queries, and will slow down the efficiency of the dbs, as access will have ot recompile a query plan each time

b) have a table called "activewarehouse", and base your queries on that
then depending on which warehouse you need rename the warehouse table you need to be called "activewarehouse", and then restore it afterwards

c) have 5 separate queries, etc, one for each warehouse

-----
did you say you have a separate table for each month as well?


it might be justifiable to have different tables per warehouse, but its really difficult having monthly tables, so another possibility is

d) bite the bullet, and put all your data in 1 table - but this may be a lot of work now