Multiple calculations in one report, Access 97 3

[beasleyd]

Hey everyone. I'm still an infant when it comes to Access, so please bear with me.

I'm part of a Financial Advisory company and I've created a report of our clients that still have cases pending (not completed).

It is organized like below:

Plan Type (e.g.- Disability)

Total Commissions for this plan: (Sum of all listed commissions for this plan)

Commission(currency) ClientName Agent
Commission ClientName Agent
Commission ClientName Agent

That's generally what it looks like. Okay, what I need to do is this - you see how it takes the sum of say... all the Disability plans, or Life, or whatever the current plan is? I need to make specific calculations for each plan.

Each plan has different ratios when it comes to "Points" and "Dollar amounts"... for instance, a Life plan may be 1:1 in Pointsollars, but Disability may be .5:1.

I know this might sound confusing, I'm not really sure how to phrase it all properly. I'd like to display "Points" next to "Total Commissions for this Plan" and have it displayed, so no one has to do the math themselves.

Can anyone understand what I'm getting at? Any help would be appreciated, let me know if something is unclear.

Thanks a lot.

 

[PaulBricker]

If I understand, each plan has a certain "Points and Dollar" amount associated with it. Do you have a table that has

Plan      Points
Life              1:1
Disability       .5:1
WorkerComp       25:1


etc.  If you do then you should be able to add this table to the underlying query for your report, join the Plan fields and then add the Points field from this table.  Then you would just add the Points field to your Report and it should tie itself to the proper Plan automatically.
If I've missed the "Point" let me know.

Paul

 

[beasleyd]

Thanks for the reply

No, we don't have a table with that set up, that's why I was wanting to make the calculation within the report.

Now that I have the report up, I can describe it a little better...

In the "Plan_Type header" area, it displays... of course, the [Plan_Type] and then I have a text box that does:

 = Sum ([Annual_Commission])

.

This displays the sum of all the comissions it is about to list. I'd like to have some kind of "Points Possible" or something of that nature in the same row as the sum, which is easy, except that different plans have different ratios.

I'd rather not create a table, if at all possible, mainly for the honest reason that I'm not sure how I'd calculate the ratios in a table... unless you wanted to give me a little walkthrough...

Hope this clarifies things a bit.

 

[dhookom]

Kudos to Paul for recommending a maintainable, data-driven solution. Even if the solution might not match the original question (I think it does) it is great advice and deserves a star.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PaulBricker]

How are the ratio's calculated? Is there a formula? I assumed that they were predetermined to be certain ratios for certain Plans.

Paul

 

[beasleyd]

I'll explain it as best as I understand it... I'm just the IT guy, I let those crazy financial planners do their own thing

It's really done off of percentages, I was just using ratios as another means to describe my situation.


Let's say Life's % is 100... or 1:1

I wouldn't need a calculation for that, I'd just re-display the sum in a long number format.

What about, say, Disability? Let's put it at 35%. Say our total commissions were $32,600. I'd need a

Sum([Annual_Commission])*0.35

field to do this.

This comes to 11,410 credits awarded to the company/agent because of the 0.35 credits to 1 dollar ratio.

All I'm doing is creating a text box on the report, and using the expression builder for values.

Do you see the problem now? Or do you see my confusion? The formula changes with each plan, but I need all the plans in one report. This would be simple if I wanted 15 different reports, but that's too much of a hassle.

 

[PaulBricker]

I think you will have to work with a table on this. Put the Plan in one field and the Percent of Commission in the other field. Otherwise, you don't have any way to determine what percent goes with what Plan.
That's my take on it anyway. Something has to tell the report what the ratio is and that value has to be stored in a table someplace.

Paul

 

[beasleyd]

Okay, I suppose I can do that. Can you help me with the table though?

So I need records to have the plan type, a percent... and what else?

Once I get those, how do I query the correct information, then implement it?

I appreciate all of your help, I'm really happy I found these forums.

 

[PaulBricker]

You just need two fields
PlanType datatype Text Indexed, No Duplicates
PlanRatio datatype will depend. Either Text if you want a value like 1:1 or Number if you want a value you can calculate with like .35. Indexed = No

Then you add all the Plans to the PlanType field and all the corresponding Ratios to the PlanRatio field. Then you will need to create a query that has this table plus the table with your other Plan Info in it. Join the PlanType fields. Add the PlanType from the other table and the PlanRatio from this field. Then base you report on the query. If you already have a query undlying your Report, all you should have to do is add the new table and join the PlanType fields. Then add your Ratio field to the section of the report you want it.

Post back with any problems.

Paul

 

[beasleyd]

PaulBricker,

Thanks so much for the help. It worked perfectly. The only request they have for me now is to do something that I don't think is possible without some extensive VB knowledge, which I don't have.

I'll tell you what it is, but if it takes more than 20 seconds to think of a solution, don't worry about it, they can deal

For each plan (Life, Disability, Advantage) they have the credits figured from the commission amount... we all know that now right?

Well, lo and behold, there are a few special plans which have THEIR credits based on premiums, which are a percent of the commissions. So it'd be something like this...

[Special_Plan_Ratio] * ([Commissions] * .30)

So now we have TWO math problems instead of the simple ONE from before.

Now, there is a simple fix for this, I know, just pull the premium amounts from the database, create a field on the report for "Premium Based Credits" and do the formula there. The problem is, I'd have to display that field for EACH plan, even though it only applies to a select few...

So, blah blah blah, here's what I'm trying to do:

Is there a way to make a formula look for a SPECIFIC plan, and then add one more step to the math?

Something similar to an If-Then statement would work, but those aren't in VB...are they?

Anyway, thanks for all the previous help

 

[PaulBricker]

Well, right off the top, depending on the number of plans involved you could try this in the Control Source for your Textbox.

=IIf(Plan = "SpecialA" or Plan = "SpecialB" or Plan = "SpecialC", [Special_Plan_Ratio] * ([Commissions] * .30), [Special_Plan_Ratio] * [Commissions])


Paul

 

[beasleyd]

Wow, thanks.

Okay, we're getting somewhere, but a new problem came up...

=IIf([Plan_Type]="Special Life" Or "Special DI" Or "Special-LTC", Sum([Annual_Commission])/0.325*[Plan_Ratio], Sum([Annual_Commission])*[Plan_Ratio])

That's what I have down. Premiums are decided by DIVIDING the commission by 32.5%, then credits by multiplying 20% to the premium.

When I do the report, the special plans come out perfect, but all the others come up with completely wrong numbers.

For instance, we have "A10" at a .5:1 ratio.
The commissions total to $2468.00, which should be 1,234 credits. Instead it reports 3796.9231(?!).

Any ideas where I zigged when I should have zagged?

 

[PaulBricker]

If the left side works, then the right side should as well. The only difference is adding the dividend 0.325. Are you sure the left side is calculating correctly.

Paul

 

[dhookom]

You didn't follow Paul's syntax. You must add two more "[Plan_type]=". Re-read Paul's previous reply to find out where you missed this.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[beasleyd]

PaulBricker - You rock for sticking with me.

dhookom - You rock for finding that stupid mistake.

Thank you both.

It works perfectly now. I know where to come now for any help regarding access (oh plenty more questions to come I assure you...I can almost hear you both groan).

Truly professional and helpful... thanks again.

 

[beasleyd]

Uh oh.. you didnt think you could get rid of me that fast did you?

One last request has been made of me.

Okay, I'm displaying the Credits in a field that uses the above formulas we've been working on.

Is there a way that I can create a variable that totals the credits for each plan, and displays it at the end of the report? I know in C+ you could do this... like, defining 'x', then doing something like x = x + new_number, and keep doing that until the end, where you would display 'x'.

Is this possible?

 

[PaulBricker]

I'm just catching back up to this. Got called out of the office for a bit. Thanks Duane, never saw the ommission of the Fieldname.
If I understand you, what you would use is

=Sum(IIf([Plan_Type]="Special Life" Or [Plan_Type]= "Special DI" Or [Plan_Type]= "Special-LTC", Sum([Annual_Commission])/0.325*[Plan_Ratio], Sum([Annual_Commission])*[Plan_Ratio]))

Paul

 

[beasleyd]

Paul,

Okay, that threw an error...

The error:

Can't Have Aggregate Function In Expression

=Sum(IIf([Plan_Type]="Special Life" Or [Plan_Type]= "Special DI" Or [Plan_Type]= "Special-LTC", Sum([Annual_Commission])/0.325*[Plan_Ratio], Sum([Annual_Commission])*[Plan_Ratio]))

I didn't just copy/paste yours in my report, but it looks exactly the same, I just use "Special" on this forum because I don't know if I should be throwing actual product names around.

I have this in the "Report Footer" along with a sum of all commissions in a dollar amount, which works fine.

You've been helpful, might as well put my name out...

-David

 

[PaulBricker]

Sorry, David. It's been a busy afternoon here. I've got another meeting in a couple minutes so I'm assuming it doesn't like the Sum inside the Sum so try this.

=Sum(IIf([Plan_Type]="Special Life" Or [Plan_Type]= "Special DI" Or [Plan_Type]= "Special-LTC",[Annual_Commission]/0.325*[Plan_Ratio],[Annual_Commission]*[Plan_Ratio]))

I assume that you can do the calculations either way. Try this one. I'll be back from my meeting in about an hour and will check back in.

Paul

 

[beasleyd]

Paul,

Thanks again, that did the trick as far as I can tell.


I appreciate all your help, you've been a life saver

-David