KimD2
asked on
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
ASKER
"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.
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
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
ASKER
Thank you but it is still not updating. Do you have any other suggestions?
In what way is it not updating ? Are you getting an error when you run the Update Query ?
ASKER
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.
ok, check your where clause, step through the code and see if the where returns data.
Example:
Open a SQL editor:
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])
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok -- the where does return data.
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 )"
ASKER
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.
Open in new window