Club database and scoring help & ideas

[antonyg]

Hi,

I am wanting to create a club database and completion scoring table. To give you an idea, let’s say 100 participants who would be split into age group and male or female.

There would be 10 or 12 events. And not all participants take part, in all competitions (although could in theory).

There is then a scoring system for 1st to xth per event.

I want to try to create a way to track each competition and know who has won the season.

Any ideas?

[Olly]

That's a REALLY broad question!! Almost impossible to give an answer, without a load more detail....

High level approach:
Dimension table for Members
Dimension table for Events
Fact table for Results
Measure to calculate ranked positions

How does your 1st to nth scoring system work?

[antonyg]

Thanks Olly - appreciate it was a broad question. At present it is done with tabs for each event, and look ups - which leads to some errors and manual work. The club are looking for better ways to manage for this year and future.

I was thinking along similar lines, that the members and events are in separate tables - and this can be exported in from our membership database and calendar. The hard part (for me) was working out how to link and formulize the results.

Scoring works by giving 1st place, 1 point. 2nd 2, etc. Lowest score at end of season wins. Winners for age group as well as overall.

[Olly]

I'd strongly recommend using PowerPivot. Then you can easily manage relationships between tables, and create powerful DAX measures.

Attachment 627204

So is the winning score the lowest AVERAGE position? How do you allow for competitors who haven't taken part in all events? I mean, if I didn't enter any events, my score is 0, so I win, right?!

[antonyg]

There are also some rules - you have to compete in at least 2 of each type of event to qualify, and unfortunately 0 doesn't win :-).

The lowest score from each of the events (x2) is counted.

I will look into PowerPivot.