Running sum query syntax problem

[pb21]

I have the following sql that returns my sample records:
SELECT QryAllEnrolments.[Person Id], QryAllEnrolments.[Course Code], QryAllEnrolments.[Course Name], QryAllEnrolments.GLH, QryAllEnrolments.Date
FROM QryAllEnrolments LEFT JOIN QryWithdrawls ON (QryAllEnrolments.[Person Id] = QryWithdrawls.[Person Id]) AND (QryAllEnrolments.[Course Code] = QryWithdrawls.[Course Code])
WHERE (((QryAllEnrolments.[Person Id])=1950165) AND ((QryWithdrawls.[Person Id]) Is Null));


and wrote the following hoping it would return a running sum of the guided learning hours (GLH) utilising the above:

SELECT Pro.[Date], Pro.[Person Id], Pro.[Course Code], Pro.[Course Name], Pro.[GLH],
(Select Sum(Pro1.[GLH] FROM [QryAllEnrolments Without Matching QryWithdrawls] Pro1 WHERE Pro1.[Date]<=Pro.[Date]) AS [Running Total], Pro.[Date]
FROM [QryAllEnrolments Without Matching QryWithdrawls] AS Pro
ORDER BY Pro.[Date]

It squeels of a syntax error at the inner select sum clause.

your assistance to finish this off would be greatly appreciated.

regards
Peter

 

[aqif]

Hi

Just by looking at it I can see that you are missing a bracket after sum in the inner select query. Try this

SELECT Pro.[Date], Pro.[Person Id], Pro.[Course Code], Pro.[Course Name], Pro.[GLH],
(Select Sum(Pro1.[GLH]) FROM [QryAllEnrolments Without Matching QryWithdrawls] Pro1 WHERE Pro1.[Date]<=Pro.[Date]) AS [Running Total], Pro.[Date]
FROM [QryAllEnrolments Without Matching QryWithdrawls] AS Pro
ORDER BY Pro.[Date]

Hope it helps
Aqif

 

[pb21]

running sum problems

It worked in a fashion but I wonder if their is any other way that does not utilise the date as where too enrollments appear on the same date it sums it all rather than speparate within the date. so if course 1 is 5 and course two 6 it returns 11 for both rows for the same date.

I think its almost there. if it could just sum up the row before it would be great.

regards

 

[aqif]

Do you an autonumber field in the tavble. If not then create autonumber field first and then instead of DateField<=DateField use Autonumber<=Autonumber.

Good luck!

 

[pb21]

Running sum within group

I finally have the running sum working based on autonumber. I have parameterised by learner ref and that works fine if I enter the learner Ref. How do I now run the report across all learners with the running sum for each learner and not cross learner?

the sql so far:

SELECT Pro.Date, Pro.ID, Pro.[Person Id], Pro.[Course Code], Pro.[Course Name], Pro.GLH, (Select Sum(Pro1.[GLH]) FROM [QryAllEnrolmentsWithoutMatchingQryWithdrawls] Pro1 WHERE Pro1.[ID]<=Pro.[ID]) AS [Running Total], Pro.UpliftCode, [Running Total]/2 AS [50%], IIf([UpliftCode]="99","No, Uplift turned off",IIf([UpliftCode]="1","No, as Postcode Uplift",IIf([Running Non BS Total]>[50%],"Yes","No, as more BS Hours than 50% GLH Threshold"))) AS [Rule Broken], (Select Sum(Pro1.[BS Hours]) FROM [QryAllEnrolmentsWithoutMatchingQryWithdrawls] Pro1 WHERE Pro1.[ID]<=Pro.[ID]) AS [Running BS Total], (Select Sum(Pro1.[Non BS Hours]) FROM [QryAllEnrolmentsWithoutMatchingQryWithdrawls] Pro1 WHERE Pro1.[ID]<=Pro.[ID]) AS [Running Non BS Total]
FROM QryAllEnrolmentsWithoutMatchingQryWithdrawls AS Pro
ORDER BY Pro.ID;

I can see that if I use a report it will do it but I wanted to do this in a query.


regards in advance

 

[pb21]

running sum in report; how to filter on text box

I have followed the advice and created a report that performs the running sum. I had to create some other text boxes on the report that calculate other values. One calculated textbox returns "yes" or "no" depending if a rule is broken. I would like to filter on "Yes" value from that calculated text box. When I add this in the filter property ie [TextboxX] ="Yes". it treats it as a parameter which is not what I wanted. How do I apply the filter.

Regards