x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 380

# SQL UPDATE query based on calculations

Part of the criteria for this update query is a calculation.  Currently this figure 'liTotalCost' is made just once.  This number needs to be calculated for each record.  How do I integrate these calculations into the query?  I am working with VB6, Access 2003.  Thank you for your help.
``````Dim strSQL5 As String
Dim liTotalCost As Long
Dim liBalMD As Long
Dim liEstMD As Long
Dim liActMD As Long

liBalMD = (CDbl([A]) + CDbl([B]) + CDbl([C])
liEstMD = (CDbl([X]) + CDbl([Y]) + CDbl([Z])

If liBalMD < 0 Then
liActMD = (liEstMD + liBalMD)
Else
liActMD = liEstMD
End If
liTotalCost = (CDbl([ActMDCost]) - liActMD)

strSQL5 = "Update DISTINCTROW [ProjOverview] o Left Join (SELECT ProjID, SUM(Amount) AS SumOfAmount From JONProjJoins GROUP BY ProjID) s ON s.ProjID = o.IDNmbr Set [Status] = 'Complete', [Complete] = Now , [TotalServicesAct] = [TotalServicesEst] Where (([Status] = 'Funded') AND (" & liTotalCost & " > Nz(s.SumOfAmount, 0))) OR (([Status] = 'Funded') AND (" & liTotalCost & ") > ([TotalEstimate]))"
DoCmd.RunSQL strSQL5
``````
0
KimD2
• 5
• 5
1 Solution

Commented:
It looks OK to me, except for an extra paren.  It should be:
``````...Where (([Status] = 'Funded') AND (" & liTotalCost & " > Nz(s.SumOfAmount, 0)) OR (([Status] = 'Funded') AND (" & liTotalCost & ") > ([TotalEstimate]))"
DoCmd.RunSQL strSQL5
``````
0

Author Commented:
"liTotalCost" needs to be re-calculated for each record.  Is it being recalculated when written like this?  I am new to this and not sure whay the query is not working.  Thank you for the paren note.  Unfortunately that was not it.  I do not receive an error - just the records that should be updated are not.    I appreciate your input.
0

Application Development ManagerCommented:
Put your Sum query in the Where clause

strSQL5 = "Update DISTINCTROW [ProjOverview] o " & _
"Set [Status] = 'Complete', " & _
"[Complete] = Now , " & _
"[TotalServicesAct] = [TotalServicesEst] " & _
"Where (([Status] = 'Funded') " & _
"AND (" & liTotalCost & " > Nz(( SELECT SUM(Amount) AS SumOfAmount " & _
"From JONProjJoins s " & _
"where s.ProjID = o.IDNmbr " & _
"GROUP BY ProjID), 0))) OR (([Status] = 'Funded') AND (" & liTotalCost & ") > ([TotalEstimate]))"
DoCmd.RunSQL strSQL5
0

Author Commented:
Thank you but it is still not updating.  Do you have any other suggestions?
0

Application Development ManagerCommented:
In what way is it not updating ?  Are you getting an error when you run the Update Query ?
0

Author Commented:
I do not get any errors.  It seems to run but the fields are not updated.  In the records where the criteria is met the fields (Status, Date.. ) remain unchanged.
0

Application Development ManagerCommented:
ok, check your where clause, step through the code and see if the where returns data.

Example:

Open a SQL editor:

``````Select * from [ProjOverview] o
Where ( ( [Status] = 'Funded')
AND ( 1000 > Nz(
( SELECT SUM(Amount) AS SumOfAmount
From JONProjJoins s
where s.ProjID = o.IDNmbr
GROUP BY s.ProjID), 0
)
)
) OR ( [Status] = 'Funded' AND 1000 > [TotalEstimate])
``````
0

Application Development ManagerCommented:
The more I look at this the more I want to remove some of these parenthesis and help you clean it up  :)

``````Dim strSQL5 As String
Dim SumSql  As string
Dim liTotalCost As Long
Dim liBalMD As Long
Dim liEstMD As Long
Dim liActMD As Long

liBalMD = (CDbl([A]) + CDbl([B]) + CDbl([C])
liEstMD = (CDbl([X]) + CDbl([Y]) + CDbl([Z])

If liBalMD < 0 Then
liActMD = (liEstMD + liBalMD)
Else
liActMD = liEstMD
End If
liTotalCost = (CDbl([ActMDCost]) - liActMD)

SumSql = "SELECT SUM(Amount) AS SumOfAmount " & _
"FROM JONProjJoins s " & _
"WHERE s.ProjID = o.IDNmbr " & _
"GROUP BY s.ProjID"

strSQL5 = "UPDATE [ProjOverview] o " & _
"SET [Status] = 'Complete', " & _
"[Complete] = Now , " & _
"[TotalServicesAct] = [TotalServicesEst] " & _
"WHERE [Status] = 'Funded' " & _
"AND (" & liTotalCost & " > Nz(" & SumSql & ", 0 ) " & _
" OR " & liTotalCost & " > [TotalEstimate] )"
DoCmd.RunSQL strSQL5
``````
0

Author Commented:
Ok -- the where does return data.
0

Application Development ManagerCommented:
Make sure to place parens around the SumSql
``````SumSql = "( SELECT SUM(Amount) AS SumOfAmount " & _
"FROM JONProjJoins s " & _
"WHERE s.ProjID = o.IDNmbr " & _
"GROUP BY s.ProjID )"
``````
0

Author Commented:
Thank you!   I revisited the data --- good data combined with your code worked perfectly.   Thank you also for the clean formatting.  It is certainly much clearer and easier to read.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.