Problem

[securityman]

I have a form to keep up with the remaining balance of a purchase order. The PO has a beginning balance of say $25,000. I receive invoices for this PO and I want to subtract the amount of the invoice from the balance of the PO. The first invoice amount I enter will be subtracted from the beginning balance of the PO and then the following invoice amounts will be subtracted from the remaining balance.
My table is set up with PON, INV,AMOUNT, BALANCE. On the form I have the same. I want the BALANCE to be figured as soon as I enter the AMOUNT. How would I construct a formula or function to do this. ($25000-Amount=Remaining Amount) Then (Remaining Amount - the next Invoice Amount= Remaining Amount).

Thanks

 

[Fuga]

Sounds to me that the balance is a calculated field. You don´t need to store that value.

I would set it up like this:

TBL 1

Transaction time/date (date)
Transaction amount (double)

+ whatever info you want about the invoices


To find the balance, you use a query.


Fuga.

 

[securityman]

So how would the query be set up? I am just being to use Access and am having a hard time uderstanding some of the finer details of it.

Thanks

 

[Fuga]

If you are in design view when creating a query, you click the summation button up in the menues. This will give you some alternatives. Group by is default I think. Change it to "sum" for your amount field.

If you choose view->SQL, this is a simplified version of what you want:

SELECT Sum(yourtable.amountfield) AS SumOfamountfield
FROM yourtable;

Fuga.