Changing Tables In a Query

[JimMahoney]

I have a series of tables which have quarterly information all in the same format. I have developed a series of queries and reports to extract the information I need. How do I create a front end function that will allow me to change the table that is used in the quesry without having to rename the table or do a cut and paste in the query?
Thanks

 

[B827]

I think that the best way to do this is in code. I use the following procedure quite frequently

Dim SQLString As String, FileName As String, Qdf As QueryDef, Mydb As Database
SQLString = "SELECT ......."
SQLString = SQLString & " FROM ..... WHERE ......."
FileName = "D:\My Documents\TestFile.Txt"
Set Mydb = CurrentDb
Set Qdf = Mydb.QueryDefs("bomtest&quot
Qdf.SQL = SQLString
DoCmd.TransferText acExportDelim, , "bomtest", FileName, True


Obviously you won't be doing the transfer to a text file, but you could then open the report. To enhance things further you could put this code behind a button on a form with a list box of tables and another of reports and use them to select the combination required. Sandy

 

[THoey]

Well, I suggest that you don't... It sounds like you have multiple duplicate structure tables called 2002_1stQTR, 2002_2ndQTR, etc... This is bad design and is going to be nothing but a maintenance nightmare. I suggest that you combine the tables into one, and create some sort of dated column that can be used to create your quarterly data...

If I am way of base on this, I apologize... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...