Beginning Balances

[Guest]

We are setting up a database to recreate bank account information for
multiple accounts. We want to be able to produce reports queried by our
choice of time period that show a beginning balance for that time period,
activity for that time period and the ending balance for that time period.
We may want to produce one report for all accounts showing this information
for a one month period, one quarter period, one year period, or life to date.
We may also want a report for each seperate account showing this information.

How do you make access calculate an ending balance for any time period and
carry it to be the beginning balance for the next time period, for any choice
of time periods, for all individual accounts in the database or does each
account need to be in a seperate database.

 

[Marshall Barton]

We are setting up a database to recreate bank account information for
multiple accounts. We want to be able to produce reports queried by our
choice of time period that show a beginning balance for that time period,
activity for that time period and the ending balance for that time period.
We may want to produce one report for all accounts showing this information
for a one month period, one quarter period, one year period, or life to date.
We may also want a report for each seperate account showing this information.

How do you make access calculate an ending balance for any time period and
carry it to be the beginning balance for the next time period, for any choice
of time periods, for all individual accounts in the database or does each
account need to be in a seperate database.

For a report, the key is to first create a query that can be
used as the report's RecordSource. In this case, a simple
minded approach would be a query like:

SELECT Account, TransDate, TransAmt,
DSum("TransAmt","Tranactions",
"TransDate<" & [Start Date]) As StartBalance
. . .
FROM Tranactions
WHERE TransDate Between [Start Date] And [End Date]

It would be better to use text box's on a form instead of
prompt parameters.

The DSum may be unacceptably slow. If so, it might(?) be
better to use a separate query to calculate the start
balance and join that to the table.