Need a query for the "Top 5" Scores

[mdgibbs88]

Hello All,

I am working on a database that will track points for BBQ Competitions. Only the top 5 highest points results are to be counted for each team. Team_ID is the Unique identifier for each team. I would like to sum the top 5 points finishes for each team and order them high to low.

Any thoughts on how to query this? I have attached a sample table.

 

[mdgibbs88]

Here is table in excel format

 

[Brianwarnock]

You will need to read this link to see how to do a top n by group, then you can sum them.

http://allenbrowne.com/subquery-01.html


Brian

 

[mdgibbs88]

Thanks for the lead Brian. I tried that method and it keeps returning more records than I seem to want. I made a duplicate table named Dupe for the Chicken_Points table and here is the query (I attached the results of the query to this post). Ultimately, I want to take the Top 5 results from each team and sum them. Any help would be greatly appreciated!

SELECT Chicken_Points.Event_ID, Chicken_Points.Team_ID, Chicken_Points.Points
FROM Chicken_Points
WHERE (((Chicken_Points.Points) In (Select TOP 5 Points From Chicken_Points AS Dupe
Where Dupe.Team_ID = Chicken_Points.Team_ID
Order by Dupe.Points DESC)))
Order by Points DESC;

 

[jdraw]

I made a duplicate table named Dupe for the Chicken_Points table

What exactly did you do?

The link says that if/when you use the same table in an sql statement (subquery in this case) you must use an alias. Doesn't say to create a new table.

 

[mdgibbs88]

What exactly did you do?

The link says that if/when you use the same table in an sql statement (subquery in this case) you must use an alias. Doesn't say to create a new table.

I guess I took this line in the Points to Note as I needed duplicate tables:

-Since we have two copies of the same table, we need the alias.

Programming is not my strength, so I was probably reading too much into the note.

 

[mdgibbs88]

Ok, I got rid of the "duplicate" table. Didn't need that after all. It appears that my problem is this, I want the top 5 scores from a list by team_number. If the scores in the database are 160, 140,135,135,135,135,135 the result of the query gives me ALL of the 135's. I only want to go down the physical 5th score and not have all of the additional 135's.

Does this make sense to anyone?

 

[plog]

I think this may be possible with queries alone--using a TOP 5 sub-query and linking that with a datasource of unique Team_ID values. But I couldn't figure that out. So I built a custom VBA function to order your data properly, then used it in a query to SUM the top 5 results for each Team_ID. Below is the function:

Public Function getScoreOrder(t, p, e) As Integer
    ' orders team (t) points (p) data based on points and then event (e)
    
Dim ret As Integer              ' will hold return value of function

ret = DCount("[Team_ID]", "Chicken_Points", "[Team_ID]=" & t & " AND [Points] >" & p)
    ' determines number of records with more points
    
ret = ret + DCount("[Team_ID]", "Chicken_Points", "[Team_ID]=" & t & " AND [Points] =" & p & " AND [Event_ID]>=" & e)
    ' determines number of records with same points but lower event ID

getScoreOrder = ret


End Function

Paste the above code into a module. Then use the below SQL to get your results:

SELECT Team_ID, Sum(Points) AS Top5Points
FROM Chicken_Points
WHERE (((getScoreOrder([Team_ID],[Points],[Event_ID]))<=5))
GROUP BY Team_ID
ORDER BY Team_ID;

 

[mdgibbs88]

Hello Plog,

Thank you so much for jumping in to help out with my question. I hate to admit it, but I am just not sure what to do with the code that you took the time to write for me.

I have attached a copy of my database to this thread. Can you take a look and give me some more direction? I think I am in the deep end of the pool and need a life preserver!

Regards,
Mark

 

[plog]

Copy my first set of code. In your database click on the 'Create' above the ribbon, then click on the arrow beneath the option in the Ribbon that says 'Macro' and click on the Module option. Paste my code into the form that pops up and then close it.

Next, click on Create, and then on the option that says 'Query Design'. Close the 'Show Table' dialog that automatically opens up. In the top left of the page just underneath the Home option, click on the button that says SQL view. In there, paste my second set of code. Run that query and it will produce the results you want.

 

[mdgibbs88]

Plog,

You are a hero! Thank you so much for your help.

If you are ever on the East Coast of the US look me up and I will feed you some top notch BBQ!

Mark