Calculating average from 3 smallest values ?

[pookie62]

Hi,

I'm using a mdb to set scores.
Usually there are 5 scores, now I would take the three smallest values from the five present, and calculate the average of those three.
The scores entered (in a form) then should be erased and replaced by this one new average value.
Is this possible with VBA ?
Most ideal would be to hook up a commandbutton with the code, so the guy entering the original scores has the choice whether he wants to use this function or not.

I know it's a lot what I want.. but have no idea if this is possible and where or how to start..

Appreciate your input, examples..!

 

[rubbernilly]

It is absolutely possible...

Here is the sql for getting the average you are looking for

SELECT Avg(Score) as AvgScore
FROM tblScore
WHERE Score in (SELECT Top 3 Score
FROM tblScore
WHERE StudentID = 'LimitFromForm'
ORDER BY Score);

That will return you one record with the average you are looking for. Once you have it, you can do anything you want with it.