Retun grouped results of a query

[cbenisch]

Attached is a database I am working on. "June 7" helped me clear one hurdle I had for which I very grateful. Now I am at the next and hopefully the last hurdle in this application. The database is attached. I have a query called TournamentRanking that returns all the scores based on gross score and then sorts backwards by hole to break ties to rank the scores from top to bottom. That was pretty straight forward.



What I am struggling with is that I need to "Flight" them and report the scores for each "Flight". Flighting is basically dividing the scores into thirds Flight A, Flight B and Flight C. Again this is pretty easy to do by looking at the data but doing programmatically seems very difficult.

For 36 teams there would be 3 Flights of 12, for 35 teams there would be two flights or 12 and one flight of 11, for 34 teams there would be one flight of 12 and two flights of 11 and so on.

I know I can use Top 33% to get the first flight but how to get the others is my challenge. I think it could be selecting the top 66% and linking it somehow to not include the results of the first query and then flight wouold like somehow to not include the results of either of the first two queries.

I thought about trying to add a field as part of the TournamentRanking Query that contained the RANK as created in that query but everywhere I read said that is a non no in databases so I am asking once again for someone to help me learn the correct way to do this so that I can add it to my knowledge base going forward.

I have also attached a spreadsheet showing what I mean by Flights. As I said it is each on paper but appears to be much harder in access to get to the same point.

ThanksGolf Tournament2.zip

[June7]

What you want is probably very difficult to accomplish programmatically.

Review http://allenbrowne.com/ranking.html

[cbenisch]

Nice or you to reply again

It looks like my best option after reading the link you listed will be to create a temp table with my query that I have that ranks the scores. I can use an autonumber field that will create the rank number for each score because the append query I will use to append to the table will sort them into the table correctly. My question to you then becomes how do I select the records from that table in thirds based on the total number of records in the table. I need to make sure the records pulled in the first are not also in the second third and the records from the second third are not in the last. I would have thought that part would have been easier than I am finding it to be or is this the part that you were saying would be hard.

Thanks again for helping the newbie!

[June7]

That is the part that is hard. Without criteria to identify the grouping, will require code that opens a recordset, counts the number of records, calculates the number of records for each 'grouping', assigns group number, saves records to temp table, increments the number, saves next 'group'. Requires looping structure.