Selecting only the best three

[Bomac]

Hello All,

I have a small problem which I feel I should be able to solve but the hours of trying are proving me wrong!

From a choice of 16 events, competitors can enter as many as they like providing they are eligible. For each event they receive performance marks which are duly stored in a table. Some compete in 1 event others in 5 or 6. (I do not have a control showing ‘number of events entered’ and have tried several options, all unsuccessful, to create a ‘count’ of events entered. Is there a way?) My main problem is, from each individuals’ records, how do I select only the highest 3 marks from the classes each individual entered.

I’d be very grateful for any advice/guidance.

Bomac.

 

[Jon K]

It is easier to demonstrate with an example. See the two queries in the database for the 'count of events entered' and 'the best three marks' of each individual.

Note: When there is a tie in the marks of an individual, more than three records will be returned for that individual.
.

 

[Bomac]

Select the best three

Hello Jon K,

Thanks for picking up my query and for the example you made available. I had a quick squint at your reply first thing this morning before going to work and I’m sure it mentioned something about table structure. My original submission gave all the details including table structure but on second reading I got cold feet thinking it was too detailed so withdrew it and re-wrote. I worry about how much information to supply.

The examples you provided would seem to do precisely what I want! To make them work, however, I would appreciate just a little more help on how to apply them to my situation. I have never come across the ‘In’ expression before and at the moment can’t see how to edit it to fit my query. I have looked it up in the books but none are very helpful. I shall continue the search when I’ve submitted this.

All my data, EntryNo, EventNo, Name and EventMark is contained in a single temporary table “tblEventMarks” created from the mother database specifically to work out the highest aggregate mark achieved from 16 particular Events. The table is then deleted. The records in the table are in order by EntryNo, Name, EventNo and EventMark.

Using your example against my table for the count of events entered all I got was a long column of 1s (ones). I haven’t been brave enough to try the select top three yet – but I will have a go and see what happens. I suspect having a single data source changes things a bit. If you can advise a bit further I would be very grateful.

Bomac

 

[Jon K]

Assuming your table "tblEventMarks" looks like this

EntryNo	EvenNo	Name	  EventMark
      1	     1	John Doe	55
      2	     2	John Doe	66
      3	     3	John Doe	77
      4	     4	John Doe	88
      5	     5	John Doe	99
      6	     6	John Doe	76
      7	     2	Jane Doe	82
      8	     2	John Smith	84
      9	     3	John Smith	85
     10	     6	John Smith	86
     11	     7	John Smith	87
     12	     1	John Smith	85

you can run these two queries:-

SELECT Name, Count(EntryNo) AS EventsEntered
FROM tblEventMarks
GROUP BY Name;

SELECT tblEventMarks.*
FROM tblEventMarks
WHERE EventMark IN (Select Top 3 EventMark from tblEventMarks as S where S.Name=tblEventMarks.Name order by S.EventMark desc)
ORDER BY Name, EventMark DESC;


We need the IN operator here instead of = because IN can accept more than one values. The = operator accepts only one value.

The three tables in my example are the standard structure for a many-to-many scenario. In your case, an individual can compete in many events and an event can be entered by many individuals. My table 'tblMarks', which is very similar to your table 'tblEventMarks', is sometimes called a junction table in a many-to-many situation.
.

 

[Bomac]

Select Top Three Only

Hello Jon K,

At the end of a rotten day I was so pleased to see that you had followed up with detailed advice on what I should do! I haven’t had time to try it yet but I am sure it will work fine.

I also intend to see if I can find out a bit more about the ‘In’ operator. Not much information is given in my manuals. Thanks for your patience and help.

Bomac

PS. Both the queries worked and did exactly what I wanted. Excellent. Thanks again