Yes.I belive it is a running balance from Expr1 since each month there
will
be a deposit into accounts then Expr1 provides a calculation off that
balance then Expr2 should be reducing Expr1 as withdrawals/charges are
done
to that account during the month.
:
Are you looking for a running balance or just a single "row" per ACCT
that
sums the deposits and withdrawals?
--
Duane Hookom
MS Access MVP
ACCT Date WithdrawalAmt DepositAmt Expr1:
Expr2:
1525102 08/15/2006 $10,000
$300.00
1525102 08/20/2006 $100.00
$200.00
1525102 08/25/2006 $100.00
$100.00
On 8/20, a withdrawal should reduce Expr1 by $100.00 leaving $200.00
in
Expr2 then on 8/25 for this acct, another withdrawal of $100.00
should
again
reduce Expr1 leaving a balance of $100.00 in Expr2.
The value in Expr1 is the 3 percent from the intial 10,000 deposit
then
any
withdrawls placed against the account would reduce Expr1.
:
How about providing some sample records and desired results. It
isn't
clear
what your data looks like and how dates figure into the solution or
problem.
--
Duane Hookom
MS Access MVP
I see it is my description of what was needed that needed to be
clarified.These withdrawals and deposits on done on accts. So if
acct
MB1212
has a deposit on the August 18 and then on August 25 there is a
withdrawal
on
this acct, then expr:2 should perform the calculation on August
18.
Sorry
for
not stating this sooner!
:
I created a table and entered your values and got 200 as the
result
as
expected. My only change to your SQL other than removing
irrelevant
tables
and fields was to specify the "if Null" value in Nz().
SELECT [Account Types].AccountTypeID,
Accounts.AccountNumber,
Transactions.TransactionDescription,
Transactions.WithdrawalAmount,
Transactions.DepositAmount,
[DepositAmount]*1.03-[DepositAmount] AS Expr1,
NZ([DepositAmount],0)*1.03-NZ([DepositAmount],0)-NZ([WithdrawalAmount],0)
AS Expr2
FROM [Account Types] RIGHT JOIN (Accounts RIGHT JOIN
Transactions
ON
Accounts.AccountID = Transactions.AccountID) ON [Account
Types].AccountTypeID
= Accounts.AccountTypeID;
--
Duane Hookom
MS Access MVP
Hello John,
The formula seems to move the withdrawal amount to the
expressions 2
column.I have included the SQL so if there is something you
can
see
in
it
that i have done wrongly in design/formula.
SELECT [Account Types].AccountTypeID, Accounts.AccountNumber,
Transactions.TransactionDescription,
Transactions.WithdrawalAmount,
Transactions.DepositAmount,
[DepositAmount]*1.03-[DepositAmount]
AS
Expr1,
(NZ([DepositAmount])*1.03-NZ([DepositAmount]))-NZ([WithdrawalAmount])
AS
Expr2
FROM [Account Types] RIGHT JOIN (Accounts RIGHT JOIN
Transactions
ON
Accounts.AccountID = Transactions.AccountID) ON [Account
Types].AccountTypeID
= Accounts.AccountTypeID;
I have a deposit amount of 10,000 and the the exp:1 provides
the
percentage
which results in 300.00.I placed 100.00 in the withdrawal
field
and
this
should have been subtracted from 300.00 with a the end result
of
200.00
in
the exp:2 field.
I appreciate your help in this!
:
On Mon, 28 Aug 2006 21:54:01 -0700, jk
<
[email protected]>
wrote:
Expr2:
([DepositAmount]*1.03-[DepositAmount])-[WithdrawalAmount]
but i get no results in this column at all...What else could
it
be
?
If any one of these fields is NULL, the entire expression
will
be
NULL. Try
Expr2: (NZ([DepositAmount])*1.03 - NZ([DepositAmount])) -
NZ([WithdrawalAmount])
or, more compactly and mathematically identical,
0.03*NZ([DepositAmount]) - NZ([WithdrawalAmount])
John W. Vinson[MVP]
