Calculate conditiona total at bottom of continuous form.

[Dennis]

Hi,

I'm running Access via XP Office Pro on Windows 7 Pro.

Background:
---------------------------------------------------
I'm working on a cash receipts data entry form to track Sunday contributions
for a church. We enter the cash and checks in a batches of 20 contribution
envelopes.

At the time the batches are created, the people do not look inside the
evelope to determine if the envelope contains cash or a check. The next
person to handle the batch runs a adding machine / calculator tape on the
batch. The next person to handle the batches enters them into the computer.
The have to balance to the calculator tape or correct the tape so it balances
to the batch.

Each batch contains both checks and cash. I am in the process of trying to
change the process so that a batch contains only checks or only cash, but I
have not been able to convince the users. This is an older congregations and
"this is how it has always been done". It is less painfull to bash my head
against a brick wall then trying to talk them into into this change.


Database ----------------------------------------------

I'm trying to keep this real simple. I have many tables, but the the tables
that are concerned with this question are:

tblCheckRegister - 1 record for each check issued or for each contribution
/ cash batch.
tblContribution - Contains 1 record for each contribution amount within
a batch. A batch number is assigned to the deposit record that goes in the
tblCheckRegister and is stampled on each contritbution record associated with
the batch.

Question------------------------------------------------------

I have a cash receipts screen that contains a form and a sub-form. The form
allows the user to enter the data that creates the deposit record that goes
in the check register table. The sub-form enables the user to enter the
individual contribution records that are posted to the tblContribution table.

The footing of the cash entry sub-form has a text control that sums the
values of the cash / checks entered. This works fine.

I want to create a text control box that will go in the footer that shows
the totals of the cash entered. I would like another text control that would
contain the total for the checks entered. I know if cash is entered because
the check number field will contain the word Cash.

The total of the cash sub-total plus the check sub-total should equal the
grand total.

I have tried the following =IIf([FamCheckNo]="Cash",Sum([ChkAmt]),0) in the
cash sub-total field, but it does not work.

Does anyone have a suggestion regarding what I can do to get this to work?

Dennis

 

[golfinray]

I am not quite fully understanding exactly what you want, but let me take a
shot.
First, you have a form and subform. You need a form/subform when you have a
one-to-many relationship. One contributor, many contributions. One salesman,
many products. It sounds like you may have one cash, one check set up. That
would not require a form/subform so you may want to thin about that. Second,
for sums you need as the control source of your textbox:
=sum([cashfield])
and =Sum([checkfield])
Then =sum(([cashfield])+([checkfield]))
Hope that helps

--
Milton Purdy
ACCESS
State of Arkansas

Hi,

I'm running Access via XP Office Pro on Windows 7 Pro.

Background:
---------------------------------------------------
I'm working on a cash receipts data entry form to track Sunday contributions
for a church. We enter the cash and checks in a batches of 20 contribution
envelopes.

At the time the batches are created, the people do not look inside the
evelope to determine if the envelope contains cash or a check. The next
person to handle the batch runs a adding machine / calculator tape on the
batch. The next person to handle the batches enters them into the computer.
The have to balance to the calculator tape or correct the tape so it balances
to the batch.

Each batch contains both checks and cash. I am in the process of trying to
change the process so that a batch contains only checks or only cash, but I
have not been able to convince the users. This is an older congregations and
"this is how it has always been done". It is less painfull to bash my head
against a brick wall then trying to talk them into into this change.


Database ----------------------------------------------

I'm trying to keep this real simple. I have many tables, but the the tables
that are concerned with this question are:

tblCheckRegister - 1 record for each check issued or for each contribution
/ cash batch.
tblContribution - Contains 1 record for each contribution amount within
a batch. A batch number is assigned to the deposit record that goes in the
tblCheckRegister and is stampled on each contritbution record associated with
the batch.

Question------------------------------------------------------

I have a cash receipts screen that contains a form and a sub-form. The form
allows the user to enter the data that creates the deposit record that goes
in the check register table. The sub-form enables the user to enter the
individual contribution records that are posted to the tblContribution table.

The footing of the cash entry sub-form has a text control that sums the
values of the cash / checks entered. This works fine.

I want to create a text control box that will go in the footer that shows
the totals of the cash entered. I would like another text control that would
contain the total for the checks entered. I know if cash is entered because
the check number field will contain the word Cash.

The total of the cash sub-total plus the check sub-total should equal the
grand total.

I have tried the following =IIf([FamCheckNo]="Cash",Sum([ChkAmt]),0) in the
cash sub-total field, but it does not work.

Does anyone have a suggestion regarding what I can do to get this to work?

Dennis

 

[Steve Sanford]

I also don't understand your structure. Looking at your formula for the
control, I would have written it differently.

You want to sum the field [ChkAmt], but only if the field [FamCheckNo]
equals "Cash".

For the cash sub-total, I would write it like this:

=Sum(IIf([FamCheckNo]="Cash", [ChkAmt], 0))


For the check sub-total, I would write it like this:

=Sum(IIf([FamCheckNo]<>"Cash", [ChkAmt], 0))


... just my 2 pennies.......

--
HTH
---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi,

I'm running Access via XP Office Pro on Windows 7 Pro.

Background:
---------------------------------------------------
I'm working on a cash receipts data entry form to track Sunday contributions
for a church. We enter the cash and checks in a batches of 20 contribution
envelopes.

At the time the batches are created, the people do not look inside the
evelope to determine if the envelope contains cash or a check. The next
person to handle the batch runs a adding machine / calculator tape on the
batch. The next person to handle the batches enters them into the computer.
The have to balance to the calculator tape or correct the tape so it balances
to the batch.

Each batch contains both checks and cash. I am in the process of trying to
change the process so that a batch contains only checks or only cash, but I
have not been able to convince the users. This is an older congregations and
"this is how it has always been done". It is less painfull to bash my head
against a brick wall then trying to talk them into into this change.


Database ----------------------------------------------

I'm trying to keep this real simple. I have many tables, but the the tables
that are concerned with this question are:

tblCheckRegister - 1 record for each check issued or for each contribution
/ cash batch.
tblContribution - Contains 1 record for each contribution amount within
a batch. A batch number is assigned to the deposit record that goes in the
tblCheckRegister and is stampled on each contritbution record associated with
the batch.

Question------------------------------------------------------

I have a cash receipts screen that contains a form and a sub-form. The form
allows the user to enter the data that creates the deposit record that goes
in the check register table. The sub-form enables the user to enter the
individual contribution records that are posted to the tblContribution table.

The footing of the cash entry sub-form has a text control that sums the
values of the cash / checks entered. This works fine.

I want to create a text control box that will go in the footer that shows
the totals of the cash entered. I would like another text control that would
contain the total for the checks entered. I know if cash is entered because
the check number field will contain the word Cash.

The total of the cash sub-total plus the check sub-total should equal the
grand total.

I have tried the following =IIf([FamCheckNo]="Cash",Sum([ChkAmt]),0) in the
cash sub-total field, but it does not work.

Does anyone have a suggestion regarding what I can do to get this to work?

Dennis

 

[Dennis]

Steve, Milton,

Thank you for your assitance. My apoligies for not be clearer and not
responding sooner. That pesking think called work kept me away for a couple
of days.



Steve,

I used your code of:

=Sum(IIf([FamCheckNo]<>"Cash", [ChkAmt], 0))

and it worked!

I'm a happy camper now.

Dennis

 

[Steve Sanford]

I'm afflicted with that same ailment: work!

But it allows me to do the two things I enjoy the most...... Live indoors
and eat


Glad its working.....