Field Values in Order

R

Ripper

I have a table, tblCogatScores, that contains 4 fields (LocalID, VScore,
NScore, and QScore) These scores are different for each student. I need to
get them in numerial order (highest to lowest). Is there a way in a query to
maiplulate the values to get them in that order? It might be V,N,andQ for 1
kiddo and N,Q, and V for another.
 
G

George Hepworth

A normalized table design will make this a much easier task. You have what
is called a "repeating group", (VScore, NScore and QScore). These should not
be seperate fields in your table.

Your table should have a field called "ScoreType", or something similar.
This field will contain one of three possible values "V", "N" or Q". Your
table should have a second field called "Score" in which the actual scores
are stored.

Then it is a rather straightforward query to generate the results you want:

Select LocalID, ScoreType, Score
From tblCogatScores
Order By LocalID, Score Desc;

This is just one instance where a properly normalized table creates less
work in the long run.

HTH

George
 
K

KARL DEWEY

I am not sure but I think this is what you want.
SELECT Ripper.LocalID, Ripper.VScore, Ripper.NScore, Ripper.QScore
FROM Ripper
ORDER BY
IIf([VScore]>[NScore],[VScore],IIf([NScore]>[QScore],[NScore],[QScore])) DESC;
 
Top