Solved

SQL SUM subquery

Posted on 2011-09-12
9
461 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 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