Averages problem (1 Viewer)

[PeterWieland]

I have a query that calculates percentages as follows

SELECT dbo_tblStudents.RollNumber, dbo_tblStudents.YearGroup, dbo_tblStudents.Absence1, dbo_tblStudents.NoPoss1, ([NoPoss1]-[Absence1])/[NoPoss1]*100 AS [Absence1%]
FROM dbo_tblStudents;

I have called this query PercentAttend
This works fine.

I now want another query that will take the output from this first query, and produce the average percentage attendance for each year group like so

SELECT PercentAttend.YearGroup, Avg(PercentAttend.[Absence1%]) AS [AvgOfAbsence1%]
FROM PercentAttend
GROUP BY PercentAttend.YearGroup;

What I expect to get is something like this:

YearGroup AvgOfAbsence1%
7 86
8 90
9 89
10 93
11 92

When I run this query, I get the following error

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Divide by zero error encountered.(#8134)[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation. (#8153)

What does that lot mean. I have tried putting the error message into Microsoft Support website and I get no document found. The help button on the message box tells me that the ODBC Database is not connected, but I know that it is because I can run other queries.

Anyone have any ideas?

 

[PeterWieland]

Solved this one myself, almost by accident.

The calculated field in the first query was producing 1 result out of 1600 that was #error due to bad data. I was just scrolling through the results looking for inspiration and happend to spot it.