Software

Ask something
Discussion Locked

Access expression

0 helpful
7 replies

by lf king

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Need an expression that will average a spreadsheet based on evaluation results.
There are 15 field headers. Need to total all the scores in each field to report an average score. This months report is 14 rows, totaling 1007. When divided the result is 4.8 Example:
Organized Knowledge Voice Materials
4 5 4 4
5 5 5 5
5 4 5 5
I want the expression to average all the above numbers from each field to giveme one overall score.

7 replies

by andrew

for your 4 column example above:

SELECT (Avg(Organized)+Avg(Knowledge)+Avg(Voice)+Avg(Materials))/4
FROM Table1;

seems to work.

by lf king

I changed the formula to your solution and get Enter Parameter Value.
The formula reads:(Avg(Organization)+Avg(Design)+Avg(Knowledge)+Avg(Rapport)+Avg(Enthusiasm)+Avg(Voice)+Avg(Language)+Avg(use of materials)+Avg(professionalism)+Avg(Honesty)+Avg(Pacing)+Avg(Communication)+Avg(Topic)+Avg(Interaction)+Avg(Recommend))/15

by kees.valkenswaard

It is not quite clear what you have and what you exactly want. Do you have an Access database? Or is it another type? One can indeed use SQL. A good solution is to make a query. Use a summarising option by clicking the ? [sigma] button and use avg or sum on the totals line. In this case the group by option is not needed and both avg and sum can be calculated. If you want to have the overall average in the query use a formula like
AVG: ([Organized]+[Knowledge]+[Voice]+[materials]+....)/14 with 14 fields and select also avg on the totals line. Another formula could be to add all the fields in an expression and to use sum in the totals row. The query would give just two numbers, the overall average and the overall sum. Of course for individual fields a query could be made as well.
However, I would export everything to Excel [just 14 rows] with tools, Office links etc. In Excel it is very easy to calculate in one formula the average of a rectangular area.
Sucess!

This discussion is closed to new replies.