query calculation problems

[papa jonah]

I have created a query (qryProactive) based on left joins among three
other queries. The resultant information is generally correct in that
I get the proper numbers from the different queries into the broader
query. My problem is that as the left joins allow nulls to be carried
into the global query (qryProactive), I am having difficulties with
calculations.
Thanks to Marsh, I have found a way to get zeros in place of the nulls.
Currently an example of the results of the qryProactive my look like
this:

Org Total Events Concerns Near Miss
Ops 12 12 0 0
Maint 25 20 0 5
Safety 5 1 3 1

The next step I want to do is to add a column that displays the
percentage of the total for Events and Concerns.

The result would look like:

Org Total Events % Concerns % Near Miss %
Ops 12 12 100% 0 0% 0
0%
Maint 25 20 80% 0 0% 5
20%
Safety 5 1 20% 3 60% 1
20%

I can't seem to get this to work for all orgs. It seems that as long
as there is a non-zero number in both the concerns and near miss
columns I can get a percentage calculated. If the concern or near miss
columns both have zeros, none of the percentages calculate for that
org. In the above example, the percentages for Ops would not appear
because both concerns and Near Miss are zero.

I am populating the columns (such as the number of concerns) with this:
Num 10A2: Sum(nz([qryc].[Num 10A2s],0)) as an expression (qryC counts
number of concerns per org)

This is an example of how I am attempting to do the percentage
calculation:
Concern %: Sum([qryc].[Num 10a2s]/[qryd].[num]) (qryD counts total
events per org)

Any ideas would sure be appreciated.

 

[Guest]

You aren't going to get a percentage if either your dividend or divisor is
zero. That's simple math. A division by zero will cause an error and dividing
into zero will always give zero.
If you could give an example of a percentage that is being calculated
incorrectly and what you expect the result to be, it might help.

Dorian

 

[Guest]

I am having a similar problem. I have 1 query called solve rate and one
called solve rate 2. The first one gives me a total number of cases assigned
to each individual. The second one solve rate 2 gives me the total number of
records that contain "UNKNOWN. The third query solve rate computation uses
the expression.....

Expr2: ([Solve Rate]![CountOfMPI CASE NUMBER]-[Solve Rate 2]![CountOfMPI
CASE NUMBER])/([Solve Rate]![CountOfMPI CASE NUMBER])

to give me a percentage, however when the solve rate 2 query does not find
"UNKNOWN" and gives a result of 0, then the percentage calcualtion does not
work and does not provide any result.

How do I make it so that when "UNKNOWN" is zero that it gives me the answer
of 100% which would be the correct answer.

You aren't going to get a percentage if either your dividend or divisor is
zero. That's simple math. A division by zero will cause an error and dividing
into zero will always give zero.
If you could give an example of a percentage that is being calculated
incorrectly and what you expect the result to be, it might help.

Dorian

I have created a query (qryProactive) based on left joins among three
other queries. The resultant information is generally correct in that
I get the proper numbers from the different queries into the broader
query. My problem is that as the left joins allow nulls to be carried
into the global query (qryProactive), I am having difficulties with
calculations.
Thanks to Marsh, I have found a way to get zeros in place of the nulls.
Currently an example of the results of the qryProactive my look like
this:

Org Total Events Concerns Near Miss
Ops 12 12 0 0
Maint 25 20 0 5
Safety 5 1 3 1

The next step I want to do is to add a column that displays the
percentage of the total for Events and Concerns.

The result would look like:

Org Total Events % Concerns % Near Miss %
Ops 12 12 100% 0 0% 0
0%
Maint 25 20 80% 0 0% 5
20%
Safety 5 1 20% 3 60% 1
20%

I can't seem to get this to work for all orgs. It seems that as long
as there is a non-zero number in both the concerns and near miss
columns I can get a percentage calculated. If the concern or near miss
columns both have zeros, none of the percentages calculate for that
org. In the above example, the percentages for Ops would not appear
because both concerns and Near Miss are zero.

I am populating the columns (such as the number of concerns) with this:
Num 10A2: Sum(nz([qryc].[Num 10A2s],0)) as an expression (qryC counts
number of concerns per org)

This is an example of how I am attempting to do the percentage
calculation:
Concern %: Sum([qryc].[Num 10a2s]/[qryd].[num]) (qryD counts total
events per org)

Any ideas would sure be appreciated.