Running total

[lololthis]

Hi All,



I'm relatively new to Access and am trying to replace an Excel formula that I have with an Access equivalent. Here is my situation:

I have orders with a PO#, Part#, Qty and Due Date. I would like to keep one line per PO# and have a column which sums Qty for all orders for the part which have an earlier due date.

The idea is to have a column which shows how many units will be coming in before that order's due date. Any idea on how I can do this?

Thanks!

[khalid]

Hi,
This can be achieved by calculating the Qty for all the orders using a query with grouping on PO#, Part# and calculating the Qty field with sum aggregate function.

Here is how to Count data by using a query:

[lololthis]

The problem is that I only want to sum Qty when due date is < due date on that line

[khalid]

You can use the criteria in the query grid under the [due date] column to < [due date]
did you create a query to sum the Qty?

[lololthis]

I think that I didn't explain this well enough. Let's say that there are 10,000 orders for 4,000 different parts. a given part has 5 different orders of quantity 1 each. For the order with the earliest due date, the "previous order sum" column should be 0, the next order will have a 1, the following order a 2, etc. This should work even if the quantities are not 1 each.

Ideas?

[khalid]

no idea, may be not understood fully. You may be asking about the re-order level?