Calculate conditiona total at bottom of continuous form.
in [Forms]
|
From: Dennis on 27 May 2010 15:54 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
From: golfinray on 27 May 2010 16:03 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 "Dennis" wrote: > 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
From: Steve Sanford limbim53 at yahoo dot on 27 May 2010 17:34 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.) "Dennis" wrote: > 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
From: Dennis on 27 May 2010 23:11 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
From: Steve Sanford limbim53 at yahoo dot on 28 May 2010 03:31 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..... -- HTH --- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Dennis" wrote: > 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 > > > >
|
Pages: 1 Prev: Syntax error in Update statement Next: Richiesta salvataggio modifiche in una mascehra |