Beginning Balances

S

SFCS

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.
 
M

Marshall Barton

SFCS said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top