Running Totals

[Guest]

i have an Access 2003 qry "qryP11"

Payment ID Employee ID Week Number Pay Pay to date
1 4 1 100
100
2 4 2 100
200
3 4 3 250
450

im trying to work out the correct expression to give me the pay to date
values, a running sum. Can someone point me in the right direction please.

 

[Guest]

Hi

It may be a better idea to create a report from the query and then use a
running sum in a unbound box to produce the desired result.

Hope this helps

 

[Guest]

thanks for the reply but im not using a report, just qry

 

[Guest]

No worries you can create a standard module and refer to this is the query.

Check out this link for details

http://support.microsoft.com/?kbid=205183

Good luck

 

[Neil Sunderland]

im trying to work out the correct expression to give me the pay to date
values, a running sum. Can someone point me in the right direction please.

You need to join the table to itself, and get the total pay for each
employee for all the weeks that is less than or equal to the current
record.

SELECT
A.Payment_ID, A.Employee_ID, A.Week_Number, A.Pay,
(SELECT
Sum(B.Pay)
FROM
Payments AS B
WHERE
A.Employee_ID = B.Employee_ID AND
A.Week_Number >= B.Week_Number) AS Pay_To_Date
FROM
Payments AS A

Please note that I've assumed the table you're pulling the data from
is called 'Payments', and I've also changed all the spaces in your
field names to underscores, as I don't like typing all those square
brackets

 

[Marshall Barton]

i have an Access 2003 qry "qryP11"

Payment ID Employee ID Week Number Pay Pay to date
1 4 1 100
100
2 4 2 100
200
3 4 3 250
450

im trying to work out the correct expression to give me the pay to date
values, a running sum.

Use a subquery:

SELECT T.[Payment ID], T.[Employee ID],
T.[Week Number], T.Pay,
(SELECT Sum(X.Pay)
FROM table As X
WHERE X.[Employee ID] = T.[Employee ID]
And X.[Week Number] <= T.[Week Number]
) As [Pay to Date]
FROM table As T

 

[Michel Walsh]

Hi,

You already got answers with a subquery, I would personally use a join:


SELECT LAST(T.[Payment ID]), T.[Employee ID], T.[Week Number], LAST(T.Pay),
SUM(X.Pay)
FROM table AS t INNER JOIN table AS x
ON X.[Employee ID] = T.[Employee ID] And X.[Week Number] <= T.[Week
Number]
GROUP BY T.[Employee ID], T.[Week Number]



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

i've found the solution, it is

' Find the record that matches the control.
Dim rs As Object

Set rs = Form_sbfrmEmployees.sbfrmEmployeesDetails.Form.RecordsetClone
rs.FindFirst "[Employee ID] = " & Str(Nz(Me![EmployeeID], 0))
If Not rs.EOF Then
Form_sbfrmEmployees.sbfrmEmployeesDetails.Form.Bookmark = rs.Bookmark