Average Function Not Working Properly

[djs45uk]

Hi all

I'm using the following SQL statement to calculate the average results in a table.

SELECT Avg(results.resultsQ1) AS AvgOfresultsQ1 FROM results WHERE (((results.participantCourseID)=23);

The answer it gives is 3. But the table looks like this:

4,4,3,2,3,4,4,4,3,3

Now if my maths is correct, the answer should actually by 3.4.

Please does anyone know how to fix this problem as it is a really important part of my database -- I've got these average calculations everywhere.

Any help is very much appreciated

Many thanks

Daniel

 

[Golom]

Most likely your data types are integers or longs so Vb os "helping you out" by doing integer arithemetic. Try computing your average as

SELECT Avg([COLOR=blue]cDbl([/color]results.resultsQ1[COLOR=blue])[/color]) AS AvgOfresultsQ1 

FROM results 

WHERE (((results.participantCourseID)=23);

 

[PHV]

You haven't changed any property of AvgOfresultsQ1 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886