Running Balance on Form

[Randy]

Access 2000. I need a running balance on my form that is specific to each
customer. I enter [debits] and [Credits] on my form "Accounts". I would
like a running balance. My table is "Account" with the fields of "Credits",
"Debits", "App_Id", (App_Id is my customer ID) and a PK of "AccountID"
What do I need to do? Thanks

 

[John Vinson]

Access 2000. I need a running balance on my form that is specific to each
customer. I enter [debits] and [Credits] on my form "Accounts". I would
like a running balance. My table is "Account" with the fields of "Credits",
"Debits", "App_Id", (App_Id is my customer ID) and a PK of "AccountID"
What do I need to do? Thanks

Is there some record within the table indicating the *sequence* of
entries? You can't have a running balance unless you have transactions
in some defined order. Do you have a transactiondate field?

There may be problems with your table design. If you have a PK of
AccountID, you can enter one and only one record for a given account,
and a "running sum" is impossible.

John W. Vinson[MVP]

 

[Randy]

I do have a date field of [DateIsssued] this is the transaction date. I
dont understand the problem with a PK of "AccountID" I have many multiple
records for the same customer, but different transaactions and dates. The
customers account number is [App_ID] not a primary key. I thought PK cannot
be duplicated. Randy

Access 2000. I need a running balance on my form that is specific to each
customer. I enter [debits] and [Credits] on my form "Accounts". I would
like a running balance. My table is "Account" with the fields of
"Credits",
"Debits", "App_Id", (App_Id is my customer ID) and a PK of "AccountID"
What do I need to do? Thanks

Is there some record within the table indicating the *sequence* of
entries? You can't have a running balance unless you have transactions
in some defined order. Do you have a transactiondate field?

There may be problems with your table design. If you have a PK of
AccountID, you can enter one and only one record for a given account,
and a "running sum" is impossible.

John W. Vinson[MVP]

 

[John Vinson]

I do have a date field of [DateIsssued] this is the transaction date. I
dont understand the problem with a PK of "AccountID" I have many multiple
records for the same customer, but different transaactions and dates. The
customers account number is [App_ID] not a primary key. I thought PK cannot
be duplicated. Randy

Probably just my misunderstanding of the meaning of AccountID - to me,
that sounds like the Primary Key of an Accounts table.

Try creating a Query with a calculated field:

RunningBal: DSum("[Credits]", "[Accounts]", "[App_ID] = " & [App_ID] &
" AND [DateIssued] <= #" & [DateIssued] & "#") - DSum("[Debits]",
"[Accounts]", "[App_ID] = " & [App_ID] & " AND [DateIssued] <= #" &
[DateIssued] & "#")

This will find all prior transactions for this customer (App_ID) for
dates prior to or equal to the date of the current record, sum all the
credits, and subtract the sum of all the debits.

John W. Vinson[MVP]

 

[Randy]

Thank you John, That was what I needed.

I do have a date field of [DateIsssued] this is the transaction date. I
dont understand the problem with a PK of "AccountID" I have many multiple
records for the same customer, but different transaactions and dates. The
customers account number is [App_ID] not a primary key. I thought PK
cannot
be duplicated. Randy

Probably just my misunderstanding of the meaning of AccountID - to me,
that sounds like the Primary Key of an Accounts table.

Try creating a Query with a calculated field:

RunningBal: DSum("[Credits]", "[Accounts]", "[App_ID] = " & [App_ID] &
" AND [DateIssued] <= #" & [DateIssued] & "#") - DSum("[Debits]",
"[Accounts]", "[App_ID] = " & [App_ID] & " AND [DateIssued] <= #" &
[DateIssued] & "#")

This will find all prior transactions for this customer (App_ID) for
dates prior to or equal to the date of the current record, sum all the
credits, and subtract the sum of all the debits.

John W. Vinson[MVP]