Dropdown on form or query to pick table to query

[APechaitis]

I am using a database to track forecasts and I want to be able to run queries on the forecast. I inherited this setup and I can't change the base tables as other people also use them. I'm using Access 2007, if it makes a difference.

There is a table for each month (named June 2011 Forecast, July 2011 Forecast, etc). Each table has the same fields: Item Number, Forecast, Description, Manufacturing Location plus about 12 other fields. All fields in each table are in the same order. Each table has about 225,000 records.

I want to be able to run a query for specific item numbers that sums the forecast for those items regardless of Manufacturing Location. I know how to create this query for an individual table, but is there a way (with a form possibly?) to to specify which table to query? I'm ok with multiple steps (ie, 1st query would just copy the table I'm interested in into a table called "Current_Forecast", then I can run all queries, reports etc off the Current_Forecast table), but I'm open to any solution that works.

Thank you in advance for any advice you can send my way.

 

[Norie]

So you just need a form with a combobox with all the table names, where the table name has Forecast in it.

You could start by creating a blank form, adding a combobox (Combo1) and putting this code in the form's On Load event.

Option Compare Database
Option Explicit
 
Private Sub Form_Load()
Dim tbl As DAO.TableDef
 
    Me.Combo1.RowSourceType = "Value List"    ' can be set in design view
    For Each tbl In CurrentDb.TableDefs
        If tbl.Name Like "*Forecast*" Then
            Me.Combo1.AddItem tbl.Name
        End If
    Next tbl

End Sub

If you don't want to use code for this part you can set the comboboxes row source with this SQL.

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (MsysObjects.Name Like '*forecast*') AND (MsysObjects.Type=1)
ORDER BY MsysObjects.Name;

Whichever you use should give you a combobox with the names of all the tables.

Add a command button to the form as well and in it's On Click event put this.

Private Sub Command0_Click()
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String
Dim strTblName As String
 
    If Not IsNull(Me.Combo1.Value) Then
 
        strTblName = Combo1.Value
 
        strSQL = CurrentDb.QueryDefs("qryForecast").SQL
        ' code here to change SQL of query for selected table
        
        Set qdfTemp = CurrentDb.CreateQueryDef(strTblName & " Query", strSQL)
        
        DoCmd.OpenQuery qdfTemp.Name
 
        MsgBox strTblName
        
        CurrentDb.QueryDefs.Delete qdfTemp.Name

    End If
 
End Sub

What this does:

1. If there is a table selected from the combobox put the name in strTblName

2. Put the SQL from the template query (qryForecast) into strSQL

3. Replace the table name in the SQL with the selected table name.

4. Creates a temporary query and opens it.

5. Deletes the temporary query.

Obviously 3 hasn't been done, though I did test it some ideas with simple queries.

That part depends on the query you would want to run.

Oops, it also depends on you having a 'template' query that can easily be altered for each table by just changing the table name.

 

[Xander117]

You may also want to look at the Northwind DB in 2007. Look at the sales report dialog form, I believe it does something similar to what you are asking for. It may be a little more complex than you are wanting though. Might give you some ideas though. I know I have used a lot from the Northwind DB.

 

[APechaitis]

Thanks Norie! I couldn't get the SQL to work (although I'm not an SQL expert by any means so I could have been doing something wrong) but the 2 pieces of VBA worked like a charm!

And Xander117 thanks for the suggestion of Northwind 2007 - I'd forgotten about that database, it does have some good examples in it to review.

On a side note - does anyone know a good reference guide for learning the differences between Access 2000/2003 and Access 2007? Half of my troubles are coming from not being able to find anything that I know I used to be able to do!

 

[APechaitis]

Oops, I spoke too soon. I missed the " ' code here to change SQL of query for selected table" part.

Basically, I want to use the query template I created (I called it qry_template1) but replace "September 2011 Forecast" with the correct table. I'm going to play around a little bit but I'd appreciate help from anyone that wants to give it a shot.

 

[Norie]

If it's a simple query it's hopefully as straightforward as replacing the table name.

Can you post the SQL for the query?

You can get that by opeing the query in design mode and going to View>SQL.

If this data was all in one table, which to be honest it really should, you wouldn't need all this.

I've not looked at Northwind for ages but if I'm right it uses a form where you can pick the start date/end date or perhaps period.

Those values are then used as parameters in a query that is the recordsource for a report.

I was originally going to suggest you copy all the data into one table.

When you mentioned you couldn't alter the data I wasn't sure you'd be able to do that, so came up with this idea.

If you can copy the data into one table it'd make things a whole lot easier.

 

[APechaitis]

I agree that the data SHOULD be in one table, but that's not what I inherited. Its not a clean database by any means - basically its a large excel spreadsheet that is too big for excel and hence the use of access. And changing the way things "have always been done" could take years in a large corporation like mine, so ...

Option 1: instead of changing the table name for a query, is there a way to make a "table of tables" that could append each month's data to a master table and add a field as to which month the data came from (again, 225,000 rows per month, at least)? Then I could run my queries on this master table.

Option 2: Change the table name for the query. (my original question). My actual query is more complex than this, but if I can make it work for this basic query, I can figure it out for a more complex query later:

SELECT DISTINCTROW [September 2011 Forecast].[Item Number], Sum([September 2011 Forecast].[12 Mth Fcst]) AS [Sum Of 12 Mth Fcst]
FROM [September 2011 Forecast]
GROUP BY [September 2011 Forecast].[Item Number];

Thanks for your help Norie (and others), I understand it's frustrating working with poorly designed databases when life would be simpler if it was setup right in the first place.

 

[Norie]

Option 1:

If you mean combine all the data into one table, with an added field to identify which table it comes from you can start with a query like this:

SELECT *, 'June 2011 Forecast' As fldTableName
FROM [June 2011 Forecast]

UNION ALL

SELECT *, 'July 2011 Forecast' As fldTableName
FROM [July 2011 Forecast]

UNION ALL

SELECT *, 'August 2011 Forecast' As fldTableName
FROM [August 2011 Forecast]

... and so on for all the tables.

You can use code for this and you can also create a table from the resulting query.

Option 2:

Should be able to be done since it's not too hard to identify the table name.