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

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 :)

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

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

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