Handicap Race System.

[Mantaii]

Hi! I hope you can help!


I have designed my database and think it holds all the information I need, the rest I hope is possible via reports. If it requires programming then please move this thread to the correct section of the site!

I organise a handicap running race once a month, the handicap is set as 60mins, with the slowest runner going first and the fastest runner going last.

I have setup the database so I can register all the runners.
I have setup the database to accept the results, but while typing this realised that it doesnt store whether a runner is a member of the club on a month by month basis. Will need to do that!

I know its mostly down to the logic and making sure I get the correct calculations entered but this is where I need your help!

What I need to be able to do is create numerous reports each month and also at the end of the year.

I will go over the monthly reports first.
1) Overall Results Report - Show the results of all runners by finish_time for that month

2) Members Only results report - Show the results for all the runners who are members for that month by finish_time for that month.

3) Somehow, I need to allocate a finishing score for each member, each month. If they are first, the get 1 point, if they are second 2 points etc. But this must be for members only.

Yearly / current reports.
1) I need to be able to calculate how many points each runner has been allocated each month so that I can create a leader board, the complicated bit is also how to discard the worst result after X number of runs. (still to be decided)

Overall

I need to be able to recalculate someones handicap after 2 concecutive bad runs i.e. going slower than their handicap i.e. someone who has a handicap of 30mins, finishes 31mins and 32mins. So for the 3rd race they get a new handicap time.

If this is all too confusing then ask as many questions as you like.

Thanks

[June7]

Certainly this can all be done, we just don't have enough info to provide specific guidance.

Monthly 1 and 2 appear could be managed by a SELECT query with filter criteria.

Monthly 3 methodoloy depends on data structure. How is place determined?

Yearly 1 might be accomplished with query(s)

Overall looks complicated. Might need some programming for this.

You need to make attempt at accomplishing these tasks and when you encounter specific issue, post thread on that issue. Provide enough information for an analysis of the issue (SQL statements, code, data structure).

[Mantaii]

Hi
thanks, I am more than happy to give it a bash myself, I just wanted to know if it was possible to start with, no point me building a MS access system if Access can't do what Im wanting.

For Monthly 3 place is determined by the order that they cross the line, so it would be by their overal finish time. Which I have managed to sort when creating a report however, I need to be able to store this position somehow so I can do calculations with it i.e. positions up i.e. first place (1) second place (2), third place (3) add it to together to get a score of 6.

Would I open up a thread for each query or just keep it all together within this thread?

Thanks,

[June7]

You don't have anything built yet? Start building. As you encounter issue start thread with specific question.

No point in asking questions about queries if data is not yet structured.

Review the 'sticky' thread tutorials here http://forums.aspfree.com/microsoft-access-help-18/

[Mantaii]

Sorry June, what I meant was I have the database created but just not started the calculations or reports. Got a couple of forms created etc.

Will get on with building this and then start asking the questions!