SUM query using a combo box

[kent christensen]

Heres what i have:

i have 3 tables set up that are identical. The only difference is that the tables have different titles as they are databases for different customers.

The table has the following fields:
"RMA #", "Quote ID", "Credit(USD )", "Credit(#)" , "Credit(Euros)" , "Credit(CAD )", and "Credit(YEN )"



I have a form called form1 that has:

A combo box (combo23) set up with the name of the three customer tables.


WHAT I WANT:
I was wondering if there was a way to be able to select the name of the customer table from the combo box,and have a command button query sum the following fields "Credit(USD )", "Credit(#)" , "Credit(Euros)" , "Credit(CAD )", and "Credit(YEN )". I would like this query to be tied to a command button.

Is this possible?

[MMcCarthy]

Hi Kent

OK I think what you want is to set up a dynamic query. I've left space for you to put in what you want to do with the query results as you didn't say.

Private Sub MyCmdButton_Click()
Dim qdf As QueryDef
Dim strSQL AS String

    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _ 
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo23

    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", sqltext)
        DoEvents


        ' Do whatever you want with the query results here ....

        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
    End With

End Sub

[kent christensen]

MMcCarthy,

Once again thanks for the help. Sorry i forgot to tell you where i wanted the results to go. I would like the results to be updated into a table that i just created. The table is titled "Customer Table" with the following fields:
"Customer", "Customer ID", "Credit(USD )", "Credit(#)" , "Credit(Euros)" , "Credit(CAD )", and "Credit(YEN )"

I have all of the customers names in the customer field so is there a way that when the sum query runs it only updates the correct customer row (chosen from combo23 like before) and correct fields ("Credit(USD )", "Credit(#)" , "Credit(Euros)" , "Credit(CAD )", and "Credit(YEN )")

What would be the code for the sum query and the query that updates the "customer table". I would like to link it to a command button.

Thanks in advance

[MMcCarthy]

OK try this ...

Private Sub MyCmdButton_Click()
Dim qdf As QueryDef
Dim strSQL AS String
 
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _ 
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo23
 
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", sqltext)
        DoEvents
 
 
        DoCmd.RunSQL "INSERT INTO [Customer Table] (Customer, [Credit(USD)], " & _ 
            "[Credit(#)], [Credit(Euros)], [Credit(CAD)], and [Credit(YEN)], [Credit(AUD)]) " & _
            "SELECT '" & Me!Combo23 & "' AS Customer, SumCreditUSD, [SumCreditC#], " & _
            "SumCreditEuro, SumCreditCAD, SumCreditYen, SumCreditAUD " & _
            "FROM MyQuery"

        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
    End With
 
End Sub

I've included CreditAUD as you had it in the original thread, you can remove it if you don't need it. I've also left out CustomerID as I'm not sure where you are getting it from.

Mary

[kent christensen]

Mary,

I did need CreditAUD so thanks for including that. The Customer ID is a column next to the "customer" field that is already been manually typed into the table for each customer. So im not very good at VB code so i just cut and pasted what you wrote but it doesn't seem to work. I get the following error message "Invalid SQL statement; expected 'Delete','Inser t','Procedure', 'Select' or 'Update'". I have included the code for the command button below. Could you please help me fix the code.
Thanks in advance.

Private Sub Command26_Click()
On Error GoTo Command26_Click_Err

    Dim qdf As QueryDef
Dim strSQL As String
  
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo23
  
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", sqltext)
        DoEvents
  
  
        DoCmd.RunSQL "INSERT INTO [Customer Table] (Customer, [Credit(USD)], " & _
            "[Credit(#)], [Credit(Euros)], [Credit(CAD)], and [Credit(YEN)], [Credit(AUD)]) " & _
            "SELECT '" & Me!Combo23 & "' AS Customer, SumCreditUSD, [SumCreditC#], " & _
            "SumCreditEuro, SumCreditCAD, SumCreditYen, SumCreditAUD " & _
            "FROM MyQuery"
  
        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
    End With


Command26_Click_Exit:
    Exit Sub

Command26_Click_Err:
    MsgBox Error$
    Resume Command26_Click_Exit

End Sub

[MMcCarthy]

I can get the customer name from the drop down box but is the customerID in the drop down box as well. When I say where do I get it, I mean where on the form can I find that value to input it into the table.

[MMcCarthy]

In the meantime, I don't see any real problem with what you copied and pasted. However, I have added a line of code at line 20 which will print out the query to the "Immediate" window. We should have a better idea what's going on then. Assuming the code stops on error again. Copy the query from the immediate window and then open a new query in Access design view. Then change the view to sql and paste in the query code. The error message should tell us what's wrong.

Private Sub Command26_Click()
On Error GoTo Command26_Click_Err
Dim qdf As QueryDef
Dim strSQL As String
 
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo23
 
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", strSQL)
        DoEvents
 
        strSQL = "INSERT INTO [Customer Table] (Customer, [Credit(USD)], " & _
            "[Credit(#)], [Credit(Euros)], [Credit(CAD)], and [Credit(YEN)], [Credit(AUD)]) " & _
            "SELECT '" & Me!Combo23 & "', SumCreditUSD, [SumCreditC#], " & _
            "SumCreditEuro, SumCreditCAD, SumCreditYen, SumCreditAUD " & _
            "FROM MyQuery"
        Debug.Print strSQL
        DoCmd.RunSQL strSQL
        
        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
        
    End With
 
Command26_Click_Exit:
    Exit Sub
 
Command26_Click_Err:
    MsgBox Error$
    Resume Command26_Click_Exit
 
End Sub

[kent christensen]

Mary,

For the customer ID should i create a new combo box with customer and customer ID. If so it will be "Combo30"

As for the code, it still gives me an error message that says "object My Query Already exists".

It is not printing the query to the "Immediate" window so it seems as if the query is not even running. Any ideas what could cause this.

[MMcCarthy]

Delete MyQuery, it's left over from when the code errored out last time and the code that deletes it never ran.

In combo30 is the CustomerID in the first or second column?

[kent christensen]

the customer id is in the 2nd column, with the first column being the customer. So what would the new code be with the new combo box. I also just deleted myquery.

[MMcCarthy]

This should work ...

Private Sub Command26_Click()
On Error GoTo Command26_Click_Err
Dim qdf As QueryDef
Dim strSQL As String
 
    strSQL = "SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], " & _
        "Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, " & _
        "Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD " & _
        "FROM " & Me!Combo30
 
    With CurrentDb
        Set qdf = .CreateQueryDef("MyQuery", strSQL)
        DoEvents
 
        strSQL = "INSERT INTO [Customer Table] (Customer, CustomerID, [Credit(USD)], " & _
            "[Credit(#)], [Credit(Euros)], [Credit(CAD)], and [Credit(YEN)], [Credit(AUD)]) " & _
            "SELECT '" & Me!Combo30 & "', " & Me!Combo30.Column(1) & ", SumCreditUSD, [SumCreditC#], " & _
            "SumCreditEuro, SumCreditCAD, SumCreditYen, SumCreditAUD " & _
            "FROM MyQuery"
        Debug.Print strSQL
        DoCmd.RunSQL strSQL
 
        ' When you are finished delete the query object
        DoCmd.DeleteObject acQuery, "My_Query"
        qdf.Close
        Set qdf = Nothing
 
    End With
 
Command26_Click_Exit:
    Exit Sub
 
Command26_Click_Err:
    MsgBox Error$
    Resume Command26_Click_Exit

End Sub

[kent christensen]

It still does not seem to be working. I now get a message of Sytax error in INSERT INTO statement. I tried looking through the code if anything was wrong and cant see anything.
Also in line 24, should the "_" be deleted?

[MMcCarthy]

Sorry about that, yes delete that underscore.

Did you copy the sql from the immediate window and paste it into a query?

[kent christensen]

Mary,
Im sorry but i can't copy the sql from the window because when i click the command button, the error"Sytax error in INSERT INTO statement" shows and then in my navigation pane 'myquery' is there. These are the only things that happen.
Also when i click on the myquery, it does sum the correct table, however all the summed numbers are not in a correct currency format. Is this because the customer table that this query is going into has different currency formats. For example, the credit(#) format is £#,###.##, the credit(Yen) format is ¥#,### and the credit(euro) format is "Euro". The rest of the credits are in basic USD currency.

However, when checking the customer table that the button was pulling from, the currency was all in the correct format.

Also the myquery is still not deleting. Can you please help. Sorry for all the trouble.

The code for myquery is below:

SELECT Sum([Credit(USD)]) AS SumCreditUSD, Sum([Credit(#)]) AS [SumCredit#], Sum([Credit(Euros)]) AS SumCreditEuro, Sum([Credit(CAD)]) AS SumCreditCAD, Sum([Credit(YEN)]) AS SumCreditYen, Sum([Credit(AUD)]) AS SumCreditAUD
FROM AXA;

[kent christensen]

In 'myquery', AXA is the name of one of the customer tables. This is the value pulled from combo30.