Find highest and lowest number

[Hans Recter]

Hi

I have a table in Access 2000 with fields as:

Item id
Score1
Score2
Score3
Score4
Score5

I am trying to find the highest and lowest score for each item. I thought it
would be easy, and probably is, but I just don't seem to be able to get it.

Any help would be greatly appreciated.
Hans

 

[John Vinson]

Hi

I have a table in Access 2000 with fields as:

Item id
Score1
Score2
Score3
Score4
Score5

Then you have a misdesigned table. "Fields are expensive, records are
cheap" - this is a good spreadsheet design, but for a relational
database you should have two tables in a one to many relationship.

I am trying to find the highest and lowest score for each item. I thought it
would be easy, and probably is, but I just don't seem to be able to get it.

It's not very easy. You'll need a VBA function to parse through the
fields. Any chance you can normalize the data into two tables, so you
can just use a Totals query with Min and Max?

John W. Vinson[MVP]

 

[Hans Recter]

Thanks for your reply

This table is part of larger application which is used for judging
photographs. Each of the scores are from 5 different judges.

The scores are averaged to get the points that the photograph(Item_id) gets.

What I am trying to set up is an assment graph for each judge to show where
they scored in relation to the average, highest and lowest scores.

I hope I have explaned this correctly.

Hans

 

[David Cox]

so you have a table:

photograph_id
Judge_id
score
....

and you can run max, min, average, top values queries using this table as
input. (and many more beside)

and you can have as many judges as you like, and as many different judging
panels as you like.

Correct design pay off.