Solved

# SQL UPDATE query based on calculations

Posted on 2011-09-13
364 Views
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
Question by:KimD2
• 5
• 5

LVL 4

Expert Comment

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 Comment

"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

LVL 14

Expert Comment

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 Comment

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

LVL 14

Expert Comment

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

Author Comment

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

LVL 14

Expert Comment

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

LVL 14

Accepted Solution

Brook Braswell earned 500 total points
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 Comment

Ok -- the where does return data.
0

LVL 14

Expert Comment

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 Closing Comment

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

## Join & Write a Comment Already a member? Login.

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

#### 744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!