Extracting highest value 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

 

[Guest]

-Create a new query
-Select your table containing the test fields
-Click on the Totals icon in the query toolbar
-Add the Pupil Id field, you will see a "group by" under the field after the
table name,
-Add your tests fields (BP1,BP2,..4)
-Change the "group by" under each of the tests fields to "Max"