Dear Kate:
SELECT [Total Findings Query].[Date By Month],
([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]) /
([Sum Of SumOfTrue (+)] + [Sum Of SumOfTrue (-)] +
[Sum Of SumOfFalse (+)] + [Sum Of SumOfFalse (-)]) * 100
AS [Accuracy %],
[Sum Of SumOfTrue (+)] / ([Sum Of SumOfTrue (+)] +
[Sum Of SumOfFalse (-)])*100 AS [Sensitivity %],
(([Sum Of SumOfTrue (-)] / ([Sum Of SumOfTrue (-)] +
[Sum Of SumOfFalse (+)])) * 100) AS [Specificity %],
Year([Total Findings Query].[Date By Month]) * 12 +
DatePart('m', [Total Findings Query].[Date By Month]) - 1
AS Expr1,
[Total Findings Query].[SumOf# of Patient's seen],
[Total Calculations] ! [Total Stressed] /
[Total Findings Query]![SumOf# of Patient's seen] AS [Total Cath'd]
FROM [Total Findings Query]
INNER JOIN [Total Calculations]
ON [Total Findings Query].[Date By Month] = [Total Calculations].[Date
By Month]
GROUP BY [Total Findings Query].[Date By Month],
([Sum Of SumOfTrue (+)] + [Sum Of SumOfTrue (-)]) /
([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)] +
[Sum Of SumOfFalse (+)] + [Sum Of SumOfFalse (-)]) * 100,
[Sum Of SumOfTrue (+)] / ([Sum Of SumOfTrue (+)] +
[Sum Of SumOfFalse (-)])*100,
(([Sum Of SumOfTrue (-)] / ([Sum Of SumOfTrue (-)] +
[Sum Of SumOfFalse (+)]))*100),
Year([Total Findings Query].[Date By Month]) * 12 +
DatePart('m', [Total Findings Query].[Date By Month]) - 1,
[Total Findings Query].[SumOf# of Patient's seen],
[Total Calculations]![Total Stressed] /
[Total Findings Query]![SumOf# of Patient's seen]
ORDER BY Year([Total Findings Query].[Date By Month]) * 12 +
DatePart('m',[Total Findings Query].[Date By Month])-1
Whew!
Now, please look at each and every place where you have a division (/) in
your query. Each and every one will need a modification like the one I
proposed.
I tested the code I gave you, using 5 and -5 for the SumOfTrue and
SumOfFalse, so their sum is zero. That's why it would be dividing by zero,
right? Well, the code I wrote gives 0 for this, not an error. However,
every place in your code where you have division is a candidate for the
cause of this error.
If you change every one of these divisions it may fix the problem. If you
have trouble with that, I will consider looking at your actual database to
propose a solution, if you choose. Don't sent it yet, work at it more
first. You'll need instructions from me before sending it.
Tom Ellison
Kate said:
I saw in another posting where someone entered there entire SQL Statement
so
I will do that too to see if that helps.
SELECT [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue
(+)]+[Sum
Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100 AS [Accuracy %], [Sum Of
SumOfTrue (+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100 AS
[Sensitivity %], (([Sum Of SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]))*100) AS [Specificity %], Year([Total Findings
Query].[Date
By Month])*12+DatePart('m',[Total Findings Query].[Date By Month])-1 AS
Expr1, [Total Findings Query].[SumOf# of Patient's seen], [Total
Calculations]![Total Stressed]/[Total Findings Query]![SumOf# of Patient's
seen] AS [Total Cath'd]
FROM [Total Findings Query] INNER JOIN [Total Calculations] ON [Total
Findings Query].[Date By Month] = [Total Calculations].[Date By Month]
GROUP BY [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue
(+)]+[Sum Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue
(-)]+[Sum Of SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100, [Sum Of
SumOfTrue
(+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100, (([Sum Of
SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum Of SumOfFalse (+)]))*100),
Year([Total Findings Query].[Date By Month])*12+DatePart('m',[Total
Findings
Query].[Date By Month])-1, [Total Findings Query].[SumOf# of Patient's
seen],
[Total Calculations]![Total Stressed]/[Total Findings Query]![SumOf# of
Patient's seen]
ORDER BY Year([Total Findings Query].[Date By
Month])*12+DatePart('m',[Total
Findings Query].[Date By Month])-1;
Thanks
Kate
Tom Ellison said:
Dear Kate:
Please allow me to try again, maybe a bit more carefully:
Specificity %: IIf([Sum Of SumOfTrue (-)] + [Sum Of SumOfFalse (+)] = 0,
0,
[Sum Of SumOfTrue (-)] / IIf([Sum Of SumOfTrue (-)] + [Sum Of SumOfFalse
(+)] = 0, 1, [Sum Of SumOfTrue (-)] + [Sum Of SumOfFalse (+)] * 100))
I feel a bit better about it this time. 2 errors!
Tom Ellison
Kate said:
If I put another parenthesis at the end of the string, i get a box
asking
for
a parameter value for SUM.
Kate
:
Dear Kate:
I expect what it is telling you is that, in at least on case:
[Sum Of SumOfTrue (-)] + [Sum Of SumOfFalse (+)]
os zero.
I recommend this:
Specificity %: IIf([Sum Of SumOfTrue (-)] + [Sum Of SumOfFalse (+)],
0,
[Sum
Of SumOfTrue (-)] / IIf([Sum Of SumOfTrue (-)] + [Sum Of SumOfFalse
(+)]
=
0, 1, [Sum Of SumOfTrue (-)] + [Sum Of SumOfFalse (+)] * 100)
The above may wrap. Remove any newlines that are inserted when you
paste
it
in.
Tom Ellison
I am using the following calculation in one of my queries and receive
the
DIVISION IS ZERO error. How can I change calculation this to accept
the
answer as 0%?
I am not sure how or if I should use Nz.
Specificity %: (([Sum Of SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum
Of
SumOfFalse (+)]))*100)
Thanks.
