CODEWITHSUNDEEP
sqlvb.netms-accesscomboboxoledb
gromit1
gromit1

Reputation: 587

Dynamically Add Fields in Access Saved Query

I have a ComboBox named ComboBox_Calculations in my program that is filled with calculations (ex. Current_Ratio, Total_Debt_To_Assets_Ratio). ComboBox_Calculations is actually populated by using the contents of a field in a tabled called Criteria in my Access Database.

I need to create a saved query called Calculations_Quarterly. The query needs to have fields of Ticker, [Year], Period and then one field for every item in ComboBox_Calculations. So for this example I would also need fields named Current_Ratio and Total_Debt_To_Assets_Ratio.

Here is what my tables look like so far:

Criteria Criteria Calculations_Quarterly Calculations_Quarterly Here is the code I have that adds one field at a time (in this case Current_Ratio. But I do not know how add a column for every item in ComboBox_Calculations

con.Open()
    Dim dr As OleDbDataReader
    Dim cmd6 As New OleDb.OleDbCommand("SELECT Calculation, [Interval], Formula FROM Criteria", con)
    dr = cmd6.ExecuteReader
    dr.Read()
    Dim Calculation = dr("Calculation").ToString
    Dim Interval = dr("Interval").ToString
    Dim Formula = dr("Formula").ToString
    Dim Where_Statement As String
    If Interval = "Daily" Then
        Where_Statement = "WHERE Historical_Stock_Prices.[Date] < " & DateTime.Now.Date & ""
    ElseIf Interval = "MRQ" Then
        Where_Statement = "WHERE Income_Statements.Period < 5"
    ElseIf Interval = "TTM" Then
        Where_Statement = "WHERE Income_Statements.Period < 5"
    ElseIf Interval = "5 Year" Then
        Where_Statement = "WHERE Income_Statements.Period = 5"
    Else
        Where_Statement = ""
    End If

    Try
        Dim cmd2 As OleDbCommand = New OleDbCommand("CREATE PROC " & Calculation & " AS SELECT Income_Statements.Ticker, Income_Statements.[Year], Income_Statements.Period, " & Formula & " AS " & Calculation & " FROM Income_Statements INNER JOIN Balance_Sheets ON (Income_Statements.Ticker = Balance_Sheets.Ticker) AND (Income_Statements.[Year] = Balance_Sheets.[Year]) AND (Income_Statements.Period = Balance_Sheets.Period)" & Where_Statement & "", con)
        cmd2.ExecuteNonQuery()
    Catch ex As Exception
    Finally
        Dim cmd2a As OleDbCommand = New OleDbCommand("DROP PROCEDURE " & Calculation & "", con)
        cmd2a.ExecuteNonQuery()
        Dim cmd2b As OleDbCommand = New OleDbCommand("CREATE PROC " & Calculation & " AS SELECT Income_Statements.Ticker, Income_Statements.[Year], Income_Statements.Period, " & Formula & " AS " & Calculation & " FROM Income_Statements INNER JOIN Balance_Sheets ON (Income_Statements.Ticker = Balance_Sheets.Ticker) AND (Income_Statements.[Year] = Balance_Sheets.[Year]) AND (Income_Statements.Period = Balance_Sheets.Period)" & Where_Statement & "", con)
        cmd2b.ExecuteNonQuery()
    End Try
con.Close

Upvotes: 0

Views: 113

Answers (1)

Steve
Steve

Reputation: 5545

I couldnt find the name of you combobox or how it is filled so assuming it is named Combo1, and you filled it using Combo1.Items.Add("..."), you would want something like this:

Dim sb as new System.Text.StringBuilder("")
For each s as string in Combo1.Items
  sb.Append("," & s)
Next

And then in your SQL:

Dim cmd2 As OleDbCommand = New OleDbCommand("CREATE PROC " & Calculation & " AS SELECT Income_Statements.Ticker, Income_Statements.[Year], Income_Statements.Period, " & Formula & " AS " & Calculation & sb.ToString & " FROM Income_Statements INNER JOIN Balance_Sheets ON (Income_Statements.Ticker = Balance_Sheets.Ticker) AND (Income_Statements.[Year] = Balance_Sheets.[Year]) AND (Income_Statements.Period = Balance_Sheets.Period)" & Where_Statement & "", con)

Upvotes: 1

Related Questions