Microsoft Access Query Calculation

  • Thread starter Calculating Runing Sum in a Queries
  • Start date
C

Calculating Runing Sum in a Queries

Query Example

Please can anyone tell me the correct expression to get Pay Field to
calculate running sum for me as below?

SecID Paid Rate TotalHours Total Pay
1 Yes £6.50 8.5 £55.25 £110.50
2 No £6.50 8.5 £55.25 £110.50
4 Yes £6.50 7 £45.50 £91.00

At the above Query Table I want formula to calculate and sum only the total
value with Yes in Paid Field
The above illustrate my problems

As Above the correct value suppose to be $201.50

I use the below expression on the query grid for Paid Field

Pay: [SeqQuery].[Total]+[Total]

Please reply to [email protected]

Many Thanks In advance
 
M

Michel Walsh

Hi,


SELECT a.SecID, LAST(a.Paid), LAST(a.Rate), LAST(a.TotalHours),
SUM(b.TotalHours)
FROM myTable As a INNER JOIN myTable As b
ON a.SecID >= b.SecID
GROUP BY a.SecID



Hoping it may help
Vanderghast, Access MVP



"Calculating Runing Sum in a Queries" <Calculating Runing Sum in a
[email protected]> wrote in message
news:[email protected]...
 
G

Gary Walter

Query Example
Please can anyone tell me the correct expression to get Pay Field to
calculate running sum for me as below?

SecID Paid Rate TotalHours Total Pay
1 Yes £6.50 8.5 £55.25 £110.50
2 No £6.50 8.5 £55.25 £110.50
4 Yes £6.50 7 £45.50 £91.00

At the above Query Table I want formula to calculate and sum only the total
value with Yes in Paid Field
The above illustrate my problems

As Above the correct value suppose to be $201.50

I use the below expression on the query grid for Paid Field

Pay: [SeqQuery].[Total]+[Total]
Click to expand...
Hi,

I might not understand completely, but....

if I had a table "tblkujo"

SecID Paid Rate TotalHours
1 Yes $6.50 8.5
2 No $6.50 8.5
4 Yes $6.50 7


and created query "qrykujo"

SELECT
t.SecID,
t.Paid,
t.Rate,
t.TotalHours,
[Rate]*[TotalHours] AS TotalPay,
IIf([Paid]=-1,[Rate]*[TotalHours],0) AS TotalPaid
FROM tblkujo AS t;

producing:

SecID Paid Rate TotalHours TotalPay TotalPaid
1 Yes $6.50 8.5 55.25 55.25
2 No $6.50 8.5 55.25 0
4 Yes $6.50 7 45.5 45.5


then the following query would give 2 running sums
(using form Michel suggested):

SELECT
a.SecID,
LAST(a.Paid) AS Paid,
LAST(a.Rate) AS Rate,
LAST(a.TotalHours) AS TotalHours,
CCur(LAST(a.TotalPay)) AS TotalPay,
CCur(SUM(b.TotalPay)) AS RunningTotalPay,
CCur(SUM(b.TotalPaid)) AS RunningTotalPaid
FROM qrykujo AS a INNER JOIN qrykujo AS b
ON a.SecID >= b.SecID
GROUP BY a.SecID;

producing:

SecID Paid Rate TotalHours TotalPay RunningTotalPay RunningTotalPaid
1 -1 $6.50 8.5 $55.25 $55.25 $55.25
2 0 $6.50 8.5 $55.25 $110.50 $55.25
4 -1 $6.50 7 $45.50 $156.00 $100.75
 
Top