Trying to Work out Formula

[$t3ff]

I have created a relational database for my local running club.
It has 2 tables, 1.information on athletes which includes name, athlete's club etc as well as an individual athlete number which is the primary key. and 2. A positioning sheet with the place as an autonumber, athlete number and time field. There is also a query which merges the information (including club) from the two tables to make up a results sheet.

In these competitions there is a team event this works by the first 4 people of any one club to come over the finish line. The positions of these athletes are then added and the team with the lowest score wins. The resulting information should be calculated for all teams in the race. There can be an A, B, C team etc for the clubs, meaning that the first four over the line for a club are team A, next 4 are team B etc. It also needs to cancel out any teams with less than 4 people. Meaning i f9 people run for the striders and place 1, 5, 6, 7, 9, 10, 13, 15, 25 then Team A is 1, 5, 6, 7 and Team B 9, 10, 13, 15 the runner who came 25th is cancelled out.

My problem is how to create this list of team results in the Access database.

[soybean]

Welcome to the forum.  To be honest, we don't get many Access questions here, so I'm not sure you'll find a solution from this forum.  Various MS Office questions are posted, but Access is seen less than Word, Outlook, Excel, and maybe even PowerPoint. 

I have Office 2003 and use Access for some simple databases.  I have used some queries in Access, but I'm reluctant to tackle your question because I believe I would consume a lot to time trying to figure it out.  This would include time trying to understand the scenario in your question, which is not clear to me now. 

I'll mention points in your info which I'm not clear on:

2. A positioning sheet with the place as an autonumber

How could "place" be an autonumber?  "Place" means the actual placing of a runner in an event, right?  So, that would have to be a data entry into the database by you, not an automatically generated field. 

In these competitions there is a team event this works by the first 4 people of any one club to come over the finish line. The positions of these athletes are then added and the team with the lowest score wins. The resulting information should be calculated for all teams in the race. There can be an A, B, C team etc for the clubs, meaning that the first four over the line for a club are team A, next 4 are team B etc. It also needs to cancel out any teams with less than 4 people. Meaning i f9 people run for the striders and place 1, 5, 6, 7, 9, 10, 13, 15, 25 then Team A is 1, 5, 6, 7 and Team B 9, 10, 13, 15 the runner who came 25th is cancelled out.

So, you have multiple clubs forming multiple teams for each club?  Is that right?  And, runners from other clubs paced 2, 3, 4, 8, ..., etc.?  Is that right?

You might also want to post your question in http://www.access-programmers.co.uk/forums/ or see what other Access resources are in http://www.google.com/search?hl=en&q=access+forum&btnG=Google+Search

[$t3ff]

On the positioning sheet i have set it as an autonumber to save on time. Means the number and time of the first athlete is number one as they are entered first and second athlete will be entered second so the autonumber will show up as two etc.

So, you have multiple clubs forming multiple teams for each club?  Is that right?  And, runners from other clubs paced 2, 3, 4, 8, ..., etc.?  Is that right?

You are correct the other positions are given to other clubs and there are about 10 to 15 clubs forming multiple teams

[Anodoin]

I could tell you how in Excel, if you're interested.

[$t3ff]

I'd be interested to see the logic of it being worked out in excel

Thank-you very much