i want to generate a top 5 from a results table

[ninefoureight]

Hi there currently i have a table which has the fields

Member
Age Group
Disciplin
r_time
r_distance
r_note
date
competition
venue

what i would like is for a report to be generated that analyses the table and pulls out the top 5 performances for each age group in each discipline stating the 5 top performances members r_time OR r_distance depending which one is not NULL the date, competition and the venue.

Is this possible ? and how should i go about generating such a report .. thanks

 

[ninefoureight]

thanks Pat, ill have a play with that code in reference to my own database. Cheers!

 

[ninefoureight]

I have read the material you directed me to RE : selecting the top 5 however i am still at a loss at how to go about manipulating my own data to make the required querys and reports. Further to the example i have two instances which the query must select and choose the top 5. From the results table i want to generate a query and report that gives me the 5 longest distances or shortest times per age group per discipline. I have attached my database as is and would greatly appreciate if you could give me a helping hand. Thanks

 

[ninefoureight]

sry here it is

http://090480.net/_misc/clubrecords.zip

 

[raskew]

It would be helpful if, when preparing a file for download, you saved it in Access 97 format. That way, a broader range of respondents would be able to look at the problem.

 

[ninefoureight]

Just resaved it didnt give me the explicit option of having it as a access97 format so i hope its right. thanks

 

[ninefoureight]

i understand the top n values help but my problem is using it to to my double double query

see for each age group there are 10-20 disciplines and i need to have the query/report return the top1-5 of each age group per discipline if that makes sense?

ie i have 3 age groups u11 u13 u15 and 4 disciplines 100m 200m 300m 400m i need to report back

u11-100m record1 record2 record3
u11-200m record1 record2 record3
u11-300m record1 record2 record3
u11-400m record1 record2 record3

u13-100m record1 record2 record3
u13-200m record1 record2 record3
u13-300m record1 record2 record3
u13-400m record1 record2 record3

u15-100m record1 record2 record3

and so on and so forth

the problem gets more complex after this but if i could first work on this it would be a start.

the complex part is i have 2 results fields 1 is a number(time) field which i want to sort lowest(fastest first) when this field in the record is filled in the other result field the number(distance) field is empty

and when the second number(distance) field is occupied i want to sort highest(longest) first and accordingly the first number field is empty

appreciate any help possible, thanks

 

[ninefoureight]

so by that you are suggesting i merge the agegroup and discipline fields yes i will also want to be seperating the males from females so i would then concatenate all 3 to produce the required results upon sorting? Can i do this in a query to produce a new table of top5 records for each age group discipline and sex ?

not got time to practically test it out but is my theory correct ?

thanks

 

[ninefoureight]

SELECT a.*, a.member, a.r_time
FROM recordsorts AS a
WHERE (Select Count(*) from recordsorts where AgeDis=a.AgeDis and r_time >= a.r_time) Between 1 And 5
ORDER BY a.AgeDis, a.r_time;

ok i made a query to concatenates the three fields age group, sex and discipline in that order to produce the new field "AgeDis" in query "recordsorts" i then came up with the above top values query however when i run it nothing happens and i dont know why ? if i try to close access it says do i want to stop running the code etc ..

whats going on is my top values query right ? thanks

 

[ninefoureight]

one possibility could be in some records the r_time field is empty as it is not appropriate could this cause the system hang ? when the r_time field is not populated the r_distance field is..

im at a total loss and cant seem to see the light for the data .. thanks

 

[Jon K]

>
... when i run it nothing happens and i dont know why
<

I think Access was running the query. It took time to run a subquery against a fairly large table. Proper indexing might help.


Since there are duplicate Times or Distances in some "Ag Sex Discipline" groups, there is bound to be a tie in some groups. When this happens, using Count(*) Between 1 And 5 won't work because the count number for those records in a tie may become greater than 5. Besides, when you use r_time >= a.r_time in the subquery, you are actually looking for the longest rather than the shortest times.

Instead you can use Top 5 in the subquery. And you don't need to concatenate any fields.

As the times and the distances require two different criteria, you can do it with two SQL statements and Union them together as in the first query in the attached DB:

SELECT a.*
FROM [_08072003bu completeresults] AS a
WHERE [R_Time] In (Select Top 5 R_Time from [_08072003bu completeresults] where Ag=a.Ag and Sex=a.Sex and Discipline=a.Discipline order by R_Time)
UNION
SELECT b.*
FROM [_08072003bu completeresults] AS b
WHERE [R_Distance] In (Select Top 5 R_Distance from [_08072003bu completeresults] where Ag=b.Ag and Sex=b.Sex and Discipline=b.Discipline order by R_Distance Desc)
ORDER BY Ag, Sex, Discipline;


It seems Access doesn't allow formatting fields in a Union query in Design view. I have used a second query and formatted the Time and the Distance fields in Design view. So you can open the attached DB and run the second query.


Notes.
-- In order to meet the file size limit here, I have deleted some fields from your table and reduced the number of records to 3000.

-- As running subqueries takes time, I have added an AgSexDiscipline index to the table on the three fields: Ag,Sex,Discipline to speed up performance.   On my system, running the second query against the 3000 records without the index took about 1½ minutes. After indexing, it took less then 3 seconds.

-- Where there is a tie in a group, more than 5 records are returned e.g.
In SEN Male Discus, 6 records are returned.

 

[ninefoureight]

Thank you John thats exactly what i needed, made a mistake in my referencing to using another table but sorted that now. For the indexes do you make 3 seperate indexes ? Many thanks! :}

 

[Jon K]

No. It's one index using the three fields: Ag, Sex and Discipline
and in that order.

You can see how it is set in the attached DB by opening the table in design view and clicking on the Indexes button on the tool bar.

 

[ninefoureight]

thanks john i realised that almost the instant i posted, ive added the indexes now. My query takes roughly 30s to generate the results, not instant but its not a query i require to run regularly so the time is negligible.

Thanks for everyones help, no doubt ill be back when i next likely get stuck but hopefully i can help others with what ive learnt so far in the meantim, cheers!