On 7 May, 14:07, "Fred Zuckerman" <Zuckerm...@[EMAIL PROTECTED]
> wrote:
> "Paul H" <goo...@[EMAIL PROTECTED]
> wrote in message
>
>
news:e503283f-1c7c-424a-a138-78206e880189@[EMAIL PROTECTED]
> 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 =93Statement=94 re****t is
run)
>
> tblAccountBalance (no PK in this table)
> ClientID
> AccountBalance (currency field can be >0 or <0)
> BalanceDate (Date stamp applied when the =93Statement=94 re****t is run)
>
> My goal is to have a re****t that shows the following:
>
> Opening balance
> Transactions for this period
> Closing balance
>
> When I run the re****t, I apply a date stamp to the =93StatementDate=94
> 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
> =93BalanceDate=94 field in tblAccountBalance. Rather than go into detail
> about all of the permutations that are slo****ng around in my head, can
> someone reassure me that my approach thus far is correct (or at least
> workable) and give me a ****ge 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
> tem****arily for a process or re****t and then deleted afterwards. The
> AccountBalance field is merely the sum of TransactionAmount from
> tblTransactions for records <=3D to the desired StatementDate.
>
> You can create your desired statement re****t using just the
tblTransaction=
> table. The re****t would have a filter set to the desired ClientID and
> TransactionDate between the desired OpeningDate and the desired
ClosingDat=
e.
> The re****t header would include a control for the OpeningBalance as:
>
> =3D DSum("TransactionAmount","tblTransactions","ClientID=3D" & ClientID
& =
" And
> TransactionDate<#" & dtOpeningDate & "#")
>
> The detail section of the re****t would list all of the transactions for
th=
e
> client during the period.
>
> Finally, the re****t footer will show the ClosingBalance with a control
of:=
>
> =A0=3D 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 re****t?
Paul
|
