Sprinks said:
You could certainly write an Update query to change the balance every month,
but I would get an answer from one of the MVPs with more experience in
transactional applications than me.
In my database that I use for my personal accounts, I do something of
this nature, but not every month. Instead, I have a Table in which (for
each account) I specify a date and a balance as of the end of the
specified day; running balances are calculated from that day forward.
[...]
Hope that helps.
Sprinks
:
[...]
2nd question is more complex. I have a beginning balance in a table as my
starting value for the account. In my report (based on detail query), I have
a Calculated field (EndBal)that sums Beg.Balance and Deposit Amt, less fee
amt and/or check amt. That works fine for the first statement period (say
1/1/03-1/3/03) defined through a report form where user enters the period.
But then each successive period Ending Balance is off, because the calc still
uses the original Beg.Balance amt. How do I store the EndBal value
cumulatively?
I suggest that trying to "store" that value cumulatively, when it's
something that can be calculated, is not a good idea, even though it is
possible to do that.
OK, I agree with you on the first one. That one you probably do need to
store, but not the others.
Because of the philosophy on which an RDBMS such as Access is based,
running totals can be a bit of a problem, but not insurmountable. For
example, you can design a Report which will calculate them.
What follows may be a bit involved (sorry), but it's extracted from my
database, which works dependably for me, and from which you may be able
to copy parts that will help you. (They won't work without change,
since you don't have the same Tables that I do.) 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.
Except for that starting balance, I do not store any (running or
otherwise) balances in any Table -- I compute them all. Remember that
they're computable, and if they don't match the bank statement, that's a
clue to where there might be mistakes, being able to find which is a
major benefit of maintaining a database.
Anyway, I have Queries which calculate the net effect of transactions
for a given date. Withdrawals are represented by negative numbers,
deposits by positive numbers. Dataset names beginning with "Q_" are
Queries; any others are Tables.
FWIW, here's an example, though you probably can't use it in this form,
as it cites Tables and Queries you don't have, but it may give you an
idea of how I do it. The Queries listed here (plus a few others) form
the basis for a Report that lists individual transactions by date,
running balance for the date, and ending balance.
I'll start with a Query that, for a given account, lists by date the net
change for that account's balance during that date:
[Q_Register_DailySum_WithReg]
SELECT T_Parameters.Account,
IIf(IsNull([Full Register]![Date issued]),
[Full Statement]![Date],[Full Register]![Date issued]) AS SortDate,
Sum(IIf(IsNull([Full Statement]![Amount]),
[Full Register]![AmtReg],[Full Statement]![Amount])) AS SumOfAmt
FROM (Accounts INNER JOIN ([Full Statement]
RIGHT JOIN [Full Register]
ON [Full Statement].ID = [Full Register].StatementID)
ON Accounts.ID = [Full Register].AccountSymbol)
INNER JOIN T_Parameters
ON Accounts.ID = T_Parameters.Account
WHERE ((([Full Register].SplitID) Is Null)
AND ((IIf(IsNull([Full Register]![Date issued]),
[Full Statement]![Date],
[Full Register]![Date issued]))>[Accounts]![RegStartDate]))
GROUP BY T_Parameters.Account,
IIf(IsNull([Full Register]![Date issued]),
[Full Statement]![Date],[Full Register]![Date issued])
ORDER BY IIf(IsNull([Full Register]![Date issued]),
[Full Statement]![Date],[Full Register]![Date issued]);
[T_Parameters].[Account] is a number specifying which of several
accounts I'm looking at, and I set that before runnning a Report.
Having calculated the daily changes (actually, this lists only the
changes for the days on which changes occurred), I combine that with the
starting balance I mentioned earlier, which is reported by
[Q_Register_Starting]. (Query [Q_Register_DailySum_NoReg] is similar to
[Q_Register_DailySum_WithReg], except that it includes records present
in the bank statement for which the register entry is missing, and much
of the time it's empty.) So the following Query includes a starting
balance, daily transactions for register (my records) entries, and
statement (bank records) entries that I'd failed to record in my
register (such as service charges):
[Q_Register_DailySum_Both]
SELECT * FROM Q_Register_DailySum_NoReg
UNION SELECT * FROM Q_Register_DailySum_WithReg
UNION SELECT [Account], [Date issued], AmtReg
FROM Q_Register_Starting
ORDER BY SortDate;
Since the same day may appear in more than one of these three datasets,
I combine them into one list of account ID, date, and net change for
that day:
[Q_Register_DailySum]
SELECT Q_Register_DailySum_Both.Account,
Q_Register_DailySum_Both.SortDate AS DailySumDay,
Sum(Q_Register_DailySum_Both.SumOfAmt) AS DailySumAmt
FROM Q_Register_DailySum_Both
GROUP BY Q_Register_DailySum_Both.Account,
Q_Register_DailySum_Both.SortDate;
I also have a Query that lists all the fields in all the transactions in
the specified account during a period I specify. If you didn't want
running account balances, this would be the basis for a complete Report.
The datasets on which this is based include individual transactions,
but no daily sums, and I'm not including their details here.
[Q_Register_Both_NoSums]
SELECT ALL * from Q_Register_WithReg
UNION SELECT ALL * FROM Q_Register_No_Reg
UNION SELECT ALL * FROM Q_Register_Starting
ORDER BY SortDate;
At this point, we can combine the full records from
[Q_Register_Both_NoSums] with the daily totals from
[Q_Register_DailySum] to get a dataset in which, for a given date, every
transaction bearing that date has the same daily total attached to it.
It may look redundant, but this daily net change to the running total is
just being calculated; it's not stored in any Table.
[Q_Register]
SELECT Accounts!Name AS AcctName,
Q_Register_Both_NoSums.*,
Q_Register_DailySum.DailySumAmt
FROM (Q_Register_Both_NoSums
LEFT JOIN Q_Register_DailySum
ON Q_Register_Both_NoSums.SortDate
= Q_Register_DailySum.DailySumDay)
LEFT JOIN Accounts
ON Q_Register_DailySum.Account = Accounts.ID
ORDER BY Q_Register_Both_NoSums.SortDate;
This is the basis for my Report, which is grouped by day, and each
record for the day includes the field identifying that day's net change
-- same value for every record that day. The group footer in the Report
for each day includes the value of [Q_Register_DailySum.DailySumAmt],
whose Running Sum property is set to "Over All". What this does is to
add each day's net change to the running total of the previous day that
appears in the dataset. Since the first record in the Report contains
the starting balance, this is an accurate running sum of the account
balance.
Notice that I used two methods for calculating sums. Within a date, I
did that using a Query (well, actually a set of Queries). Over a period
of months, I did that via a Running Sum in a Report.
Doing a running sum is somewhat easier in Excel, since there's an
inherent order to the records in an Excel table, and you can easily find
the previous record -- it's the one in the row directly above the
current row. In Access, you need to identify exactly which set of
records you're using as a basis for calculating, so it requires a bit
more effort to specify that. You might consider a Report to be an
exception to that, however, as by the time the records wind up in a
Report, their order is fixed... so it's possible to calculate and
display a Running Sum there (but not in a Query). I can't think of a
reason that it shouldn't be possible to put a Running Sum into a Query,
but AFAIAA Access doesn't allow that, perhaps because it would be
misleading and cause more trouble than it'd be worth.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
