Changing records in special cases

[Ben_Entrew]

Hi all,
I want to change certain records of a query or table.
Here I tried to change this in a query. Something is missing in my code.
Can someone help me out ?
Thanks in advance.

Regards,
Ben

Public Sub TNS_QUERY()

Dim strSQL As String
Dim x As Double
Dim qdf As QueryDef
strSQL = "SELECT TEST_TNS.[TestID],TEST_TNS.[Division],TEST_TNS.[Customer_Split],SUM([TOTAL_NET_SALES]) as [TNS] " & _
         " FROM TEST_TNS " & _
         " GROUP BY TEST_TNS.TestID,TEST_TNS.[Division],TEST_TNS.[Customer_Split]"
         

    With CurrentDb
                
                Set qdf = CurrentDb.CreateQueryDef("TNS_QUERY", strSQL)
                .Close
    End With

Select Case TNS_QUERY.TestID

Case TNS_QUERY.TestID = "Common"
x = TNS_QUERY.[TNS]

Case TNS_QUERY.TestID = "AK"
TNS_QUERY.[TNS] = TNS_QUERY.[TNS] + (x / 2)

Case TNS_QUERY.TestID = "MC"
TNS_QUERY.[TNS] = TNS_QUERY.[TNS] + (x / 2)

End Select

End Sub

 

[Ben_Entrew]

I guess I can't do it with the query. I have to change the data in the original TEST_TNS table.
Can I use these CASE statements in the SQL or do I have to use ADO commands?
Any suggestions?

 

[David R]

I'm confused. You look like you either need an UPDATE query or to step through your data in VBA. What you've posted is some weird attempt at both.

back up a step. WHAT are you trying to do here? Not code-wise, but what is your business need/process?

 

[pr2-eugin]

I say to you again and again, do not store calculations. If you are not going to stop now, you will not be able to.. You will keep on changing code, adding more complexity to you application by making small changes. Then there will be a point where you will feel completely lost, as it will be completely muddled that you do not know what function does what.

Repent now, while you still have the time to do so.

 

[David R]

Ah, did not know there was backstory here... to back up what Paul is saying, most calculations can be done by query. Almost all, in fact, and the few that can't can be done by function and returned in a query. Access doesn't care if it's looking at a table or a query as the recordsource, so make your life easier on yourself...

 

[Ben_Entrew]

Sorry Paul,David.

Basically I want to add the TNS numbers with the record ID = Common to
the TNS numbers for record ID = AK and MC. There are no calculated fields TEST_TNS.
Later on I want to export the calculated fields at the final table to EXCEL.

Regards,
Ben

 

[David R]

You can export a query to Excel as well. It doesn't have to be stored in a table.

 

[Ben_Entrew]

Hi all,

I moved on and try now to replace values on a table called TEST_TNS.
First I assign a value to x and then want to update the regarding records.
Somehow it tells me that there are too few parameters in the CurrentDb.Execute command.

Can someone help me out here?
Thanks in advance.

Regards,
Ben

Public Sub TNS_UPDATE()

Dim strSQL As String
Dim x As Double
Dim qdf As QueryDef
CurrentDb.Execute "UPDATE TEST_TNS " & _
                  " SET TOTAL_NET_SALES = x " & _
                  " WHERE Division = 'CC' and Customer_Split = '3rd party'", 128

DoCmd.RunSQL "UPDATE TEST_TNS SET [TOTAL_NET_SALES] = [TOTAL_NET_SALES]+(x/2) " & _
             " Where TEST_TNS.Division IN ('AK','MC') and Customer_Split = [3rd party]"

End Sub