performing calculations

[Phil Boorman]

I would like to keep track of how much money each club member has on account
with our swim club. I created a table and a form with the following fields;

Account Check Number Date Description Deposit Withdrawal
Balance

The Account field uses a lookup function to get names from my swim families
table.

My problem is with the Balance. I have been playing with the Control
Source - Expression Builder for the Balance field in design View of the form
but with no luck.

Is there a formula I can use to keep a running balance?

Thanks for any help.

Phil

 

[John Vinson]

I would like to keep track of how much money each club member has on account
with our swim club. I created a table and a form with the following fields;

Account Check Number Date Description Deposit Withdrawal
Balance

The Account field uses a lookup function to get names from my swim families
table.

My problem is with the Balance. I have been playing with the Control
Source - Expression Builder for the Balance field in design View of the form
but with no luck.

Is there a formula I can use to keep a running balance?

Not in a Table; but such information should not be stored in a Table
in any case, since it's derived data which would become invalid if you
changed the amount of any deposit or withdrawal in any record in the
table (or added or deleted a transaction).

Instead, calculate this running balance on a Report: create a report
based on this table (minus the Balance field); put a textbox on the
report named Balance, with a Control Source of

=NZ([Deposit]) - NZ([Withdrawal])

and set its Running Sum property to Over Group.

Use the Report's Sorting and Grouping dialog to group by Account, and
sort by [Date], and you should get an up to date current balance.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Phil Boorman]

Thank you very much John. Your reply was extremely helpful.

Phil

I would like to keep track of how much money each club member has on
account
with our swim club. I created a table and a form with the following
fields;

Account Check Number Date Description Deposit Withdrawal
Balance

The Account field uses a lookup function to get names from my swim
families
table.

My problem is with the Balance. I have been playing with the Control
Source - Expression Builder for the Balance field in design View of the
form
but with no luck.

Is there a formula I can use to keep a running balance?

Not in a Table; but such information should not be stored in a Table
in any case, since it's derived data which would become invalid if you
changed the amount of any deposit or withdrawal in any record in the
table (or added or deleted a transaction).

Instead, calculate this running balance on a Report: create a report
based on this table (minus the Balance field); put a textbox on the
report named Balance, with a Control Source of

=NZ([Deposit]) - NZ([Withdrawal])

and set its Running Sum property to Over Group.

Use the Report's Sorting and Grouping dialog to group by Account, and
sort by [Date], and you should get an up to date current balance.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

I have been looking for a way to do this for a long time now! The only
resource I could find, including Microsoft help, was one which I had to pay
for. I somehow missed this conference and obviously wasn't entering the
right search terms in Microsoft help. You were very helpful! Thanks a lot!

Thank you very much John. Your reply was extremely helpful.

Phil

I would like to keep track of how much money each club member has on
account
with our swim club. I created a table and a form with the following
fields;

Account Check Number Date Description Deposit Withdrawal
Balance

The Account field uses a lookup function to get names from my swim
families
table.

My problem is with the Balance. I have been playing with the Control
Source - Expression Builder for the Balance field in design View of the
form
but with no luck.

Is there a formula I can use to keep a running balance?

Not in a Table; but such information should not be stored in a Table
in any case, since it's derived data which would become invalid if you
changed the amount of any deposit or withdrawal in any record in the
table (or added or deleted a transaction).

Instead, calculate this running balance on a Report: create a report
based on this table (minus the Balance field); put a textbox on the
report named Balance, with a Control Source of

=NZ([Deposit]) - NZ([Withdrawal])

and set its Running Sum property to Over Group.

Use the Report's Sorting and Grouping dialog to group by Account, and
sort by [Date], and you should get an up to date current balance.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps