# Thread: Calculating the sum and average of fields (2002, SP2)

1. ## Calculating the sum and average of fields (2002, SP2)

Hi Gurus,

Access is not a strong point with me and I have a bit of a problem.

A number of our employees completed a "Skills Analysis" and the information has been captured in a table within an Access database. (It was done within Access as the database also contains information such as individual details, locations, departments, management, etc.) Each record, within this table contains the Employee ID and the score obtained on each question (57 questions covering 5 skill groups). I am now wanting to obtain, for each individual, an average percentage score for each skill group and an overall average score.

I have tried searching through various documentation and found loads of information on calculations involving fields across different records but, so far, I have been unable to find anything on performing calculations based on fields within one record.

Have I given myself an impossible problem?

Any advice would be gratefully appreciated.

Regards,

Ian

2. ## Re: Calculating the sum and average of fields (2002, SP2)

If possible, you should reorganize your data so that each score is a separate record. We could help with that, if necessary.

It is possible, but tedious, to calculate averages etc. across fields. I have attached code (originally from an example by Microsoft) with field aggregate functions.

3. ## Re: Calculating the sum and average of fields (2002, SP2)

Hi Hans,

If I understand you right, you would be suggesting that the score for each question, of which there are 57, should be a separate record. Does that mean that I would need a separate record per employee (currently 554) per question or should the table be turned on its end, each row being a question and each column being an employee? Or am I missing the mark altogether.

The key information is the employee as the database provides other static information about them and this can vary as and when an employee leaves or starts, etc.

Before I received your reply, one of my colleagues suggested a SQL statement along the lines of SELECT [A1]+[A2]....AS [TOTAL A] which seems to have worked. From that, it was relatively easy to create a calculated field with a formula which calculated the average. It is possibly not the most efficient way but it seems to have given the result required.

Regards,

Ian

4. ## Re: Calculating the sum and average of fields (2002, SP2)

The suggestion from your colleague will work, although it is not very flexible (but that is not your colleague's fault).

Changing the table structure may be more work than it's worth. If you were to do it, you would split the table into two:

- A table tblEmployees with info about the employees but no scores. It contains one record for each employee (so currently 554 records). You need some kind of unique EmployeeID, for example an AutoNumber field in this table.

- A table tblScores with three fields: EmployeeID, QuestionID and Score, with a unique key on the combination of EmployeeID and QuestionID. This table contains one record for each question for each employee, so it contains 554 x 57 records. That is a lot, but they are very small.

tblEmployees and tblScores are linked on EmployeeID (and presumably, there is a third table tblQuestions, linked to tblScores on QuestionID.)

Grouping, sorting and counting by question or by employee is very simple with tblScores.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•