Calculation in Report 3

[Spyridon]

Hi,

I'm trying to calculate a few fields in a report. All I need to do is find the total number of sales that are finished, and the total number of sales that are canceled for any certain employee. Then I need to calculate a percentage based on the completion of their sales.
I have 3 different text boxes,

1. txtFinishedSales
2. txtCanceledSales
3. txtCompletionPercent

Both txtFinishedSales and txtCanceledSales are based off the same source in a query called "Status". What I have as the control source for 1 is
=Abs(Sum([Status]="Finished&quot)

and for 2 I have
=Abs(Sum([Status]="Canceled&quot).

I'm having to use the abs function because in the table the field value is based off of a check box which gives me either a -1 value or a 0 value.

Both of these values come up fine, the problem I am having is calculating the percent. in my 3rd text box I have tried putting all kinds of combinations, but nothing has worked yet. Basically I need it to be something like

sum((txtFinishedSales + txtCanceledSales)/ txtFinishedSales)
in txtCompletionPercent

But at best, no matter which way I've tried it, I get a parameter box asking for the value of txtFinishedSales and txtCanceledSales.... If anyone could help with this it would be much appreciated.

Michael

 

[CosmoKramer]

Michael,

Unfortunately, Access doesn't allow you to perform calculations on calculated controls. You will have to re-calculate them to get your result. The control source for txtCompletionPercent should be something like this:

=(Abs(Sum([Status]="Finished")) + Abs(Sum([Status]="Canceled"))) / Abs(Sum([Status]="Finished"))

 

[Spyridon]

Awesome, that worked out great, except that if an employee has 0 finished sales and 0 canceled sales, I get the output #Num! for him. Any simple way around that?

 

[CosmoKramer]

Oh yeah, the old "divide by zero" trick....

Try this, if Abs(Sum([Status]="Finished&quot) equals 0, a 0 will be returned, otherwise the calculated value will be:

=IIf (Abs(Sum([Status]="Finished&quot) = 0, 0, (Abs(Sum([Status]="Finished&quot) + Abs(Sum([Status]="Canceled&quot)) / Abs(Sum([Status]="Finished&quot))

Replace the red 0 with something else if you don't want a zero to display.....

 

[Spyridon]

It might sound funny, but what you wrote actually worked so well, that I found that some other calculations in my report, the ones that I thought were easy... Well, they looked good at first but upon closer inspection... ugghh.

So now I'm studying what you wrote.. To much more of this and I'm going to be able to see the red haired girl in the matrix for myself...

 

[Spyridon]

what if....

what if I had a query that had 3 order types, Basic, ExBasic, and HSI. You can either pick Basic or ExBasic, but you can have HSI by itself or with either of the other two. There is also a field called Status, which has 3 options, Open, Canceled, or Finished.

The reason I ask, is that I found out that if I just count or sum up all the orders by "Finished", or even sum (*) in Status, that I am leaving out Sales that also have HSI in them. So I need some kind of SQL statment like
"Sum(Basic) + Sum(ExBasic) + Sum(HSI) WHERE Status = "Finished""

But if you can't tell from my syntax, I obviously can't figure out how to do it! Could you please be a kind, kind, good hearted person and extend your helpfulness out a little bit more?

Michael
(who doesn't usually beg, but is getting more and more used to it when it Access doesn't play nice.)

 

[dhookom]

You need to review responses especially CosmoKramer's and note that Abs(Sum(---your expression---)) counts the number of times the expression is true. Can you find an expression for your "business logic" that evaluates to either true or false? If so, just use the simple "template". If not, come on back.

Duane
MS Access MVP

 

[wolfbrother]

Can someone help me out with this particular principle..i feel like i'm missing something...
I have used the example posted above on my particular fields,(yes i know they aren't standard naming conventions...someone elses database that I'm trying to work with here) and have come up with the expression below.

However when it runs...I have a total generated of 365 records i've tested the first part of the equation and get that number, the second is calculated in another field separately as well and it gives me 359, yet when it divides it generates 359.016 yata yata yata...help me please?

=Abs(Sum([ppo par/ non par]="Y&quot)+Abs(Sum([ppo par/ non par]="N&quot)/Abs(Sum([ppo par/ non par]="Y&quot)

 

[dhookom]

Are you missing some ()s?
Consider the two equations:
4+2/4 = 4.5
and
(4+2)/4 = 1.5
Same numbers but much different result.

Duane
MS Access MVP

 

[wolfbrother]

Thanks Duane, I appreciate the post back, I realized my math error about 5 minutes after I posted, needless to say I'm a little redfaced at the moment...but thank you.