Solved

SQL SUM subquery

Posted on 2011-09-12
9
462 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

691 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