How to add a calculated field (Quiz Total) in the following query

[al_mamun]

ST ID	SUB ID	Quiz 1	Quiz 2	Quiz 3	Quiz 4	Quiz 5	Quiz Total (Best 3 )
0111	Maths	9	9	9	10	10
0111	Eng. I	9	7	7	8	9
0111	Eng II	10	8.5	7	8	8.5
0111	Eng. Lit.	9.5	9.5	8	8.5	8

How can I calculate the Quiz Total field that is the addition of the best three quizzes.

[June7]

Adding 5 fields is a simple expression. Complication is the 'Best 3' requirement.

That is not normalized data structure. This link http://support.microsoft.com/kb/209839/en-us has various function for aggregating row data, however, it doesn't handle your 'Best 3' parameter. That will require more code.

Another method is with UNION query to get the data into a normalized structure then use that query as source for subsequent queries.

SELECT [St ID], [Sub ID], [Quiz 1] As Score, "1" As Quiz FROM tablename
UNION SELECT [St ID], [Sub ID], [Quiz 2], "2" FROM tablename
UNION SELECT [St ID], [Sub ID], [Quiz 3], "3" FROM tablename
UNION SELECT [St ID], [Sub ID], [Quiz 4], "4" FROM tablename
UNION SELECT [St ID], [Sub ID], [Quiz 5], "5" FROM tablename;

There is no wizard for UNION query, type or copy/paste in SQL View of query builder.

Then you need a query to select the top 3 http://allenbrowne.com/subquery-01.html#TopN and then a query to aggregate (or build a report).

BTW, recommend no spaces in names.