Eliminate zero values/pass calculated value to new calc field

[Guest]

I have what seems two pretty basic questions, but they're driving me nuts. I
have created a simple bank account database, with 3 basic
activities--deposits, fees and checks. First question is how do I eliminate
the 0 values showing up on reports and datasheets on those transaction dates
when only a deposit occurred, or only a fee or a check? Report currently
grouped by transaction date, will show a 0 value in defined fields DepAmt
(Currency), DepType (lookup to Deptype table) if only a fee amt and fee type
is entered for that date. Same happens with only a deposit or check on any
given transaction date.

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? 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? 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. Thanks much.

 

[Guest]

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.

Regarding the first question--open the report's underlying query, and create
a calculated field for the ones that might contain a zero value. For
example, for a field named Amount:

FormattedAmount: IIf(Nz([Amount])=0,"",Format([Amount],"Currency"))

Change the ControlSource of the report control to this calculated field. If
the field is Null or equal to zero, the calculated field will be a blank
string, otherwise it will print the currency-formatted amount.

Hope that helps.
Sprinks

 

[Vincent Johns]

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.

 

[Guest]

Thanks, Sprinks. Your formatted cells did the trick. I'm still looking for
a simple answer to my calculation question, but am going to try an update
query as you suggested.
--
Carla

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.

Regarding the first question--open the report's underlying query, and create
a calculated field for the ones that might contain a zero value. For
example, for a field named Amount:

FormattedAmount: IIf(Nz([Amount])=0,"",Format([Amount],"Currency"))

Change the ControlSource of the report control to this calculated field. If
the field is Null or equal to zero, the calculated field will be a blank
string, otherwise it will print the currency-formatted amount.

Hope that helps.
Sprinks

I have what seems two pretty basic questions, but they're driving me nuts. I
have created a simple bank account database, with 3 basic
activities--deposits, fees and checks. First question is how do I eliminate
the 0 values showing up on reports and datasheets on those transaction dates
when only a deposit occurred, or only a fee or a check? Report currently
grouped by transaction date, will show a 0 value in defined fields DepAmt
(Currency), DepType (lookup to Deptype table) if only a fee amt and fee type
is entered for that date. Same happens with only a deposit or check on any
given transaction date.

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? 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? 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. Thanks much.

 

[Guest]

Thanks for your suggestion. I tried to reply earlier, but it went awry. I
agree that trying to store the cumulative end balance seems too complicated
and I'm not sure I have the skills to adapt your methods. I would prefer to
go a simpler method of just doing a running sum, however I can't seem to get
that to work either.
In my underlying query, I've added an Expression cell SubTot:=Beginning
Balance+DepAmt-Fee Amt-Ck Amt. This works fine for the first transaction,
but every transaction after that is off because it uses the Beginning Balance
figure in the expression. So, I tried in the report to add an unbound text
box named EndBal with the following calc:End Bal: [SubTot]+Sum([Deposit
Amount]-[Fee Amount]-[Disbursement Amount]), running sum over all. Again,
this works fine for the first report period (1st month), but every one after
that is off again. So, how do I create a calculation for a running sum that
doesn't keep referring to the original beginning balance? Thanks very much
for the help.

--
Carla

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.

 

[Vincent Johns]

Thanks for your suggestion. I tried to reply earlier, but it went awry. I
agree that trying to store the cumulative end balance seems too complicated
and I'm not sure I have the skills to adapt your methods. I would prefer to
go a simpler method of just doing a running sum, however I can't seem to get
that to work either.

In an Access Report, if you've added a grouping level, you can display a
running sum; check Access Help for instructions on how to do that.

In my underlying query, I've added an Expression cell SubTot:=Beginning
Balance+DepAmt-Fee Amt-Ck Amt. This works fine for the first transaction,
but every transaction after that is off because it uses the Beginning Balance
figure in the expression. So, I tried in the report to add an unbound text
box named EndBal with the following calc:End Bal: [SubTot]+Sum([Deposit
Amount]-[Fee Amount]-[Disbursement Amount]), running sum over all. Again,
this works fine for the first report period (1st month), but every one after
that is off again. So, how do I create a calculation for a running sum that
doesn't keep referring to the original beginning balance? Thanks very much
for the help.

OK, this might help. Given a Table of transactions containing an
"original beginning balance" value of $10.00 (i.e., a bogus "deposit"
with a date earlier than any other date in the Table):

[Account]
AccountID Date DepAmt Ck Amt
----------- ---------- ------- -------
-1135076452 9/15/2004 $10.00 $0.00
1125055787 3/1/2005 $50.00 $4.00
-187227504 3/15/2005 $25.00
652112721 4/2/2005 $13.00
-1342895298 5/3/2005 $25.00
799845863 5/10/2005 $0.00 $8.00

I defined a Query to sum the transactions for each month:

[Q_Balance]

SELECT Year([Account]![Date]) AS AccYr,
Month([Account]![Date]) AS AccMth,
Sum(Nz([Account]![DepAmt])-Nz([Account]![Ck Amt])) AS Amt
FROM Account
GROUP BY Year([Account]![Date]), Month([Account]![Date])
ORDER BY Year([Account]![Date]), Month([Account]![Date]);

This produced the following results, in Query Datasheet View (you might
have to specify Currency format for the amount):

AccYr AccMth Amt
----- ------ --------
2004 9 $10.00
2005 3 $21.00
2005 4 ($13.00)
2005 5 $17.00

Based on that, I defined another Query to calculate running totals:

[Q_RunningBalance]

SELECT Bal1.AccYr, Bal1.AccMth,
Sum(Bal2.Amt) AS SumOfAmt
FROM Q_Balance AS Bal1, Q_Balance AS Bal2
WHERE (((Bal2.AccMth)<=[Bal1].[AccMth])
AND ((Bal2.AccYr)=[Bal1].[AccYr]))
OR (((Bal2.AccYr)<[Bal1].[AccYr]))
GROUP BY Bal1.AccYr, Bal1.AccMth;

In Query Datasheet View, I got this:

AccYr AccMth SumOfAmt
----- ------ --------
2004 9 $10.00
2005 3 $31.00
2005 4 $18.00
2005 5 $35.00

You may want to fiddle with date formats, etc.

If you want to display both the details and the monthly running balances
in one Query, you could do something like this:

[Q_Details]

SELECT Account.Date, Account.DepAmt,
Account.[Ck Amt],
RB.SumOfAmt AS MonthlyRunningBalance
FROM Account, Q_RunningBalance AS RB
WHERE (((Month([Account]![Date]))=[RB].[AccMth])
AND ((Year([Account]![Date]))=[RB].[AccYr]))
ORDER BY Account.Date;

giving a Query Datasheet View looking like this:

Date DepAmt Ck Amt MonthlyRunningBalance
---------- ------- ------- ---------------------
9/15/2004 $10.00 $0.00 $10.00
3/1/2005 $50.00 $4.00 $31.00
3/15/2005 $25.00 $31.00
4/2/2005 $13.00 $18.00
5/3/2005 $25.00 $35.00
5/10/2005 $0.00 $8.00 $35.00

In a Report based on this Query, of course, you'd likely display the
monthly balances only in the group summary sections, not with every
transaction as shown here. You'd also probably include additional
fields (not shown) identifying payee, purpose of transaction, &c.

Is this closer to what you wanted? HTH.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.