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
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
