Running sum query
Hello All,
The following code i use to add running sum to the query grid.
PDno is the autonumber, Qty is the number i want to accumulate.
But it dosnt work. Can you see what the problem is.
Please see the attachment
Total: (SELECT Sum([POrder RcvdDT].Qty) AS Total FROM [POrder RcvdDT]
WHERE [POrder RcvdDT].PDno <= T1.PDno)
Thanks in advance
Capture.PNG
The following code i use to add running sum to the query grid.
PDno is the autonumber, Qty is the number i want to accumulate.
But it dosnt work. Can you see what the problem is.
Please see the attachment
Total: (SELECT Sum([POrder RcvdDT].Qty) AS Total FROM [POrder RcvdDT]
WHERE [POrder RcvdDT].PDno <= T1.PDno)
Thanks in advance
Capture.PNG
If this is for a report, it is far more efficient to create the running sum in the report. Choose the OverAll or OverGroup option depending on whether or not you have intermediate groups.
Is there other criteria in the main query? PDno is not sequential so there's lots of records "missing". You may need to add additional criteria to the Total query.
Also, I'm pretty sure that running a separate query for each row isn't the best technique if you decide that you must do this in a query. I would use a non equi-join rather than a sub query. That lets the query engin determine the most efficient method of summing the data.
Is there other criteria in the main query? PDno is not sequential so there's lots of records "missing". You may need to add additional criteria to the Total query.
Also, I'm pretty sure that running a separate query for each row isn't the best technique if you decide that you must do this in a query. I would use a non equi-join rather than a sub query. That lets the query engin determine the most efficient method of summing the data.
AS T1 needs to be the alias for the table in the main SELECT, not in the sub select. Your statement was correct as it stood. You just need to alias the table in the main select.
Jim.
Jim.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It did work fine.
The reason I didn't close the question, because I was waiting an answer for my follow up questions,
in the meantime I already figured it out.
Thanks
The reason I didn't close the question, because I was waiting an answer for my follow up questions,
in the meantime I already figured it out.
Thanks
WHERE [POrder RcvdDT].PDno <= T1.PDno)