Re: Eliminate zero values/pass calculated value to new calc field

Tech-Archive recommends: Fix windows errors by optimizing your registry

Sprinks wrote:

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

"Carla Gilless" wrote:

[...]

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.

In other words, each period's beginning balance should be the ending balance of the previous period, except for the very first one, which is a stored value?

OK, I agree with you on the first one. That one you probably do need to store, but not the others.

Or am I going about this all wrong? I would really appreciate some help on what should have been a very simple database, but has turned into a major frustration.

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.

Thanks much.
--
Carla

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 <vjohns@xxxxxxxxxxxxxxxxxx>
  Please feel free to quote anything I say here.
.

Relevant Pages

  • Re: Statements - Opening balance closing balance question.
    ... I have a transactions table and a balance table that look something ... StatementDate (Date stamp applied when the “Statement” report is run) ... field of each row in tblTransactions where the StatementDate is either ...
    (comp.databases.ms-access)
  • Re: Show all cumulative end of month quantites
    ... You could do this fairly easy using a running sum in a report. ... >I have a table that stores all inventory transactions. ...
    (microsoft.public.access.queries)
  • Re: Eliminate zero values/pass calculated value to new calc field
    ... go a simpler method of just doing a running sum, however I can't seem to get ... but every transaction after that is off because it uses the Beginning Balance ... I tried in the report to add an unbound text ... >>>starting value for the account. ...
    (microsoft.public.access.tablesdbdesign)
  • 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: 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)