Average

[Guest]

How do I calculate an average for a field in a table?
I have a form linked to a table and it measures an individuals progression.
the scoring is 1a, 1b,1c 2a, 2b,2c and the same up to 6c. It measures a
persons progress in learning the saxophone. As it is a group i need to work
out an average for the group and show this in a field on a form. I am hoping
my pupils will progress rapidly so this field will change often. Is there an
easy way to show the group average and have it change as soon as data is
updated. I don't do VBA (yet) but will try anything else.

Many thanks

Landor

 

[Ken Snell \(MVP\)]

What would be the average value of 1a, 1b, 2a, 3b, and 6c? You'll need to
identify a numerical equivalency.

You use a Totals query to get an Average of a field's values.

This query gives you an average of all the values:

SELECT Avg(FieldName)
FROM TableName;


This query gives you an average for each "person":

SELECT PersonID, Avg(FieldName)
FROM TableName
GROUP BY PersonID
ORDER BY PersonID;

 

[Guest]

Ken is correct. You would need to attach scores for each grade but what
numbers you use will determine how you interpret the speed of progress. You
will have to think very carefully how you choose your scoring.

Having said that if you would rather keep to your a's, b's and c's then
depending on how often you give each person a score you may want to calculate
the number of times each grade is given to a person and score a percentage
for a given period (like every month or two month period). Then at the end of
each period progress is determined by whether each person has mostly one
grade rather than any other (if that makes sense).

 

[Guest]

Many thanks Ken. I understand where I have been going wrong. I can change how
I score my pupils. I think this will be the easiest solution as it is for my
records only. very much appreciated

landor