Average a subset of data

[Bill]

I have a database ASJ that contains fields of scores AS1, AS2, AS3, AS4, AS5,
AS6. I also have a field in the ASJ database that is the average score
(AVGSCORE) for the lowest 4 scores out of the aforementioned 6 scores. How
can I setup a query that updates the AVGSCORE field in the ASJ database after
each addition of scores?

 

[Jeff Boyce]

"How" depends on "what"...

Are you saying your table (in Access they're called tables, the whole thing
is a 'database') contains one column (i.e., 'field') per score? Right now
you have 6 scores and six "AS#" columns?

So, what are your intentions?!<g> What will you do if you suddenly have to
deal with seven scores? Or five?

Or your requirement for average takes into account the lowest 5 scores? Or
the "middle" 6? ... or ...?

You do realize, right, that you will have to revise every related table,
form, query, procedure, macro, and report that uses your "old" way to
calculate the average? That's a maintenance nightmare!

Instead, if you are using one column per score, consider the benefits of
normalizing your data a bit more. First and foremost, MS Access'
relationally-oriented features and functions work best when fed
well-normalized data. Second, you wouldn't need to revise your table
structure to accommodate a change in number of scores. Third, you'd be able
to make a change in one place to handle any new variation on calculating the
"average".

Or you could move the data to a spreadsheet and do the calculations there?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

You are doing a couple of things wrong.
You should not store a calulated value (AVGSCORE ) in the database.
You should not have 6 fields to record the scores. Use one for score and
another to define what the score represents.
Player Game Score
Bill Bskt 32
Bill Ftbl 7
Bill Scor 12
Joe Bskt 12
Joe Ftbl 8
Joe Scor 21

Use a union query to move your data into the correct table format.
SELECT Player, 1 AS Game, AS1 AS Score
FROM ASJ
UNION ALL SELECT Player, 2 AS Game, AS2 AS Score
FROM ASJ
UNION ALL SELECT Player, 3 AS Game, AS3 AS Score
FROM ASJ
UNION ALL SELECT Player, 4 AS Game, AS4 AS Score
FROM ASJ
UNION ALL SELECT Player, 5 AS Game, AS5 AS Score
FROM ASJ
UNION ALL SELECT Player, 6 AS Game, AS6 AS Score
FROM ASJ;

Then use that query as source for make table query into MyBestTable.

The use a Ranking in a Group query to get the lowest 4 per player.

SELECT Q.Player, Q.Game, Q.Score, (SELECT COUNT(*) FROM [MyBestTable] Q1
WHERE Q1.[Player] = Q.[Player]
AND Q1.[Score] < Q.[Score])+1 AS Rank
FROM MyBestTable AS Q
WHERE ((((SELECT COUNT(*) FROM [MyBestTable] Q1
WHERE Q1.[Player] = Q.[Player]
AND Q1.[Score] < Q.[Score])+1)<5))
ORDER BY Q.Player, Q.Game, Q.Score;

Then you can average the lowest 4 scores.