average function

[Guest]

I have a database similar to:
Last Name:
Week1Grade:
Week2Grade:
Week3Grade:

I want to average the three grades for each student listed. I went to
update query but cannot make anything work. How do I build this query?

 

[Guest]

Make your table like this
Name
Grade
Date or WeekNo

Then create a query that will pull records off that table.
Select the Sum function on the toolbar looks like the the Greek Sigma
character.
underthe Grade column select AVG.

That will give you an average grade. you can then call that number in a
form or whatever.

 

[Michel Walsh]

Hi,


SQL works well vertically, not horizontally. That is the reason behind the
"design" table suggestions.

Make a query that will "normalize" your data:

SELECT LastName, Week1Grade As grade, 1 As weekNumber FROM myTable
UNION ALL
SELECT LastName, Week2Grade, 2 FROM myTable
UNION ALL
SELECT LastName, Week3Grade, 3 FROM myTable



then it is easy to get what you want:

SELECT LastName, AVG(grade)
FROM previousSavedQuery
GROUP BY LastName



Quite easy, when the table design "flows" within the tool "expectations"




Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Normalize your data structure or use a VBA function.

Your structure should be - Table Grades where you have one record for each
grade
StudentID
WeekNumber
Grade

Then you could use a simple totals query to get the results - that would
look like
SELECT StudentID, Avg(Grade) as Average
FROM [Grades]
GROUP By StudentID

You can use a UNION query to fix your data. Save a query like this
SELECT LastName, Week1Grade
FROM YourTable
UNION ALL
SELECT LastName, Week2Grade
FROM YourTable
UNION ALL
SELECT LastName, Week3Grade
FROM YourTable

Then you can use that to get the average
SELECT LastName, Avg(Week1Grade)
FROM [savedUnionQuery]
GROUP BY LastName

====
Perhaps you can do this all in one.
SELECT LastName, Avg(Week1Grade)
FROM
(SELECT LastName, Week1Grade
FROM YourTable
UNION ALL
SELECT LastName, Week2Grade
FROM YourTable
UNION ALL
SELECT LastName, Week3Grade
FROM YourTable) as Normalized
GROUP BY LastName


Finally, if you cannot fix your structure , you can use the function below.
Paste it into a module and save the module with some name other than
fGetMeanAverage.

Add a calculated column to your query.
Field: GradeAvg: fGetMeanAverage([Week1Grade],[Week2Grade],[Week3Grade])

'============ Function Starts ===========
Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0) returns 3
(21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
fGetMeanAverage = dblSum / intElementCount
'At least one number in the group of values
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function

'============ Function ends ===========

 

[John Vinson]

I have a database similar to:
Last Name:
Week1Grade:
Week2Grade:
Week3Grade:

Then you have an incorrectly structured table.

Storing data such as a week number in a fieldname is *simply wrong*.

You should have *three* tables here:

Students
StudentID Primary Key <autonumber or an assigned unique ID>
LastName
FirstName
<other bio data about the student>

Weeks
WeekNo Integer <Primary Key>
<information about the materials covered during this week>

Grades
StudentID Long Integer <link to Students>

I want to average the three grades for each student listed. I went to
update query but cannot make anything work. How do I build this query?

If you're trying to store the average, don't do that either! Just
normalize your tables, as above, and use a Totals query to Group By
StudentID and Average the grades.

John W. Vinson[MVP]