Returning the maximum of two fields

[DrStephen]

I have two fields (Grade1, Grade2). How do I return the higher of the two grades?

Grade 1 Grade 2 High Grade
100 20 100
50 87 87

This has got to be a simple thing to do (unless, of course, you don't know how to do it).

Thanks.

 

[lespaul]

Is there some other field in the table? You're going to need a correlated query in order to compare Grade1 to Grade 2 within the same record. Maybe a student ID field or something?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lespaul]

And just as an FYI, storing two grades like this in a single record breaks normalization rules. If possible you should reconsider your design, read the Fundamentals document for more information on normalization.

leslie

 

[DrStephen]

The data are imported from another application. Both grades are contained in a single record in that application. Actually, the application, itself, is supposed to return the highest score on any exam taken, but it doesn't appear to be doing so. As a result, I have to program around it. I was looking for something similar to
high grade = maximum(grade1, grade2)

which is available in other applications (e.g., SAS).

 

[lespaul]

you'd have to write your own function to do that, it IS possible though.

leslie

 

[PHV]

High Grade: IIf([Grade 2]>[Grade 1],[Grade 2],[Grade 1])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DrStephen]

...worked like a charm, PH...Thanks.

 

[MichaelRed]

more generically, search these fora for basMaxVal. A number of simple aggregate fumctioms of this type are availanble trtoughout the fora and these is a faq which consolidates many of them.




MichaelRed