Calculating Percentage with Null value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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!
 
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






Van T. Dinh said:
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!

--
HTH
Van T. Dinh
MVP (Access)



terryh70 said:
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.
Click to expand...
Click to expand...
 
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.
 
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%.


Van T. Dinh said:
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.

--
HTH
Van T. Dinh
MVP (Access)



terryh70 said:
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
Click to expand...
Click to expand...
 
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.
 
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];


Van T. Dinh said:
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.

--
HTH
Van T. Dinh
MVP (Access)



terryh70 said:
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%.
Click to expand...
Click to expand...
 
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.

--
HTH
Van T. Dinh
MVP (Access)



terryh70 said:
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];


Van T. Dinh said:
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.

--
HTH
Van T. Dinh
MVP (Access)



terryh70 said:
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%.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top