Create a query to access different (specify) tables each time the query is run

[AustinGoldfish]

I am trying to change a query to access different tables each time the query is run.

***I know, I know, there are other ways to accomplish this. However, this is what I must to do in order to not rebuild an entire system. Please do not answer that I should change the tables... My boss says he won't allow the rebuild time either. Historical and future data will need continue be done this way. Thus, another reason I cannot push all the data into one table or other off topic solutions. This solution is what I have been given permission to do.***

I have a very detailed Access database (with links to other access databases) created as an invoicing system for our company's unique needs. These invoices rely in part on monthly tables that are created by someone else not tied to our department or with access to anything other than that table in the database (its uploaded via an import).

Okay, a little more detail on what I need:

The monthly tables that vary are called CCL-1212 (for December 2012), CCL-1301 (for January 2013), and so forth. These tables are provided to me with "hard data" that only relates to the given month with lots of other pieces of data. For the sake of simplicity, let's just say it tells me how to invoice certain items for certain customers. Really simplified; In service, Out of Service, Partially invoiced or joint customer invoice charges.

There is a Master Report (let’s call it ReportInvoice) that pulls data from a Master Query (let’s call it QueryMonthlyInv oice) that pulls from several tables with relationships and the one that needs to vary with each monthly report; the CCL-YYMM Master Table file.

Each time I want to run the file I have to frustratingly manually change the table CCL-YYMM to the corresponding month I want the ReportInvoice to cover. If I forget then I don't catch the correct data. (very bad...sigh)

My first solution is that I created a Query called CCL-Query that I go into and "query" that single CCL-YYMM table and drop the asterisk version (of "all data") into it. All the other reports have that generic CCL-Query so that I don't have to worry it might kill the relationships in other areas.

This keeps me from having to change the database name for each field from that database in the Master Query. That was a bit better as it kept me from errantly missing a field or two when updating. It is still time consuming to have to go into that single query each time I want to run a report in order to specify the correct CCL-YYMM timeframe. Forget about running several historical invoices quickly... that is the pain I am trying to avoid.

I want a simple way to not have to do this. I envision this happening in a couple of possible ways. However, I don't know how to code the Master Query to make this work.


A.) I'd love to run the Report and have a selection list box with all the possible CCL-YYMM table options listed (I could create the list easily as there will be one for every month and year). Is there an equivalent of the criteria area to make a pop-up [Select CCL-YYMM table]? Or, similar? I don't mind typing CCL-1212 but a dropdown box would be nice to avoid typos.

or

B.) Would someone post the exact SQL code / Macro code / Module Code and directions on how to do this as a general newbie.

or

C.) Something even easier! ;-)


I know enough (I think) to cut and paste code into a module. But I get confused reading example code that isn't using my terms. So, if any one sees way to rescue me if you would use my terms above that would be great.

Thanks in advance for any help!

[Seth Schrock]

Well, you beat me to the punch on changing your tables. One method would be to create a table and import the data from the monthly tables into the one table and run reports from there. Just something to think about that could save you more time later on.

On to your question... You can change the SQL code of the query that you base your report on. You will need a textbox that holds a date from the month you are wanting to report on. I'll call it txtDate in my code. Stealing code from Query in vba

Dim db as DAO.Database
Dim qdefQuery as QueryDef

Set db = CurrentDb
Set qdefQuery = db.QueryDefs("YourQueryNameHere")
qdefQuery.SQL = "SELECT [I]Your fields[/I] " & _
                "FROM [CCL-" & Format(txtDate, "yymm") & " " & _
                "WHERE [I]your criteria[/I]"

Set qdefQuery = Nothing
Set db = Nothing

DoCmd.OpenReport "QueryMonthlyInvoice"

This would go in the button's OnClick event that you use to open your report.

Please be aware that we really can't provide exact code for you. We don't know your database. More specifically, we don't know your query that you are currently using to know what fields, joins, etc are needed. This site is also not a code writing service. We just point you in the right direction and help you where you need it with specific problems, not whole solutions. Please read the following Posting guidelines and How to ask good questions -read before posting

[NeoPa]

Alternatively, you could use a similar setup, but if you know the old name and the new name of the record sources then the following code should work to change from one to the other :

Dim dbVar as DAO.Database
Dim strOld As String, strNew As String

strOld = "XXX"
strNew = "YYY"
Set dbVar = CurrentDb()
With dbVar.QueryDefs("YourQueryNameHere")
    .SQL = Replace(.SQL, strOld, strNew)
End With
Set db = Nothing

[Rabbit]

Seth's suggestion will certainly work. And is probably the quickest running of all the varied methods of accomplishing this.

Allow me to make an alternate solution. It will be slower than Seth's solution, how much slower depends on the volume of data. But it has the added benefit of not relying on VBA code and bringing all the data into one query allowing you to query multiple tables at once.

The alternate solution is to use a union query:

SELECT *, '1' AS SourceTable
FROM Table1

UNION ALL 

SELECT *, '2' AS SourceTable
FROM Table2