# Thread: Calculating a total baised on criteria

1. Hi I am struggling with totaling a calculation and need some "Expert" help!

I have a Fee Schedule with multiple parameters and would like to have it automatically give me a total. The Paramaters are:

Per filing (check box) must be true.
if plan is small then total is \$750.
If plan is Large then total is \$2000.
If there is multiple plans then the amounts above are doubled.....

Any help would be MUCH appriciated!!!
Thanks!

2. [quote name='RFlaggard' post='774187' date='08-May-2009 08:27']Hi I am struggling with totaling a calculation and need some "Expert" help!

I have a Fee Schedule with multiple parameters and would like to have it automatically give me a total. The Paramaters are:

Per filing (check box) must be true.
if plan is small then total is \$750.
If plan is Large then total is \$2000.
If there is multiple plans then the amounts above are doubled.....

Any help would be MUCH appriciated!!!
Thanks![/quote]

Can you post a version of the database with just enough data to see the overall structure?
Where do you want to perform the calculation? In a query or on a form or on a report?

3. [quote name='johnhutchison' post='774215' date='07-May-2009 23:08']Can you post a version of the database with just enough data to see the overall structure?
Where do you want to perform the calculation? In a query or on a form or on a report?[/quote]
This thing is a MONSTER~ Is there an easier way to explain it to you?? It would be calculating in both a query and on a form.

4. [quote name='RFlaggard' post='774713' date='12-May-2009 07:22']This thing is a MONSTER~ Is there an easier way to explain it to you?? It would be calculating in both a query and on a form.[/quote]

Generally if you perform the calculation in the query, then the form just displays the result generated by the query. Is that what you are doing?
But sometimes all the calculation is done on the form itself.
It sounds like more than one table is involved here? What are the tables? which of the fields are in which table? How do you know that there are multiple plans?
If 'Per filing (check box) must be true' how does this impact on the calculation? What happens if it is not true?

5. [quote name='johnhutchison' post='774756' date='11-May-2009 22:50']Generally if you perform the calculation in the query, then the form just displays the result generated by the query. Is that what you are doing?
But sometimes all the calculation is done on the form itself.
It sounds like more than one table is involved here? What are the tables? which of the fields are in which table? How do you know that there are multiple plans?
If 'Per filing (check box) must be true' how does this impact on the calculation? What happens if it is not true?[/quote]

Yes the calculation would be performed in a query then displayed on the form.

There is only one table involved. I have included a screen shot of the table design and the form. The fields are PerFiling (yes/no), MultiPlans (yes/no) and SizeofPlan (text field limited to either "Small" or "Large" as choice for value).

As you can see on the form the person imputing the information in will mark if the client has multiple plans to be included, and if the Fee type is PerFiling or PerDay. I have the PerDay calculation done and working.

My thoughts were this: If Perfiling is true AND SizeofPlan is "Small" then fee is \$700. If Perfiling is true AND SizeofPlan is "Large" then fee is \$2000. BUT if Perfiling is True AND Multiplans is True AND SizeofPlan is "Small" then fee is \$1400. IF Perfiling is true AND Multiplans is True AND SizeofPlan is "Large" then fee is \$4000.

I just don't know how best to put it together.....

Thank you for any help you can give me.... Or if there is a better way I am always open to ideas!

6. How is this?

Fee: iif([Perfilling] =true, iif([multiplans]=true, iif([sizeofplan]="small",1400, 4000),iif([sizeofplan]="small",700, 2000), [numberofdays]*feeperday))

iif is the Immediate if. It has 3 parts: a test, action if true, action if false so:
iif([sizeofplan]="small",1400, 4000) can be read as "if size of plan is small, then fee is 1400, else fee is 1400" but this is all within two other iif statements that are adding the two extra requirements Perfilling is true, and Multiplans is true.

Assumes that SizeofPlan can only be small or large, so if it is not small, then it is large.
If perfilling is false, then I imagine that the fee is calculated from the number of days. Replace feeperday with the amount.

If Perday and Perfilling are alternatives (as they seem to be) it would be better to have just one field displayed as an option group. That would guarantee that one (and only one) was chosen.

7. [quote name='johnhutchison' post='774926' date='12-May-2009 23:05']How is this?

Fee: iif([Perfilling] =true, iif([multiplans]=true, iif([sizeofplan]="small",1400, 4000),iif([sizeofplan]="small",700, 2000), [numberofdays]*feeperday))

iif is the Immediate if. It has 3 parts: a test, action if true, action if false so:
iif([sizeofplan]="small",1400, 4000) can be read as "if size of plan is small, then fee is 1400, else fee is 1400" but this is all within two other iif statements that are adding the two extra requirements Perfilling is true, and Multiplans is true.

Assumes that SizeofPlan can only be small or large, so if it is not small, then it is large.
If perfilling is false, then I imagine that the fee is calculated from the number of days. Replace feeperday with the amount.

If Perday and Perfilling are alternatives (as they seem to be) it would be better to have just one field displayed as an option group. That would guarantee that one (and only one) was chosen.[/quote]

Excellent!!!!!! I can't thank you enough!!!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•