Checkbook Balance with 2 Accounts

[Robert T]

Hello:

I'm new to Access and need a little help. I originally created a simple
checkbook app and I could easily create calculated fields for both Debits and
Credits, subtract the two, and come with a checkbook balance. Since there's
only one checkbook account, that was pretty simple.

However, I then added a 2nd checking account to the application and created
a "One to Many Form" with the Main Form called tblAccounts and the Sub Form
called tblTransactions. I'm having a hard time figuring out to query the
Debits and Credits for each account, get the checkbook balance, and display
it on the Form.

Note:

[1] The two accounts are stored in a field called [ID_NO] and they hold the
values of "001" and "002".

[2] tblTransactions![DEBITS] and tblTransactions![CREDITS] are currency
fields.

The One to Many Form works well, when I move to Account Number "002", I only
see the transactions for that account. Now if I can only total the DEBITS and
CREDITS and display a checkbook balance for Account Number "001" or "002",
the one that's currently active/displayed, I would be most grateful.

Thanks,
RT

 

[Robert T]

Hello:

I just reread my initial post and think I need to clarify something. I'm not
having any problems creating the query, it works perfectly, giving me the
correct balance grouped by Account [ID_No]. my only problem is automatically
displaying the correct balance on the form for each checking account. I don't
know how to do that in Access 2003.

In other words, when I'm viewing/working with ID_No "001" obviously I only
want the current balance for that specific account, I don't want to see the
current checkbook balance for ID_No "002".

 

[strive4peace]

Hi Robert,

btw, it would be easier to store Credits and Debits together
using, ie: an Amount field -- make one of them (probably
credit) negative numbers -- then, you just have one field to
sum. If you want to show credits, that would be any records
where Amount <0 -- likewise Debits would be any Amount >0.

On report, you can still have 2 columns -- one for Debit and
one for Credit -- use the ABS (absolute value) function to
make all numbers display as positive. Your balance
calculations will be faster if they are on just one field
instead of 2.

'~~~~~~~~~~`

Since you have limited the display of transactions to those
for the specific account number, you can simply add up what
is showing on the subform

put a calculated control(s) in the form footer (or header)
of your transactions


Name --> SumControlname
ControlSource --> =Sum[Controlname])

'~~~~~~~~~~`

If you continue to keep credit and debit seperated...

Name --> SumDebit
ControlSource --> =Sum(nz([Debit]))
-- assuming "Debit" is the name of the control holding the debit

Name --> SumCredit
ControlSource --> =Sum(nz([Credit]))
-- assuming "Credit" is the name of the control holding the
credit

Name --> Balance
ControlSource --> =Sum(nz([Credit]) - nz([Debit]))
-- you have to use controls that are NOT calculated in any
equation on the form (same is true for reports)

'~~~~~~~~~~`

If you consolidate Debit and Credit into an Amount field...

Name --> SumDebit
ControlSource --> =Sum(IIF(nz([Amount])>0,[Amount],0))

Name --> SumCredit
=ABS(Sum(IIF(nz([Amount])<0,[Amount],0)))

Name --> Balance
ControlSource --> =Sum( nz([Amount]) )


Obviously, in order for the overall Balance to be correct,
you will need to add a transaction for your beginning
balance before you started tracking things. The figure here
will just be a reflection for the month, or whatever period
the form is for.

You may want to order your transactions by descending date
so the most recent transactions are at the top. I will tell
you how to do this if you ask (since Access ignores the sort
specified in a query).

'~~~~~~~~~~~ NZ

NZ is null-to-zero and will make a null value 0 or an empty
string unless the second optional parameter is specified.
The reason you need to do this is because, analogous to
multiplying a number by 0 where the answer is always zero --
Access cannot add or subtract "nothing" to something -- the
answer is always an error

nz([credit])

is the same as

nz([credit],0)

since Credit is bound and Access knows its data type, the
second argument (optional) specifying 0 is not necessary

'~~~~~~~~~~~ updating the balance

since the balance and sums are calculated controls, they
will only be updated when a record is saved. If you wish
these numbers to change as you enter credits and debits, you
can put this on the AfterUpdate event of Credit and Debit
(or Amount if you consolidate):

me.dirty = false

'~~~~~~~~~~~ Dirty property

if a form is "dirty", that means that data has changed. By
setting "dirty" to be false, you are telling Access to make
it that way -- ie, save the record

if you are in code and you do not know if a record needs to
be saved, you can do this:

if me.dirty then me.dirty = false

since I told you to put the save on the AfterUpdate event of
a control, the record is already dirty (or the event
wouldn't happen) and we don't need to test it



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hello:

I'm new to Access and need a little help. I originally created a simple
checkbook app and I could easily create calculated fields for both Debits and
Credits, subtract the two, and come with a checkbook balance. Since there's
only one checkbook account, that was pretty simple.

However, I then added a 2nd checking account to the application and created
a "One to Many Form" with the Main Form called tblAccounts and the Sub Form
called tblTransactions. I'm having a hard time figuring out to query the
Debits and Credits for each account, get the checkbook balance, and display
it on the Form.

Note:

[1] The two accounts are stored in a field called [ID_NO] and they hold the
values of "001" and "002".

[2] tblTransactions![DEBITS] and tblTransactions![CREDITS] are currency
fields.

The One to Many Form works well, when I move to Account Number "002", I only
see the transactions for that account. Now if I can only total the DEBITS and
CREDITS and display a checkbook balance for Account Number "001" or "002",
the one that's currently active/displayed, I would be most grateful.

Thanks,
RT

 

[strive4peace]

Hi Robert,

rather than using a query, try this:

base your subform directly on your transactions table

use LinkMasterFields and LinkChildFields properties of the
subform control to filter for the account number you have
displayed in the main form

LinkMasterFields --> ID_No_controlname
(on main form)

LinkChildFields --> ID_No_controlname
(on subform -- this needs to be there but does not need to
show -- make its Visible property =no)

Robert, please let me know if you saw both this AND the
previous post


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Robert T]

Hi Crystal:

It's Thursday morning, around 9 am here in MD, and I'm off to the gym. Those
are some interesting suggestions which I'll try when I return.

If I use only one field for dollar amounts, there must be a way to ensure
that debits and credits are always entered as the appropriate positive or
negative number. Any thoughts on that one?

The filter idea is intriguing, I'll play around with that and see if it does
what I need. I agree, that would indeed make the calcuations much easier.

Speak with you later,
Robert

Hi Robert,

btw, it would be easier to store Credits and Debits together
using, ie: an Amount field -- make one of them (probably
credit) negative numbers -- then, you just have one field to
sum. If you want to show credits, that would be any records
where Amount <0 -- likewise Debits would be any Amount >0.

On report, you can still have 2 columns -- one for Debit and
one for Credit -- use the ABS (absolute value) function to
make all numbers display as positive. Your balance
calculations will be faster if they are on just one field
instead of 2.

'~~~~~~~~~~`

Since you have limited the display of transactions to those
for the specific account number, you can simply add up what
is showing on the subform

put a calculated control(s) in the form footer (or header)
of your transactions


Name --> SumControlname
ControlSource --> =Sum[Controlname])

'~~~~~~~~~~`

If you continue to keep credit and debit seperated...

Name --> SumDebit
ControlSource --> =Sum(nz([Debit]))
-- assuming "Debit" is the name of the control holding the debit

Name --> SumCredit
ControlSource --> =Sum(nz([Credit]))
-- assuming "Credit" is the name of the control holding the
credit

Name --> Balance
ControlSource --> =Sum(nz([Credit]) - nz([Debit]))
-- you have to use controls that are NOT calculated in any
equation on the form (same is true for reports)

'~~~~~~~~~~`

If you consolidate Debit and Credit into an Amount field...

Name --> SumDebit
ControlSource --> =Sum(IIF(nz([Amount])>0,[Amount],0))

Name --> SumCredit
=ABS(Sum(IIF(nz([Amount])<0,[Amount],0)))

Name --> Balance
ControlSource --> =Sum( nz([Amount]) )


Obviously, in order for the overall Balance to be correct,
you will need to add a transaction for your beginning
balance before you started tracking things. The figure here
will just be a reflection for the month, or whatever period
the form is for.

You may want to order your transactions by descending date
so the most recent transactions are at the top. I will tell
you how to do this if you ask (since Access ignores the sort
specified in a query).

'~~~~~~~~~~~ NZ

NZ is null-to-zero and will make a null value 0 or an empty
string unless the second optional parameter is specified.
The reason you need to do this is because, analogous to
multiplying a number by 0 where the answer is always zero --
Access cannot add or subtract "nothing" to something -- the
answer is always an error

nz([credit])

is the same as

nz([credit],0)

since Credit is bound and Access knows its data type, the
second argument (optional) specifying 0 is not necessary

'~~~~~~~~~~~ updating the balance

since the balance and sums are calculated controls, they
will only be updated when a record is saved. If you wish
these numbers to change as you enter credits and debits, you
can put this on the AfterUpdate event of Credit and Debit
(or Amount if you consolidate):

me.dirty = false

'~~~~~~~~~~~ Dirty property

if a form is "dirty", that means that data has changed. By
setting "dirty" to be false, you are telling Access to make
it that way -- ie, save the record

if you are in code and you do not know if a record needs to
be saved, you can do this:

if me.dirty then me.dirty = false

since I told you to put the save on the AfterUpdate event of
a control, the record is already dirty (or the event
wouldn't happen) and we don't need to test it



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hello:

I'm new to Access and need a little help. I originally created a simple
checkbook app and I could easily create calculated fields for both Debits and
Credits, subtract the two, and come with a checkbook balance. Since there's
only one checkbook account, that was pretty simple.

However, I then added a 2nd checking account to the application and created
a "One to Many Form" with the Main Form called tblAccounts and the Sub Form
called tblTransactions. I'm having a hard time figuring out to query the
Debits and Credits for each account, get the checkbook balance, and display
it on the Form.

Note:

[1] The two accounts are stored in a field called [ID_NO] and they hold the
values of "001" and "002".

[2] tblTransactions![DEBITS] and tblTransactions![CREDITS] are currency
fields.

The One to Many Form works well, when I move to Account Number "002", I only
see the transactions for that account. Now if I can only total the DEBITS and
CREDITS and display a checkbook balance for Account Number "001" or "002",
the one that's currently active/displayed, I would be most grateful.

Thanks,
RT

 

[Robert T]

Hi Crystal:

I was reading quickly earlier this morning because I was in a rush to hit
the gym and I think I misunderstood your message. Are you simply suggesting
the creation of a "One to Many Form" with tblAccounts as the Parent and
tblTransactions as the child? If so, I already did that. But now I don't know
how to calculate debits and credits only for the current account. That's the
point of my question, I only want to display the current balance for let's
say Acct_No "001".

If I create a calculation and place it on the form, won't it calculate ALL
debits and credits, as opposed to only those linked to the current account?

As for using one field instead of Debits and Credits, that's an intriguing
idea if I can ensure the user can only enter negative amounts if it's a
credit and positve amounts if it's a debit. I can't rely on the user to
always make the correct choice manually.

Any thoughts?

Robert

 

[Robert T]

Hi Crystal:

Wow! You put a lot of thought into that response, and me, like a real idiot,
fired off a response prior to moving down to see the remainder of your
message. I am so sorry and apolgize for jumping the gun. It appears as if you
already answered my question and I missed it the first time around. I better
slow down and start reading a little more carefully.

I'll digest what you wrote and get back to you.

Thanks so much,
Robert

 

[strive4peace]

Hi Robert,

just because you STORE both in one place doesn't mean you
have to collect it that way...

you can use 2 unbound controls for CREDIT and DEBIT that are
mutually exclusive...

If you are using a single form to collect the information,
you can do it this way:

'~~~~~~~~~~~~`
Private Sub Credit_AfterUpdate()
If Nz(Me.Credit) > 0 Then
Me.Debit = 0
Me.Amount = -Me.Credit
End If
End Sub

Private Sub Debit_AfterUpdate()
If Nz(Me.Debit) > 0 Then
Me.Credit = 0
Me.Amount = Me.Debit
End If
End Sub

Private Sub Form_Current()
Select Case True
Case Nz(Me.Amount) > 0
Me.Debit = Me.Amount
Me.Credit = Null
Case Nz(Me.Amount) < 0
Me.Credit = -Me.Amount
Me.Debit = Null
Case Else
Me.Credit = Null
Me.Debit = Null
End Select
End Sub

'~~~~~~~~~~~~`

If you are using a continuous form, it will need to be done
differently so that each Credit and Debit shows (an unbound
control can only have one value on the form)

For displaying info, you can do this:

field --> Credit: IIF(nz([Amount])<0,-Amount,0)
field --> Debit: IIF(nz([Amount])>0,Amount,0)


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hi Crystal:

It's Thursday morning, around 9 am here in MD, and I'm off to the gym. Those
are some interesting suggestions which I'll try when I return.

If I use only one field for dollar amounts, there must be a way to ensure
that debits and credits are always entered as the appropriate positive or
negative number. Any thoughts on that one?

The filter idea is intriguing, I'll play around with that and see if it does
what I need. I agree, that would indeed make the calcuations much easier.

Speak with you later,
Robert

:

Hi Robert,

btw, it would be easier to store Credits and Debits together
using, ie: an Amount field -- make one of them (probably
credit) negative numbers -- then, you just have one field to
sum. If you want to show credits, that would be any records
where Amount <0 -- likewise Debits would be any Amount >0.

On report, you can still have 2 columns -- one for Debit and
one for Credit -- use the ABS (absolute value) function to
make all numbers display as positive. Your balance
calculations will be faster if they are on just one field
instead of 2.

'~~~~~~~~~~`

Since you have limited the display of transactions to those
for the specific account number, you can simply add up what
is showing on the subform

put a calculated control(s) in the form footer (or header)
of your transactions


Name --> SumControlname
ControlSource --> =Sum[Controlname])

'~~~~~~~~~~`

If you continue to keep credit and debit seperated...

Name --> SumDebit
ControlSource --> =Sum(nz([Debit]))
-- assuming "Debit" is the name of the control holding the debit

Name --> SumCredit
ControlSource --> =Sum(nz([Credit]))
-- assuming "Credit" is the name of the control holding the
credit

Name --> Balance
ControlSource --> =Sum(nz([Credit]) - nz([Debit]))
-- you have to use controls that are NOT calculated in any
equation on the form (same is true for reports)

'~~~~~~~~~~`

If you consolidate Debit and Credit into an Amount field...

Name --> SumDebit
ControlSource --> =Sum(IIF(nz([Amount])>0,[Amount],0))

Name --> SumCredit
=ABS(Sum(IIF(nz([Amount])<0,[Amount],0)))

Name --> Balance
ControlSource --> =Sum( nz([Amount]) )


Obviously, in order for the overall Balance to be correct,
you will need to add a transaction for your beginning
balance before you started tracking things. The figure here
will just be a reflection for the month, or whatever period
the form is for.

You may want to order your transactions by descending date
so the most recent transactions are at the top. I will tell
you how to do this if you ask (since Access ignores the sort
specified in a query).

'~~~~~~~~~~~ NZ

NZ is null-to-zero and will make a null value 0 or an empty
string unless the second optional parameter is specified.
The reason you need to do this is because, analogous to
multiplying a number by 0 where the answer is always zero --
Access cannot add or subtract "nothing" to something -- the
answer is always an error

nz([credit])

is the same as

nz([credit],0)

since Credit is bound and Access knows its data type, the
second argument (optional) specifying 0 is not necessary

'~~~~~~~~~~~ updating the balance

since the balance and sums are calculated controls, they
will only be updated when a record is saved. If you wish
these numbers to change as you enter credits and debits, you
can put this on the AfterUpdate event of Credit and Debit
(or Amount if you consolidate):

me.dirty = false

'~~~~~~~~~~~ Dirty property

if a form is "dirty", that means that data has changed. By
setting "dirty" to be false, you are telling Access to make
it that way -- ie, save the record

if you are in code and you do not know if a record needs to
be saved, you can do this:

if me.dirty then me.dirty = false

since I told you to put the save on the AfterUpdate event of
a control, the record is already dirty (or the event
wouldn't happen) and we don't need to test it



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hello:

I'm new to Access and need a little help. I originally created a simple
checkbook app and I could easily create calculated fields for both Debits and
Credits, subtract the two, and come with a checkbook balance. Since there's
only one checkbook account, that was pretty simple.

However, I then added a 2nd checking account to the application and created
a "One to Many Form" with the Main Form called tblAccounts and the Sub Form
called tblTransactions. I'm having a hard time figuring out to query the
Debits and Credits for each account, get the checkbook balance, and display
it on the Form.

Note:

[1] The two accounts are stored in a field called [ID_NO] and they hold the
values of "001" and "002".

[2] tblTransactions![DEBITS] and tblTransactions![CREDITS] are currency
fields.

The One to Many Form works well, when I move to Account Number "002", I only
see the transactions for that account. Now if I can only total the DEBITS and
CREDITS and display a checkbook balance for Account Number "001" or "002",
the one that's currently active/displayed, I would be most grateful.

Thanks,
RT

 

[strive4peace]

you're welcome, Robert

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Robert T]

Hi Crystal:

Even though I'm intrigued by the idea of one field for both debits and
credits, for now I'm leaving it as until I have enough time to work on making
that change. I added the 3 calculated fields you described to the form, but
I'm right back where I started. Instead of adding up the Debits & Credits per
account, the calculated fields [in the footer] are adding up ALL of the
Debits and Credits in both checking accounts.

Any suggestions?

Thanks,
Robert

 

[strive4peace]

Hi Robert,

the sum controls should be totalling just the transactions
that are SHOWING. If the sum includes both of your
accounts, then all those transactions must show ...

limit the subform to display just one account at a time

For your subform control, what are:

LinkMasterFields
LinkChildFields

?


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Robert T]

Hello Crystal:

Both the Master and Child linking field is [ID_No] which works properly.

Maybe I did something wrong? I tried to carefully follow your steps but I
have missed something that's obvious to you but not to a new Access user.

Robert

 

[Robert T]

Hi Crystal:

Please don't get angry with me but I just realized I did something very
stupid. When I imported the application from my Alpha Five database [a great
program] I forgot that tblAccouns [the Parent] also had two fields called
[DEBITS] and [CREDITS]. why? In A5 I calculated the sum of the debits and
credits per account, and placed those totals in the relevant fields in the
parent table.

In Access, we're doing things differently, we're using calculated controls
on the form. Once I realized the error of my ways, I deleted those two tables
from the Parent table.

Now I'm getting an ERROR message for all 3 calculated controls. Obviously
I'm doing something wrong as my expressions aren't working. That's probably
the root cause of the dilemma.

Here's SumDebits ControlSource for the DEBITS field that I wound up with by
using the Expression Builder. For SumCredits the only difference is the word
Credits.

=SUM(nz(tblTransactions.Form!Debits))

Note: I originally put sfrmTblTransactions, the name of the subform, but
Access changed it back to tblTransactions.

Robert

 

[Robert T]

Hi Crystal:

My last message wasn't as clear as I would have liked. Once again, I was
rushing and made some silly gramattical errors.

I just did some testing and realized my previous messages are now irrelevant
because I was NOT totaling the Debits + Credits in the child table
[tblTransactions]. The calculated control was doing nothing more than reading
the values from the fields [CREDITS] and [DEBITS] that were still in the
Parent Table. Duhhhhhh......

After realizing how stupid I was, I immediately deleted those two fields
from the parent table. Now the calculated controls are trying to total the
[CREDITS] and [DEBITS] from the chld table [tblTransactions], which is what
we wanted all along.

As I mentioned before, I'm receiving an error message for each of the 3
calculated controls. So Crystal, I think the problem is as simple as getting
the correct expression. Somehow I'm apparently doing something wrong in terms
of building the expression for each control. I have a feeling once that's
corrected, the original problem will be solved.

Once again, I apologize for that dumb error. However, I didn't realize that
I forgot to delete the [debits] and [credits] fields from the parent table
after the original import of the DBASE file.

Thanks,
Robert

 

[strive4peace]

Hi Robert,

" I forgot that tblAccouns [the Parent] also had two fields
called [DEBITS] and [CREDITS]. why?"

probably because it is storing calculated values - which is
not necessary and not a good idea either

"Now I'm getting an ERROR message for all 3 calculated controls"

Make sure you are using the NAME property for each control
instead of what actually shows up in Deisgn View, the
ControlSource -- turn on the properties sheet and look at
the Name. I like to change the name to match the
ControlSource for bound controls.

=SUM(nz(tblTransactions.Form!Debits))

if the equation is ON the form with your controls, you
should do this:

=SUM(nz([Debit]))

assuming Debit is the NAME of your Debit control

once the equation is on your subform and calculating
correctly, we can cover eachoing that value to the main form
if you like


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[strive4peace]

Hi Robert,

try to put the equation in WITHOUT the expression builder...
look up the Name property (set them to something logical if
they are ambiguous) and use that

You do not need a form reference if the controls you are
adding are ON the form with the equation -- all you need is
the correct control name


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Robert T]

Hi Crystal:

I double checked the NAME properties for the two fields on the subform and
they were indeed correctly named as DEBITS and CREDITS, so that wasn't the
issue. Crystal, this is so frustrating that I'm almost ready to give up. I've
tried all of your suggestions, and everything else I could think of, and
still always get an error message for the calculated controls.

Just out of curiosity I went to the subform by itself and created the
calculated controls and they worked. Therefore I know the expressions and
NAME properties of the fields are correct. However, when I go back to the One
to Many form, where tlbAccounts is the parent and tblTransactions is the
child, I keep getting those frustrating error messages on the calcuated
controls that worked when used on a flat file table. I'm sure the cause of
this problem is something so simple that I'm missing the obvious. But I must
tell you, Access makes this far more difficult than it should be.

I think we've exhausted almost everything, is there anything else you can
think of?

Thanks,
Robert

 

[Robert T]

Hi Crystal:

Just when I was ready to give up I achieved a partial success. I tried your
suggestion of putting the name of the control with the equal sign and nothing
else. Well gues what? It worked! I saw the value of the DEBITS field which
was $127.39 and it changed everytime I moved to a new record. Finally, a sign
of progress.

Here's the expression for the calculated control called SumDEBITS

=sfrmTblTransactions.form!DEBITS

However, the following brought up an error message.

=Sum(sfrmTblTransactions.form!DEBITS)
=Sum(nz(sfrmTblTransactions.form!DEBITS))

Did I do something wrong the construction of the expression above?

Thanks,
Robert

 

[strive4peace]

Hi Robert,

you cannot sum from the parent form... the equation must be
on the the same form as what you are adding up (even if it
is not visible)

then, you can do this to echo it onto the parents form

=subform_controlname.form.controlname


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Robert T]

Hi Crystal:

[you cannot sum from the parent form... the equation must be
on the the same form as what you are adding up (even if it
is not visible)

then, you can do this to echo it onto the parents form

=subform_controlname.form.controlname ]

I wish you had mentioned the above yesteday, it would have saved me hours of
frustration trying to get this to work. Nonethless, with the knowledge of the
above, your suggetions worked. I haven't had time to thoroughly test it out,
but so far it looks good.

Crystal, I can't begin to thank you enough, I truly appreciate your patience
and the fact that you hung in there with me all along.

Robert