Sub Queries in Ms Access

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
Hi

Kindly see how you can assist in another cumulative column under commission, I have done it for one column called sales  is now showing cumulative with two criteria. How do I add another column called commission as cumulative as well?

SELECT T1.IDLine, T1.Monthend, T1.ProductCode, T1.Qty, T1.Commision, (SELECT Sum(tblLinedetails.QTY) As Total
FROM tblLinedetails
WHERE  (tblLinedetails.Monthend <= T1.Monthend) AND tblLinedetails.ProductCode = T1.ProductCode) AS Total
FROM tblLinedetails AS T1;

Open in new window


Well Dale Fye advised clearly the problem of slowness with ELookups & Dsum , I now totally agree with him that the way forward is to settle on Subqueries.
Regards

Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Chris,

Rather than a subquery, you might try:

SELECT T1.IDLine, T1.Monthend, T1.ProductCode, T1.Qty, SUM(T2.Qty) as TotQty, T1.Commision, Sum(T2.Commission) as TotComm
FROM tblLinedetails as T1 LEFT JOIN tblLineDetails as T2
ON T1.ProductCode = T2.ProductCode
AND T1.MonthEnd >= T2.MonthEnd
GROUP BY T1.IDLine, T1.Monthend, T1.ProductCode, T1.Qty, T1.Commision

Open in new window

This assumes that you are trying to sum the quantity and commission for each month and product code to all previous Qty and Commission values, so those TotQty and TotComm fields would be running sums, by month.

Note the non-equi join:

T1.MonthEnd >= T2.MonthEnd

 in line 4 of the SQL.  This cannot be represented in the query grid, so I generally create the query with the join and then go to the SQL view to change that.
Now its asking the parameter value on T2 kindly see attached error!


Parameter-Value.png
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Sum(T2.Commission)
Isn't it supposed to be:
Sum(T2.Commision) ?

@Hankwembo:
typo mistake, this should be easy to spot.
I have amended Dale Fye's code to look like below:

SELECT T1.IDLine, T1.Monthend, T1.ProductCode, T1.Qty, T1.Commision, (SELECT Sum(tblLinedetails.QTY) As Total
FROM tblLinedetails
WHERE  (tblLinedetails.Monthend <= T1.Monthend) AND tblLinedetails.ProductCode = T1.ProductCode) AS Total, (SELECT Sum(tblLinedetails.[Commision]) As CumTotal
FROM tblLinedetails
WHERE  (tblLinedetails.Monthend <= T1.Monthend) AND tblLinedetails.ProductCode = T1.ProductCode) AS CoCum
FROM tblLinedetails AS T1;


This has worked properly

Thank you

Regards

Chris

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial