Calculating Percentage with Null value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

terryh70 - 06 Dec 2005 10:29 GMT

I have one 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.

Reply to this Message

Van T. Dinh - 06 Dec 2005 10:59 GMT

Try:

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

(multiple lines on the post for clarity only)

Watch out for division by zeo!

Signature

HTH
Van T. Dinh
MVP (Access)

> I have one 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
[quoted text clipped - 14 lines]
> answer
> of 100% which would be the correct answer.

Reply to this Message

terryh70 - 06 Dec 2005 12:52 GMT

That did not solve my problem,I get the same result as before, but I think it
clarified where my problem lies and how to fix it when I can fix the
underlying problem in solve rate2 query.

The solve rate 2 query only gives a result of how many records contain
"UNKNOWN". and does not give any result if their are no "UNKNOWN" the query
solve rate 2 contains the following:

1
Field: PERSON
Table: input data
Total: Group By
sort:
Show:
Criteria: Like "*" & [ENTER PERSON] & "*"
or:

2
Field: PERSON
Table: input data
Total: Group By
sort:
Show:
Criteria: "UNKNOWN"
or:

3
Field: CASE NUMBER
Table: input data
Total: Count
sort: Ascending
Show:
Criteria:
or:

which only gives me a result if "UNKNOWN" is found but does not give a
result if "UNKNOWN" is not found

> Try:
>
[quoted text clipped - 24 lines]
> > answer
> > of 100% which would be the correct answer.

Reply to this Message

Van T. Dinh - 06 Dec 2005 13:33 GMT

Post details of your Tables and the SQL of all the relevant Queries. I
can't see your database and can only guess from what you described on the
post.

Signature

HTH
Van T. Dinh
MVP (Access)

> That did not solve my problem,I get the same result as before, but I think
> it
[quoted text clipped - 35 lines]
> which only gives me a result if "UNKNOWN" is found but does not give a
> result if "UNKNOWN" is not found

Reply to this Message

terryh70 - 07 Dec 2005 14:06 GMT

Query: solve rate, contains:

1
Field: NAME
Table: input data
Total: Group by
sort:
Show:
Criteria: Like "*" & [ENTER NAME] & "*"
or:

2
Field: MPI CASE NUMBER
Table: input data
Total: Count
sort:
Show:
Criteria:
or:

3
Field: Expr1: MPI CASE NUMBER
Table: input data
Total: Sum
sort:
Show:
Criteria:
or:

Query: solve rate 2, contains:
1
Field: NAME
Table: input data
Total: Group By
sort:
Show:
Criteria: Like "*" & [ENTER NAME] & "*"
or:

2
Field: PERSON
Table: input data
Total: Group By
sort:
Show:
Criteria: "UNKNOWN"
or:

3
Field: MPI CASE NUMBER
Table: input data
Total: Count
sort: Ascending
Show:
Criteria:
or:

Query: solve rate computation, contains:

1
Field: Expr2: ([Solve Rate]![CountOfMPI CASE NUMBER]-Nz([Solve Rate
2]![CountOfMPI CASE NUMBER],0))/([Solve Rate]![CountOfMPI CASE NUMBER])
Table:
Total: Group by
sort:
Show:
Criteria:
or:

2
Field: NAME
Table: solve rate
Total: Group by
sort:
Show:
Criteria:
or:

input data table contains

MPI CASE NUMBER DATE RECEIVED PERSON    NAME
MPI-0001-2005    01-Jan-05    MARTINEZ, JOSEPH FOX
MPI-0002-2005    03-Jan-05    UNKNOWN JONES
MPI-0003-2005    01-Jan-05    JOHNSON, TIM A. FOX
MPI-0004-2005    03-Jan-05    UNKNOWN JONES
MPI-0005-2005    01-Jan-05    MARTINEZ, ROSEMARY FOX
MPI-0006-2005    03-Jan-05    KLEIN, MARTIN E. JONES
MPI-0007-2005    04-Jan-05    MARSHALL, TINA A. JONES

MPI Case Number is primary key duplicates not allowed

Query: solve rate2, does not give a result if FOX is found in the name
column of Table: input data and "UNKNOWN" is not found in person field of
Table: input data. It does give a result of 50% if "Jones" is found in name
column and "UNKOWN" is found in the person column, which is the desired
result.

If I enter the name JONES in Query: solve rate computation, I get a
percentage result of 50%, if I enter the name Jones in Query: solve rate
computation, I get no result. If the word “UNKNOWN” does not appear in the
person column but FOX appears in the name column, of Table: input data, the
desired result would be 100%.

> Post details of your Tables and the SQL of all the relevant Queries. I
> can't see your database and can only guess from what you described on the
[quoted text clipped - 39 lines]
> > which only gives me a result if "UNKNOWN" is found but does not give a
> > result if "UNKNOWN" is not found

Reply to this Message

Van T. Dinh - 07 Dec 2005 20:43 GMT

Very hard to read this way.

Open the DesignView of each Query. Use the first Toolbar ComboBox in the
"QUERY Design" Toolbar to switch to the SQL View (or the Menu View / SQL
View). Copy & paste the SQL to the post.

Signature

HTH
Van T. Dinh
MVP (Access)

> Query: solve rate, contains:
>
[quoted text clipped - 100 lines]
> the
> desired result would be 100%.

Reply to this Message

terryh70 - 08 Dec 2005 06:55 GMT

Solve rate query

SELECT [input data].[INVESTIGATOR ASSIGNED], Count([input data].[MPI CASE
NUMBER]) AS [CountOfMPI CASE NUMBER], Sum([input data].[MPI CASE NUMBER]) AS
Expr1
FROM [input data]
GROUP BY [input data].[INVESTIGATOR ASSIGNED]
HAVING ((([input data].[INVESTIGATOR ASSIGNED]) Like "*" & [ENTER INV NAME]
& "*"));

Solve rate 2 query

SELECT [input data].[INVESTIGATOR ASSIGNED], [input data].[SUBJECT(S) /
SUSPECT(S) NAME (1)], Count([input data].[MPI CASE NUMBER]) AS [CountOfMPI
CASE NUMBER]
FROM [input data]
GROUP BY [input data].[INVESTIGATOR ASSIGNED], [input data].[SUBJECT(S) /
SUSPECT(S) NAME (1)]
HAVING ((([input data].[INVESTIGATOR ASSIGNED]) Like "*" & [ENTER INV NAME]
& "*") AND (([input data].[SUBJECT(S) / SUSPECT(S) NAME (1)])="UNKNOWN"))
ORDER BY Count([input data].[MPI CASE NUMBER]);

Solve rate computation query

SELECT ([Solve Rate]![CountOfMPI CASE NUMBER]-[Solve Rate 2]![CountOfMPI
CASE NUMBER])/([Solve Rate]![CountOfMPI CASE NUMBER]) AS Expr2, [Solve
Rate].[INVESTIGATOR ASSIGNED]
FROM [Solve Rate], [input data], [Solve Rate 2]
GROUP BY ([Solve Rate]![CountOfMPI CASE NUMBER]-[Solve Rate 2]![CountOfMPI
CASE NUMBER])/([Solve Rate]![CountOfMPI CASE NUMBER]), [Solve
Rate].[INVESTIGATOR ASSIGNED];

> Very hard to read this way.
>
[quoted text clipped - 106 lines]
> > the
> > desired result would be 100%.

Reply to this Message

Van T. Dinh - 11 Dec 2005 04:17 GMT

Your final Query will take forever to run if you have a fair number of
Records in the Table since you did not specify any join. In effect, you
have double cross-join so if the [input data] hass 1000 rows, the [solve
rate] has 500 rows and the [solve rate 2] has 200 rows, you will end up with
1000 x 500 x 200 rows returned by your final query!

How do you relate Table [input data] and the 2 queries [solve rate] and
[solve rate 2]?

I am guessing you want relate them per [Investigator] ??? but this fact is
NOT included in your final query construction.

BTW, you should not include spaces or special characters in names for
Fields, Tables, Queries, ... Spaces and special characters only make it
harder to construct Queries, SQL, and later VBA code.

If you have an Access book, read up the chapter on queries. Check Access
Help / your Access book(s) on the DCount() function which may be simpler for
you.

Signature

HTH
Van T. Dinh
MVP (Access)

> Solve rate query
>
[quoted text clipped - 144 lines]
>> > the
>> > desired result would be 100%.

Reply to this Message