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

[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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dirtyweeke r" <go****@gmail.c om> wrote in message
news:42******** **************@ ptn-nntp-reader02.plus.n et...

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

[Alan Webb]

Dirtyweeker,
You oughta datawarehouse yadda yadda yadda . . . I needed the last
cumulative total in my table for several numeric measures so I could
increment these numeric measures by a new amount. Since in my db a project,
an employee and a date represent a unique row I ended up writing sub-queries
for each total I needed and joining them by employee and project. What was
new & cool to me was that each of my subqueries was returning a single value
to the query in which they lived. One subquery per column as it turned out.
I don't know if my little success will suggest a solution that can work for
you but as I read your post I thought it might.
--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS"

"Dirtyweeke r" <go****@gmail.c om> wrote in message
news:42******** **************@ ptn-nntp-reader02.plus.n et...

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