Question How to make Initial Balance from Final Balance ?

[tivadarpreiss]

Hi,

I am trying to make a database that has to do a registration of a Current
Payment, and calculating the Final Balance (closing balance)

I have already made
-Payment table with Date, Person, Current Payment, Initial Balance (opening balance) and Final Balance (closing balance).
-Payment form Date, Person, Current Payment, Initial Balance (opening balance) and Final Balance (closing balance).

I managed to calculate the Final Balance automatically,

BUT

I am stuck with making from first -starting- operation's Final Balance the next operation's Initial Balance.

Can you help me? I goggled a lot without any conclusive result.

thx

 

[gemma-the-husky]

I normal circumstances you wouldn't.

The current balance is the sum of the invoice less the payments. You do not need to actually store the current balance.

 

[arnelgp]

On a normal GL you would have to save it in to another account number.

 

[HiTechCoach]

In a properly designed (normalized) database the initial balance and final balance would never be saved in a table. They would be calculated as needed. This is true for all databases not just Access.

You should have only: Payment table with Date, Person, Current Payment

To see why storing the calculated initial and final balance should be avoided try this:

enter in at least 3 payments for a person.

Reality check: Data entry mistakes do happen.

What happens is you edit the first or second payment record's payment amount? Is the stored initial and final balance still correct for all the following records?

Hi,

BUT

I am stuck with making from first -starting- operation's Final Balance the next operation's Initial Balance.

Can you help me? I goggled a lot without any conclusive result.

thx

To get the initial balance for each payment record you will need to use a sub query to sum all the previous record.

To get the final balance for each payment record you will sum all the payment records including the current payment.


See: Subquery basics (Click here) and scroll down to the section: Aggregation: Counts and totals