MAX Value of similar but multiple fields

  • Thread starter Thread starter David
  • Start date Start date
D

David

Hello wise ones,

I have a table with fields
Student_Name
Age
1mileTime1
1mileTime2
2mileTime1
2mileTime2
3mileTime1
3mileTime2
on... and... on....

I am trying to put together a TOP 10 list of times by age. For example,
report will show top 10 1 mile times for age 15. Another report to show top
10 2 mile times for age 15 and so on......

Should I just throw the top 10 1mileTime1 times for AGE 15 and the top 10
1mileTime2 times all in one table and then the top10 from the table?

Is there a way to create an array or temporary dataset and then perform the
top 10 query on it?
 
Hi Davd.

Any time you have repleating fields like yours, it indicates that you need a
related table where you can enter the values as many records related to the
original one, instead of many fields. You can then solve the problem easily.

You already have a Student table, with fields:
StudentID AutoNumber primary key
Surname Text
Firstname Text
BirthDate Date/Time

Now you need another table to record the race results for the students.
Assuming that "1mile" means a race distance of 1 mile, this RaceResult table
will have fields like this:
StudentID relates to Student.StudentID
RaceDistance Number (1 mile, or 2 miles, or ...)
RaceTime Number (number of seconds.)

You can now create a query into both tables, and show the top 10 times for
any one age group and distance:
1. In query design, drag the RaceTime field into the grid.
In the Sorting row under this field, choose Ascending.

2. Open the Properties box (View menu.)
In the query properties, set the Top Value to 10.

3. Add the criteria to limit the age group. For example, under the BirthDate
field, you might enter:
Between DateAdd("yyyy", -16, Date()) And (DateAdd("yyyy", -15,
Date() -1)

4. Add the criteria to limit the distance.
 
I might add one more field to the RaceResult table - RaceDate. This Couldbe
used to calculate the age of the participant and would allow multiple years
of data to be stored. The other option would be to have an AgeCategory in
the RaceResult table. I say this because it may be important to know the
student's age (or age category) when the race is run.
 
Back
Top