calculate sum of data in "access database"

[Guest]

Hello, I have created a database including all students who have applied for
admissions at my school. I have included 4 fields that give the student a
"score" between 1 and 4 corresponding to their abilities in several different
areas, i.e., essay, GPA, assessment exams, and experience in the field. The
admissions committee would like me to create another field "Total Score" that
is a sum of these four areas creating a total score in which to compare each
applicant to the other.

Can anyone please help me with this? I have made the data type "number" for
the Total Score field. However, I can't find where I would put an expression
to tell the program to sum the fields I would like for each record.

 

[Tim Ferguson]

The
admissions committee would like me to create another field "Total
Score" that is a sum of these four areas creating a total score in
which to compare each applicant to the other.

SELECT CandidateNumber, FName, LName,
Score1 + Score2 + Score3 + Score4 AS TotalScore
FROM Candidates
ORDER BY 4 DESC;


The 4 in the order by clause refers to the fourth column (e.g. the
TotalScore) because it's easier than repeating the whole Score1 + Score2
etc etc stuff.


Incidentally, you might have been well advised to get these scores into a
new table of their own. What are you going to do if the panel wants to
know how many people scored two or more fours? Which score was highest
for each candidate?

All the best



Tim F

 

[peregenem]

SELECT CandidateNumber, FName, LName,
Score1 + Score2 + Score3 + Score4 AS TotalScore
FROM Candidates
ORDER BY 4 DESC;

The 4 in the order by clause refers to the fourth column (e.g. the
TotalScore) because it's easier than repeating the whole Score1 + Score2
etc etc stuff.

There's maintenance 'issue' associated with using ordinal position of
columns in this way but a good tip nevertheless. FWIW in standard SQL
you'd be able to use a correlation name (alias) in the SELECT clause
and use it in the ORDER BY clause.

Now if only your tip worked for a GROUP BY clause