Odd averages result from query

[dxp]

I am trying to calculate the average patients age from 2671 records using this SQL:

SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, DAvg("[Age]","qryAvgAge") AS Average
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);

why am I getting the result:

68.1131066106

I would have thought that it would have been 68 a whole number, has anyone got any suggestions why this should be.

thanks

 

[gemma-the-husky]

it sounds like you are getting the right answer

can you format [average] as an integer, if you dont want the dps

 

[Moniker]

Like this:

SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, CInt(DAvg("[Age]","qryAvgAge")) AS Average
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);

You're getting decimals because that's what averages do, even if you're averaging integers.

68+68+68+69 = 273
Average = 273/4, which equals 68.25

 

[dxp]

thanks for that Gemma and Moniker, added the CInt bit to the SQL and got the whole number.

The importance of the calculation is so that I can target advertising to the right age group ie Saga magazine readers as opposed to trying a younger magazine. My database contains 12,000 records so far and it would have been impossible to work out the average age manualy, it will save me a lot of wasted money in dead advertising; hence the need for accuracy.

thanks again