Divide By Zero Error

  • Thread starter Thread starter Wolf94800
  • Start date Start date
W

Wolf94800

I have this:

SELECT [Employee Identification].[Last Name],
(Sum([Alpha].[IV_Success]))/Sum([Alpha].[IV_Attempt]) AS [IV Avg],
IIf([Alpha.[IO_Attempt]=0,Null,(Sum([Alpha].[IO_Success]))/(Sum([Alpha].[IO_Attempt])))
AS [IO Avg], (Sum([Alpha].[ETT_Success]))/(Sum([Alpha].[ETT_Attempt])) AS
[ETT Avg]
FROM [Employee Identification] INNER JOIN Alpha ON [Employee
Identification].Medic_Number = Alpha.Medic_Number
GROUP BY [Employee Identification].[Last Name], [Employee
Identification].Title, Alpha.Medic_Number
HAVING ((([Employee Identification].Title)<>"Basic"))
ORDER BY [Employee Identification].[Last Name];

I want to be able to have the averages with no entries be displayed as zero,
but can't figure out where to fit the IIf statement in.

Thanks For Any Help.
 
Try changing like this --
HAVING ((([Employee Identification].Title)<>"Basic")) AND
Sum([Alpha].[IV_Success])<>0 AND
Sum([Alpha].[IV_Attempt]) <>0 AND
Sum([Alpha].[IO_Success]) <>0 AND
Sum([Alpha].[IO_Attempt]) <>0 AND
Sum([Alpha].[ETT_Success]) <>0 AND
Sum([Alpha].[ETT_Attempt]) <>0
 
Or did you want this --
SELECT [Employee Identification].[Last Name],
IIF((Sum([Alpha].[IV_Success]))/Sum([Alpha].[IV_Attempt])=0,"",(Sum([Alpha].[IV_Success]))/Sum([Alpha].[IV_Attempt]))
AS [IV Avg],
IIF(IIf([Alpha.[IO_Attempt]=0,Null,(Sum([Alpha].[IO_Success]))/(Sum([Alpha].[IO_Attempt])))=0,"",IIf([Alpha.[IO_Attempt]=0,Null,(Sum([Alpha].[IO_Success]))/(Sum([Alpha].[IO_Attempt]))))
AS [IO Avg],
IIF((Sum([Alpha].[ETT_Success]))/(Sum([Alpha].[ETT_Attempt]))=0,
"",(Sum([Alpha].[ETT_Success]))/(Sum([Alpha].[ETT_Attempt]))) AS [ETT Avg]

--
Build a little, test a little.


KARL DEWEY said:
Try changing like this --
HAVING ((([Employee Identification].Title)<>"Basic")) AND
Sum([Alpha].[IV_Success])<>0 AND
Sum([Alpha].[IV_Attempt]) <>0 AND
Sum([Alpha].[IO_Success]) <>0 AND
Sum([Alpha].[IO_Attempt]) <>0 AND
Sum([Alpha].[ETT_Success]) <>0 AND
Sum([Alpha].[ETT_Attempt]) <>0

--
Build a little, test a little.


Wolf94800 said:
I have this:

SELECT [Employee Identification].[Last Name],
(Sum([Alpha].[IV_Success]))/Sum([Alpha].[IV_Attempt]) AS [IV Avg],
IIf([Alpha.[IO_Attempt]=0,Null,(Sum([Alpha].[IO_Success]))/(Sum([Alpha].[IO_Attempt])))
AS [IO Avg], (Sum([Alpha].[ETT_Success]))/(Sum([Alpha].[ETT_Attempt])) AS
[ETT Avg]
FROM [Employee Identification] INNER JOIN Alpha ON [Employee
Identification].Medic_Number = Alpha.Medic_Number
GROUP BY [Employee Identification].[Last Name], [Employee
Identification].Title, Alpha.Medic_Number
HAVING ((([Employee Identification].Title)<>"Basic"))
ORDER BY [Employee Identification].[Last Name];

I want to be able to have the averages with no entries be displayed as zero,
but can't figure out where to fit the IIf statement in.

Thanks For Any Help.
Click to expand...
Click to expand...
 
Back
Top