Average function in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This should be pretty simple. In a query, I want to do an average across a
number of columns. I have tried the following:

Overall Average: Avg([Column 1], [Column 2], [Column 3])

I have tried many variations of this but it always comes back with an error.

I want it to return an average on the scores per person based on a table
that has the following columns.

1) Name, 2) Score 1, 2) Score 2, 3) Score 3

Any suggestions would be helpful.
Chris
 
Hi, Chris.

You are running into this problem because your table was not properly
designed (called "Normalized"). All of these scores should be in one
column, not spread among several columns, even if they represent scores for
different occasions. The "different occasion" should be identified by a
separate column (or set of columns).

For example, the following table structure and data would make it easy to
calculate an average for each student:

StudentName Score Exam
Fred 97 Mid-term
George 91 Mid-term
Ron 88 Mid-term
Fred 95 Final
George 89 Final
Ron 90 Final

The following query would average all scores for each student:

SELECT StudentName, Avg(Score) AS OverallAvg
FROM tblTests
GROUP BY StudentName;

One can create this in the Design View of a query by making it a "Totals"
query (View menu -> Totals) and changing the "Total:" row for the
StudentName field to Group, and the "Total:" row for the Score to Avg, then
creating an alias for this average by typing the following in the Score
field:

OverallAvg: Score

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Oops. Typo:

The last two paragraphs should read:

One can create this in the Design View of a query by making it a "Totals"
query (View menu -> Totals) and leaving the "Total:" row for the StudentName
field as Group By, and changing the "Total:" row for the Score to Avg, then
creating an alias for this average by typing the following in the Score
field:

OverallAvg: Score

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


'69 Camaro said:
Hi, Chris.

You are running into this problem because your table was not properly
designed (called "Normalized"). All of these scores should be in one
column, not spread among several columns, even if they represent scores for
different occasions. The "different occasion" should be identified by a
separate column (or set of columns).

For example, the following table structure and data would make it easy to
calculate an average for each student:

StudentName Score Exam
Fred 97 Mid-term
George 91 Mid-term
Ron 88 Mid-term
Fred 95 Final
George 89 Final
Ron 90 Final

The following query would average all scores for each student:

SELECT StudentName, Avg(Score) AS OverallAvg
FROM tblTests
GROUP BY StudentName;

One can create this in the Design View of a query by making it a "Totals"
query (View menu -> Totals) and changing the "Total:" row for the
StudentName field to Group, and the "Total:" row for the Score to Avg, then
creating an alias for this average by typing the following in the Score
field:

OverallAvg: Score

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


Chris said:
This should be pretty simple. In a query, I want to do an average
Click to expand...
across
a
number of columns. I have tried the following:

Overall Average: Avg([Column 1], [Column 2], [Column 3])

I have tried many variations of this but it always comes back with an error.

I want it to return an average on the scores per person based on a table
that has the following columns.

1) Name, 2) Score 1, 2) Score 2, 3) Score 3

Any suggestions would be helpful.
Chris
Click to expand...
Click to expand...
 
Back
Top