# Access 2010 Rolling Deduction or Negative Sum?

Working with a form and subform, there are examples of calculating the sum of the records on the subform and displaying the total on the form. Is it possible to have a rolling deduction? For example; 75 units were completed on an order of 100, 50 last week, 25 so far this week, with 25 left for next week.

Form: 75 units completed
Date Completed, Build Qty, Qty completed, Qty remaining
subform:               last week,              50,                      50,             50
this week,              25,                      25,             25
next week,             25,                         0,             25

As the form units completed increases, can the subform qty completed and qty remaining fields update also?
SOLUTION
Rey Obrero (Capricorn1)

membership
Create an account to see this answer
Signing up is free. No credit card required.

membership
Create an account to see this answer
Signing up is free. No credit card required.

No, I have not built any query for the subform. The subform is currently connected to a table that has Build Date and Build Qty. The form is in datasheet view and expanding the subform shows its datasheet view.

Form: OrderDate, OrderID, PartID, OrderQty, QtyComplete

subform: BuildDate, BuildQty
is Date Completed or rather Week Completed derived from BuildDate?

How about Qty completed, is this field also in the subform?
> .. an order of 100, 50 last week, 25 so far this week, with 25 left for next week.

What if:

an order of 100, 10 two weeks ago, 40 last week, 25 so far this week, with 25 left for next week.

Or would you prefer ther rows fixed:

Previous weeks
This week
Next weeks

/gustav

Here is a link to a support article that I used to create a function that calculates the grouped running sum in a query. I modified the function to ask for the order quantity and then have the function start with the order quantity and subtract the running sum.

http://support.microsoft.com/KB/205183