Solved

SQL SUM subquery

Posted on 2011-09-12
9
455 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
Comment Utility

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
Comment Utility
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
Comment Utility
Oh, and you are missing SELECT, as in "SELECT Sum(JONProjJoins.Amount)..."
0
 

Author Comment

by:KimD2
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"UPDATE" was changed to "UPDATE DISTINCTROW" to clear the error.   It is working properly now.  Thanks so much for your assistance!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now