Union query causes average field decimals to be truncated or rounded

[hotbread]

I am trying to get the average of a field called 'score' which contains values to 1 decimal place (eg 421.2, 593.6, 648.5). When I do this with a single SELECT query, the resulting average score values are unrounded (up to as many as 26 decimal places). But when I do this in a union query joining 2 SELECT queries (because I want different groupings) the resulting average score values appear truncated (or rounded) to 5 decimal places. And when I do another union query joining 3 queries, the averages are rounded to the integer. Does anyone know why this is happening, and how I can retain the decimals in a union query?

 

[TheAceMan1]

How are ya hotbread . . .

Post the [blue]Union Query[/blue] with thee queries!

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hotbread]

Thanks for your reply AceMan!

Following is the basic query structure:

SELECT Code, Year, Gender AS Group, Avg(Score) AS Mean
FROM Table1
GROUP BY Code, Year, Gender

UNION ALL
SELECT Code, Year, Ethnicity, Avg(Score)
FROM Table1
GROUP BY Code, Year, Ethnicity

UNION ALL
SELECT Code, Year, Location, Mean
FROM Table2


The third query already contains grouped data, which is why I haven't grouped it.

 

[TheAceMan1]

hotbread . . .

[tt]Instead of Avg(Score)
Try Avg([purple]CDbl([/purple]Score[purple])[/purple])[/tt]

Your Thoughts ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]