How can I set a query to give me a count of items averaged?

[Yellowbeard]

Hi group,
I have a data base table with custodial inspection scores.
I have a Crosstab query to take each team and average the score of
inspections for the month.
TEAM 2003/12 2004/1
100 2.67 2.22

etc.

Each team can have any where from 10 to 20 inspections.
How can have it report or have it tell how many inspections made up the
average per team?
I am not real good with access and am stumped at this point.

Also I get some averages that are 2.67 or 1.5 (which is good) but some are
2.996666666666678
I have tried to get the average down to a point and two digits but it does
not work. If I round it off it will round up to 3.0. Not right...........
rounding can not be it. It should be 2.99 hmmmmm???

(major newbie here) or so it seems sometimes.

I thank you for any help or pointing me in the right direction.
Martin
--

 

[John Spencer (MVP)]

Well, 3.0 is closer to 2.9966 than to 2.99 so if you round you should get 3.0.
However If you just want to truncate then try

CCur(Int(Avg(SomeField) * 100) / 100)

You can use a concatenated expression in your Crosstab to show two values in a
cell. The values will be strings and therefore you can't perform further math
on them.

CCur(Int(Avg(SomeField) * 100) / 100) & " - " & Count(SomeField)

Hope this helps you a bit.

 

[Yellowbeard]

The CCur part works, I went into Windows Control Panel , Regional settings
and selected no $ symbol.
I had to leave out the Avg part of your expression because I already had Avg
and Val for the crosstab function.
Now the & " - " & Count(SomeField) does not work. I do not understand.
error
cannot have aggregate function in expression
Any thoughts?
Martin

 

[Yellowbeard]

Actually Acess did this to the line

Expr2: Avg(CCur(Int([SCORE])*100/100))

 

[John Spencer (MVP)]

Not really. I am obviously not understanding what you are attempting.