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

 

[Douglas J. Steele]

Part of the problem sounds as though it's due to how you've designed your
tables. From the sounds of it, you're storing each score as a separate field
in a single row in one table. It shouldn't be that way: you should have one
row per student, and the scores each stored as a separate row (pointing back
to the appropriate student row) in a second table. Then, you can create a
query that selects the highest value for each test.

 

[Dirtyweeker]

I suspect you're correct about the faulty design - 2 further questions
How exactly do I go about 'pointing back to the appropriate student row'?
and
Is there a way to do this without having to reenter all the data? (Scary
thought)
Thanks for your help
DW

 

[Douglas J. Steele]

The second table has a field in it that points back to the student. Assuming
the Student table includes a primary key of "StudentId", you'd store the
StudentId in the second table as well (it's called a Foreign Key)

No, you won't have to reenter the data. Assuming your existing table looks
something like

StudentId BP1 BP2 BP3 BP4 ... BP8

you can write a query that normalizes that data along the lines of:

SELECT StudentId, "BP1" AS WhichTest, BP1 AS TestScore
FROM MyTable
UNION
SELECT StudentId, "BP2" AS WhichTest, BP2 AS TestScore
FROM MyTable
UNION
SELECT StudentId, "BP3" AS WhichTest, BP3 AS TestScore
FROM MyTable
....
UNION
SELECT StudentId, "BP8" AS WhichTest, BP8 AS TestScore
FROM MyTable

You'd then use that query to form an Update query to populate the new table.

Actually, stepping back for a minute, if you need to solve your problem in a
hurry, create the query I showed above, and base another query on it to
return the highest score. You can redesign your table tomorrow! <g>

 

[Dirtyweeker]

Many thanks for you expert help - as soon as I get into school tomorrow, I
will get onto this.
Much appreciated
DW