How to do this calculation

[rickroller5]

Hi! I'm trying to implement a prepayment system for my customer orders database. I did this by having an Orders table where I can enter a customer's orders, as well as a Credits table where I can enter a customer's credit payments (a payment of $1000 to be used towards future orders, for example). I have managed to use a Union Query to combine the two into a Transactions query, which shows all orders and credit payments and displays credit payments as positive and orders as negative. This query is sorted by date, from oldest to newest.

For each customer, I need to be able to find out how much prepaid credit he or she has left to use. The way I see this happening is by having the database go down the Transactions query and keep a running total for the customer's remaining prepaid credit. However, I can't just add up all the credit payments and subtract all the orders, since this would ignore the fact that some orders did not come out of a customer's credit and was paid with cash instead (such as if the customer did not have any credit at the time). I think the running total should go something like this:



S_n+1 = max{0, S_n+ x}

where S_n is the running total and x is the next entry in the Transactions query for that customer. This way, if the customer runs out of credit, the running total remains at zero until he adds more credit.

I am relatively new to Access and I'm wondering if this type of calculation is possible. Will I need to use the Expression Builder or another way of doing it?

[burrina]

Sounds to me all you need to do is subtract the cash payment from the balance. Credit - Payment - Cash = Balance.?
If you are trying to get a running total, will this be on a form or a report, or query?

[June7]

Running sum not necessary just to determine a current balance.

Do aggregate query that sums the orders.

Do aggregate query that sums the credits.

Build query that joins both to the Customers table and construct calculated field that subtracts SumCredits from SumOrders.

[burrina]

I did not say it was necessary. The OP said he needed a running total, i.e. running sum.

rickroller5 Good Luck With Your Project!

[June7]

Didn't mean to suggest you did. Just pointing that out to OP because they expressed view that was the way to get current balance. If they really do want running balance that is different issue. Not easily done in query or on form.