Sort by sum of multiple fields

[Guest]

I have a database that I want to run a query off and sort by the sum of
people's scores in round 1 - 4, so that I always have the top aggregate score
producers at the top, and then able to enter other information about them in
the subsequent fields.

When I write a custom expression such that SumofScores:
Sum([Round1]+[Round2]...) it gives me errors that the other fields aren't
part of an aggregate expression. Is there a way to get around this?

 

[Guest]

You don't need to use the Sum function here, just add the values of the
columns. The Sum function is for summation over a set of rows not a set of
columns. So the query would be something along these:

SELECT FirstName, LastName,
Round1+Round2+Round3+Round4 As TotalScore
FROM YourTable
ORDER BY Round1+Round2+Round3+Round4 DESC;

Note that in the table design you should give each Round# column a default
value of 0 and set the Required property to True (Yes). Otherwise, if no
score is entered in one round for some reason, the column would be NULL and
when you use NULL in an arithmetical expression the result will be NULL
regardless of the other values. In the jargon its said that 'Nulls
propagate'.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks, very helpful for a novice such as myself.

You don't need to use the Sum function here, just add the values of the
columns. The Sum function is for summation over a set of rows not a set of
columns. So the query would be something along these:

SELECT FirstName, LastName,
Round1+Round2+Round3+Round4 As TotalScore
FROM YourTable
ORDER BY Round1+Round2+Round3+Round4 DESC;

Note that in the table design you should give each Round# column a default
value of 0 and set the Required property to True (Yes). Otherwise, if no
score is entered in one round for some reason, the column would be NULL and
when you use NULL in an arithmetical expression the result will be NULL
regardless of the other values. In the jargon its said that 'Nulls
propagate'.

Ken Sheridan
Stafford, England

I have a database that I want to run a query off and sort by the sum of
people's scores in round 1 - 4, so that I always have the top aggregate score
producers at the top, and then able to enter other information about them in
the subsequent fields.

When I write a custom expression such that SumofScores:
Sum([Round1]+[Round2]...) it gives me errors that the other fields aren't
part of an aggregate expression. Is there a way to get around this?