Solved

SQL SUM subquery

Posted on 2011-09-12
9
457 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
  • 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 59

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 59

Expert Comment

by:Kevin Cross
ID: 36525169
Oh, and you are missing SELECT, as in "SELECT Sum(JONProjJoins.Amount)..."
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 59

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 59

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

773 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