I am developing a reporting tool in MS Access. I have a form with a graph and combo boxes. The user enters the form, selects what they want to view based on the combo boxes, and clicks a search command button.
Each time the button is clicked, the view is checked using vb code, and the appropriate SQL statement is chosen. The SQL statement is a SELECT INTO - and the table that is modified each time the Search button is clicked is the rowsource of the graph.
THE PROBLEM:
Each month a new data feed will come in with the most recent 36 months of data. For the purposes of my code, I standardized the column headings to read 1 through 36, instead of the month-year (i.e. Dec-99). I could say SELECT 1 AS Dec-99, but then my code will need to be updated every month.
My code is as follows:
Dim sql As String
sql = "SELECT DataTable.Product as [grpby],
Sum(Data_Table. [1]) AS [1],
Sum(DataTable.[2]) AS [2], .........etc. to 36" _
INTO Graph_Table FROM Data_Table WHERE Data_Table.Level = ""TOTAL"" And Data_Table.Channel = [Forms]![IMS RPP Graph]![cboChannel].[Value] And Data_Table.Measure = [Forms]![IMS RPP Graph]![cboGraphView].[Value] " _
GROUP BY IMS_RPP_Monthly_Sales_Table.Product;"
docmd.RunSQL (sql)
Graph.Requery
I tried numerous other things, but I have had no success. Any one have any suggestions, or run into the same problem in the past????
I tried renaming the column with a variable, a function......nothing!
PLEASE HELP!!!
JBurg11
Each time the button is clicked, the view is checked using vb code, and the appropriate SQL statement is chosen. The SQL statement is a SELECT INTO - and the table that is modified each time the Search button is clicked is the rowsource of the graph.
THE PROBLEM:
Each month a new data feed will come in with the most recent 36 months of data. For the purposes of my code, I standardized the column headings to read 1 through 36, instead of the month-year (i.e. Dec-99). I could say SELECT 1 AS Dec-99, but then my code will need to be updated every month.
My code is as follows:
Dim sql As String
sql = "SELECT DataTable.Product as [grpby],
Sum(Data_Table. [1]) AS [1],
Sum(DataTable.[2]) AS [2], .........etc. to 36" _
INTO Graph_Table FROM Data_Table WHERE Data_Table.Level = ""TOTAL"" And Data_Table.Channel = [Forms]![IMS RPP Graph]![cboChannel].[Value] And Data_Table.Measure = [Forms]![IMS RPP Graph]![cboGraphView].[Value] " _
GROUP BY IMS_RPP_Monthly_Sales_Table.Product;"
docmd.RunSQL (sql)
Graph.Requery
I tried numerous other things, but I have had no success. Any one have any suggestions, or run into the same problem in the past????
I tried renaming the column with a variable, a function......nothing!
PLEASE HELP!!!
JBurg11
