Create Teams based on averages

[Steven.Allman]

Creating a golf database for the group of guys I play with.
This version is missing a few things, due to size reduction, but what I have right now...


I can select a player and it will input him into todays game, then close and reopen a report showing todays players and their average scores in the modified stableford we play.
What I would like it to do from there, is create a query where it asks me how many teams, I select 4 or 5 (depending on our number of players, etc) and it will create teams with very similar averages.

Is this way to complex for my advanced beginner Access brain?

Tom Mike Dave Joe averages add up to 50
Ben Mark Steve Dick averages add up to 50.

Would this be much easier if I just did this last step by hand?

[Micron]

It certainly is too much for me but it will be interesting to watch this thread. The idea of automatic mixing and matching just seems too complicated, but maybe that's because I've never even had to do it manually. Doing the calculations/grouping based on averages by writing sql or code just seems too daunting, given that any tournament might have as many as 3 three-player teams, so then what? You want to input a number (which is easy to do with a query parameter prompt) but you might have 38 players so you can't simply divide that by 4 and form teams of 9.5 players. In that scenario, you'd probably want eight 4's and two 3's.

In any tournament I've played in, there always seems to be cliques formed through the knowledge of player ability who then take the lowest team score prizes.

[Minty]

As a golfer, I did write a process (a complicated one) for this for doing Texas Scrambles teams in Excel a looooong time ago, and have often considered re-doing it in Access.
It would need to be process where you took the list of entrants and then divided them into teams of 4 with a remainder to be manually adjusted.

Gathering the teams is the interesting bit, as we were trying to have teams of similar playing abilities e.g. one low handicapper, a couple of mid range and one higher handicap team member.
It takes a lot of looping and temporary tables from memory, and would be a lot easier in SQL server where you have decent inbuilt windowing (ranking) functions.

I simply don't have the time to devote to it at the moment, but maybe the outline of the idea above would get you started.

[Micron]

I haven't bothered much with Excel outside of the vba side so I have to wonder if it isn't the better tool for this. There are some pretty powerful functions and number crunching tools in there but I wouldn't know where to begin. As always, if anyone is asking in a forum, I figure Google already knows about many people who have already figured this out.