Extracting highest values from a set of fields?

[Dirtyweeker]

Hi,
I have a database which records fitness test results of pupils.

There are the usual name fields and then a series of fields holding results,
e.g. field BP1 and field BP2; each of these fields holds a test result in
numerical form. Let's assume BP1 value is 55 and BP2 value is 57.

My problem is that I want to have a report which will only give me the
highest result for that test for each pupil; I need to be able to extract
the highest score in each of the four tests. Some pupils have nil results
for some tests.

As the pupils progress through the school they could end up with a maximum
of 8 scores for each test, I still only want their highest score for each
test in a report.

I have spent hours trying to do this and I would greatly appreciate help.
I will happily email an extract of the database if that helps.
Thanks
DW

 

[Allen Browne]

The best solution to this problem would be to create a related table to hold
the test results: one per *record*. So the fields would be:
StudentID Who did this test.
TestID Which test it is
Score What result the student achieved.
It is then very easy to get the highest score in a test, or across all
tests, of a student or ...

If you want to stay with your existing fields, see:
MinOfList() and MaxOfList()
at:
http://allenbrowne.com/func-09.html