Re: Statements - Opening balance closing balance question.

On 7 May, 14:07, "Fred Zuckerman" <Zuckerm...@xxxxxxxxxxxxx> wrote:
"Paul H" <goo...@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message

news:e503283f-1c7c-424a-a138-78206e880189@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a transactions table and a balance table that look something
like this:

tblTransactions
TransactionID (PK Autonumber)
ClientID
TransactionDate
TransactionAmount (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)

tblAccountBalance (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 tblAccountBalance. 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 tblAccountBalance 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 TransactionAmount 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("TransactionAmount","tblTransactions","ClientID=" & 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(TransactionAmount)

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
.

Relevant Pages

  • Re: Eliminate zero values/pass calculated value to new calc field
    ... In my report, I have a Calculated field that sums Beg.Balance and Deposit Amt, less fee amt and/or check amt. ... But then each successive period Ending Balance is off, because the calc still uses the original Beg.Balance amt. ... If there's too much detail here, you might at least consider using the "Running Sum" feature of an Access Report, though you can't do any calculations using it. ... The Queries listed here form the basis for a Report that lists individual transactions by date, running balance for the date, and ending balance. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Calculate beginning balance, but print details on report
    ... beginning balance based on all transactions prior to the beginning date. ... The check book report can be run for: ... it needs to calculate the beginning balance by simply ... Then the report needs to print the checks and deposit for the ...
    (microsoft.public.access.reports)
  • Re: Statements - Opening balance closing balance question.
    ... I have a transactions table and a balance table that look something ... report is run) ... field of each row in tblTransactions where the StatementDate is either ...
    (comp.databases.ms-access)
  • Re: Need data that it seems can only be created with a report
    ... balance for the report as the sum of previous transactions, ... By placing that into a text box in the Customer ID Header section, ... this transaction report will give you the new totals. ... transactions so that a user cannot alter anything on any ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Finding the last entry in a column based on criteria
    ... I use the sheet to input future known transactions - some of them have known ... Where column C is the cleared column and column D is the balance column. ... I need it to calculate the last cleared balance entry and ignore anything ... Now read Excel help on the LOOKUP function. ...
    (microsoft.public.excel.worksheet.functions)