Statements - Opening balance closing balance question.

[Paul H]

I have a transactions table and a balance table that look something
like this:

tblTransactions
TransactionID (PK Autonumber)
ClientID
TransactionDate
TransactionAmou nt (currency field, values must be >0)
CRDR (indicates whether the transaction is a (credit or debit)
StatementDate (Date stamp applied when the “Statement” report is run)

tblAccountBalan ce (no PK in this table)
ClientID
AccountBalance (currency field can be >0 or <0)
BalanceDate (Date stamp applied when the “Statement” report is run)

My goal is to have a report that shows the following:

Opening balance
Transactions for this period
Closing balance

When I run the report, I apply a date stamp to the “StatementDate”
field of each row in tblTransactions where the StatementDate is either
null or within the statement period. That works fine.

But I am getting mixed up with when to apply the date stamp to the
“BalanceDate” field in tblAccountBalan ce. Rather than go into detail
about all of the permutations that are sloshing around in my head, can
someone reassure me that my approach thus far is correct (or at least
workable) and give me a nudge in the right direction.

Thanks

Paul

[Fred Zuckerman]

Re: Statements - Opening balance closing balance question.

"Paul H" <google@comcraf t.freeserve.co. ukwrote in message
news:e503283f-1c7c-424a-a138-78206e880189@l6 4g2000hse.googl egroups.com...
I have a transactions table and a balance table that look something
like this:

tblTransactions
TransactionID (PK Autonumber)
ClientID
TransactionDate
TransactionAmou nt (currency field, values must be >0)
CRDR (indicates whether the transaction is a (credit or debit)
StatementDate (Date stamp applied when the “Statement” report is run)

tblAccountBalan ce (no PK in this table)
ClientID
AccountBalance (currency field can be >0 or <0)
BalanceDate (Date stamp applied when the “Statement” report is run)

My goal is to have a report that shows the following:

Opening balance
Transactions for this period
Closing balance

When I run the report, I apply a date stamp to the “StatementDate”
field of each row in tblTransactions where the StatementDate is either
null or within the statement period. That works fine.

But I am getting mixed up with when to apply the date stamp to the
“BalanceDate” field in tblAccountBalan ce. Rather than go into detail
about all of the permutations that are sloshing around in my head, can
someone reassure me that my approach thus far is correct (or at least
workable) and give me a nudge in the right direction.
Thanks
Paul

>>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>

Paul,
I think your tblAccountBalan ce may be redundant and a poor design idea. It
appears to be 100% build-able from tblTransactions . Thus, it should not be
needed. If you really want to use such a table then it shoulde be used
temporarily for a process or report and then deleted afterwards. The
AccountBalance field is merely the sum of TransactionAmou nt from
tblTransactions for records <= to the desired StatementDate.

You can create your desired statement report using just the tblTransaction
table. The report would have a filter set to the desired ClientID and
TransactionDate between the desired OpeningDate and the desired ClosingDate.
The report header would include a control for the OpeningBalance as:

= DSum("Transacti onAmount","tblT ransactions","C lientID=" & ClientID & " And
TransactionDate <#" & dtOpeningDate & "#")

The detail section of the report would list all of the transactions for the
client during the period.

Finally, the report footer will show the ClosingBalance with a control of:

= OpeningBalance + Sum(Transaction Amount)

Fred Zuckerman

[Paul H]

Re: Statements - Opening balance closing balance question.

On 7 May, 14:07, "Fred Zuckerman" <Zuckerm...@sbc global.netwrote :

"Paul H" <goo...@comcraf t.freeserve.co. ukwrote in message
>
news:e503283f-1c7c-424a-a138-78206e880189@l6 4g2000hse.googl egroups.com...
I have a transactions table and a balance table that look something
like this:
>
tblTransactions
TransactionID (PK Autonumber)
ClientID
TransactionDate
TransactionAmou nt (currency field, values must be >0)
CRDR (indicates whether the transaction is a (credit or debit)
StatementDate (Date stamp applied when the “Statement” report is run)
>
tblAccountBalan ce (no PK in this table)
ClientID
AccountBalance (currency field can be >0 or <0)
BalanceDate (Date stamp applied when the “Statement” report is run)
>
My goal is to have a report that shows the following:
>
Opening balance
Transactions for this period
Closing balance
>
When I run the report, I apply a date stamp to the “StatementDate”
field of each row in tblTransactions where the StatementDate is either
null or within the statement period. That works fine.
>
But I am getting mixed up with when to apply the date stamp to the
“BalanceDate” field in tblAccountBalan ce. Rather than go into detail
about all of the permutations that are sloshing around in my head, can
someone reassure me that my approach thus far is correct (or at least
workable) and give me a nudge in the right direction.
Thanks
Paul
>
>
>
Paul,
I think your tblAccountBalan ce may be redundant and a poor design idea. It
appears to be 100% build-able from tblTransactions . Thus, it should not be
needed. If you really want to use such a table then it shoulde be used
temporarily for a process or report and then deleted afterwards. The
AccountBalance field is merely the sum of TransactionAmou nt from
tblTransactions for records <= to the desired StatementDate.
>
You can create your desired statement report using just the tblTransaction
table. The report would have a filter set to the desired ClientID and
TransactionDate between the desired OpeningDate and the desired ClosingDate.
The report header would include a control for the OpeningBalance as:
>
= DSum("Transacti onAmount","tblT ransactions","C lientID=" & ClientID & " And
TransactionDate <#" & dtOpeningDate & "#")
>
The detail section of the report would list all of the transactions for the
client during the period.
>
Finally, the report footer will show the ClosingBalance with a control of:
>
 = OpeningBalance + Sum(Transaction Amount)
>
Fred Zuckerman

Thank you for your post Fred.

Two years from now I will have to sum two years worth of data to
arrive at the opening balance if I omit tblBalances. Surely
tblBalances is required to reduce overhead when I run the report?

Paul

[Roger]

Re: Statements - Opening balance closing balance question.

On May 7, 5:06 am, Paul H <goo...@comcraf t.freeserve.co. ukwrote:

I have a transactions table and a balance table that look something
like this:
>
tblTransactions
TransactionID (PK Autonumber)
ClientID
TransactionDate
TransactionAmou nt (currency field, values must be >0)
CRDR (indicates whether the transaction is a (credit or debit)
StatementDate (Date stamp applied when the “Statement” report is run)
>
tblAccountBalan ce (no PK in this table)
ClientID
AccountBalance (currency field can be >0 or <0)
BalanceDate (Date stamp applied when the “Statement” report is run)
>
My goal is to have a report that shows the following:
>
Opening balance
Transactions for this period
Closing balance
>
When I run the report, I apply a date stamp to the “StatementDate”
field of each row in tblTransactions where the StatementDate is either
null or within the statement period. That works fine.
>
But I am getting mixed up with when to apply the date stamp to the
“BalanceDate” field in tblAccountBalan ce. Rather than go into detail
about all of the permutations that are sloshing around in my head, can
someone reassure me that my approach thus far is correct (or at least
workable) and give me a nudge in the right direction.
>
Thanks
>
Paul

tblBalance is a good idea, allows you to archive transactions after a
period of time by updating the accountBalance with the value of the
archived transactions

but it is not a good design idea to use it as an accumulator of past
invocations of the report

I'm guessing your scenerio is
for clientId 1, the balance is 0
run report which marks some transactions ($1000) with a statement
date
update accountBalance to $1000, balanceDate is ????

one month later run report with $2000 of transactions and mark
another date
update accountBalance to $3000, balanceDate is ???

now what happens when the client wants a statement for the past 2
months ?
how do you rebuild the first statement is the account balance is no
longer 0 ?

now, if you use accountBalance as only the client opening balance,
you'd change the report's opening balance to be accountBalance +
sum(any transactions prior to the statement date), so
for clientId 1, the balance is 0 + 0
run report which marks some transactions ($1000) with a statement
date

one month later run report with $2000 of transactions and mark
another date
opening balance is 0 + 1000 (sum of prior transactions)

to rebuild first statement, opening balance is 0, you'd select all
transactions
where the statement date <= a 'reprint' date

ditto for a statement that spans multiple months

three years from now, you'd have a function that moves transactions to
an archive table, or deletes if you don't care to about the details
and updates the accountBalance to say $100,000
and sets the balanceDate to the archive date

now the next statement you create
for clientId 1, the balance is $100,000 + 0
run report which marks some transactions ($2500) with a statement
date

etc