Divide By Zero Error

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

 

[KARL DEWEY]

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

 

[KARL DEWEY]

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.

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.

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.