Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL SUM subquery

Posted on 2011-09-12
9
Medium Priority
?
467 Views
Last Modified: 2012-05-12
One of the update conditions is a summed field from a second table.  Running this code generates the error "At most one record can be returned from this subquery".  What is the correct way to find the sum of the related records for each line then use this sum in the update conditions?
The program is VB6/ MS Access 2003.  Thank you.    
strSQL4 = "Update [ProjOverview] Set [Status] = 'Complete', [Complete] = Now , [TotalServicesAct] = [TotalServicesEst] Where 
(([Status] = 'TEST') AND ([ActMDCost] > 0) 
AND (Sum(JONProjJoins.Amount) AS SumOfAmount FROM ProjOverview LEFT JOIN JONProjJoins ON ProjOverview.IDNmbr = JONProjJoins.ProjID GROUP BY JONProjJoins.ProjID <= 0))"
DoCmd.RunSQL strSQL4

Open in new window

0
Comment
Question by:KimD2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 36525114

Update [ProjOverview] Set [Status] = 'Complete', [Complete] = Now , [TotalServicesAct] = [TotalServicesEst]
Where (([Status] = 'TEST') AND ([ActMDCost] > 0)
AND
(Sum(JONProjJoins.Amount) AS SumOfAmount FROM ProjOverview LEFT JOIN JONProjJoins ON ProjOverview.IDNmbr = JONProjJoins.ProjID GROUP BY JONProjJoins.ProjID <= 0))

The "AND..." part, can you write it out?   You have a select stmt, so you probably want "AND ProjOverview.IDNmbr in (<you select stmt>)"  But you select stmt needs modified too.  Can you give an example of the results?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36525161
Without getting into the query further, it looks like you have a simple type-o.

strSQL4 = "Update [ProjOverview] Set [Status] = 'Complete', [Complete] = Now , [TotalServicesAct] = [TotalServicesEst] Where
(([Status] = 'TEST') AND ([ActMDCost] > 0)
AND (Sum(JONProjJoins.Amount) AS SumOfAmount FROM ProjOverview LEFT JOIN JONProjJoins ON ProjOverview.IDNmbr = JONProjJoins.ProjID GROUP BY JONProjJoins.ProjID <= 0))"
DoCmd.RunSQL strSQL4

Should be:

strSQL4 = "Update [ProjOverview] Set [Status] = 'Complete', [Complete] = Now , [TotalServicesAct] = [TotalServicesEst] Where
(([Status] = 'TEST') AND ([ActMDCost] > 0)
AND (Sum(JONProjJoins.Amount) AS SumOfAmount FROM ProjOverview LEFT JOIN JONProjJoins ON ProjOverview.IDNmbr = JONProjJoins.ProjID GROUP BY JONProjJoins.ProjID) <= 0)"
DoCmd.RunSQL strSQL4
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36525169
Oh, and you are missing SELECT, as in "SELECT Sum(JONProjJoins.Amount)..."
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:KimD2
ID: 36525286
thank you mwvisa -- those items are in the code.  I apologize for the error in my posting

developedtester-- Please let me know if this makes sense to you:
ProjOverview is a list of projects
JONProjJoins links the project table to a funding table

I need the total amount from JONProjJoins table for each record in the ProjOverview table
ex:
ProjOverview.ProjIDNmbr = 1
JONProjJoins.ProjID = 1, Amount =$1 (record 1)
JONProjJoins.ProjID = 1, Amount =$1 (record 2)
JONProjJoins.ProjID = 1, Amount =$1 (record 3)
Total Amount = $3

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36525324
Ah, I am sorry I see the question. Your approach may work just fine, you just have to make the query in the where correlated instead of another join.

Instead of:
Update [ProjOverview]
Set [Status] = 'Complete', [Complete] = Now , [TotalServicesAct] = [TotalServicesEst]
Where (([Status] = 'TEST') AND ([ActMDCost] > 0)
AND (Select Sum(JONProjJoins.Amount) AS SumOfAmount FROM ProjOverview LEFT JOIN JONProjJoins ON ProjOverview.IDNmbr = JONProjJoins.ProjID GROUP BY JONProjJoins.ProjID) <= 0)

Try:
Update [ProjOverview]
Set [Status] = 'Complete', [Complete] = Now , [TotalServicesAct] = [TotalServicesEst]
Where (([Status] = 'TEST') AND ([ActMDCost] > 0)
AND (Select Sum(JONProjJoins.Amount) AS SumOfAmount FROM JONProjJoins WHERE ProjOverview.IDNmbr = JONProjJoins.ProjID) <= 0)

You could also try using an UPDATE with JOIN, joining to a derived table like:
SELECT ProjID, SUM(Amount) AS SumOfAmount
FROM JONProjJoins
GROUP BY ProjID

Hope that helps!
0
 

Author Comment

by:KimD2
ID: 36525481
I tried but am unable to get it to work.  I do need to have the left join in the statement.  Only some records in ProjOverview table have corresponding records in JONProjJoins.  Could you elaborate on the UPDATE with JOIN code?  Thank you.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36525537
I missed the LEFT JOIN, the issue is that you are getting NULL for the rows that don't exist; therefore, Nz({query}, 0) is what you need.

Nz((Select Sum(JONProjJoins.Amount) AS SumOfAmount FROM JONProjJoins WHERE ProjOverview.IDNmbr = JONProjJoins.ProjID), 0) <= 0

For the UPDATE with JOIN, the syntax is shown in a3's article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html#c3551

You would use:
Update [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] = 'TEST') AND ([ActMDCost] > 0) AND (Nz(s.SumOfAmount, 0) <= 0))

Hope that helps!
0
 

Author Comment

by:KimD2
ID: 36528992
Thank you for your help.  I have a better understanding now but still cannot resolve the problem.  The code produces an "Operation must use an updateable query" error.  I do have full permissions to the MDB folder.  Any thoughts?
0
 

Author Closing Comment

by:KimD2
ID: 36529202
"UPDATE" was changed to "UPDATE DISTINCTROW" to clear the error.   It is working properly now.  Thanks so much for your assistance!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

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

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

Join & Ask a Question