DB for Competition

[LedZled]

Hi!
I've got an Excel spreadsheet for a cheer leading competition. It holds information about classes, clubs, teams/entries, then there's scoring from 3 judges with possible deductions both in points and in per cent. Total points are calculated and the teams in each class are ranked. Works like a charm, but for the next competition I will have to enter all classes and teams again, then make sure ranking formulas are using the correct fields and so on. I would like to be able to enter teams in a form, stating the team's club and in which class the team will compete, then when all teams have been entered I want to auto-create a form where I can enter scoring and deductions, and have the ranking calculated.
Ideas, anyone?

[HiTechCoach]

First you will need to create your tables. Creating a a properly normalized design should not ot resemble your spreadsheet. This is normal for a relational database.

Until you get your tables designed I would not create any forms or reports.

[LedZled]

Thanks, Boyd. I posted my question after some futile attempts to convert my Excel spreadsheet into a database - it just turned into a bunch of tables and indexes that made no sense to me.
After posting, I gave it another shot and created 3 tables; one "clubs" table holding only the clubs, one "classes" table holding the classes, and a 3rd table called Entries, this one holds the entries and stasrted out with 3 columns - team name, club(Lookup from clubs) and class(lookup from "classes").
I then extended the "Entries" table to hold the scoring as well, then I created a form for entering the scores. Now it all got complicated - I want to enter scoring into the form, it should then calculate ranking and enter that into the table - or should it?
Scoring is calculated like this: there are 3 judges, each judge gives a score. If technical errors have been made, or rules have been broken, points are deducted either as a number of points or as a percentage, depending of what type of error has been made.
So I would like to have a form where I enter the points and deductions. In this form the score should then be calculated and entered into the database.

This form would draw info from the "Entries" table and submit scores to the same table after calulations. Is this possible?

I guess that to get the ranking I could then create a report where all entries can be presented and ranked classwise?

[HiTechCoach]

It sounds like you still may have some design issue with the scores.

It would really help to take a look at your database to see if you are on the correct path. Would it be possible for you to post a sample of your database with some sample data in each table. Be sure to compact the database before you ZIP it up. This way the attachment will be as small as possible.

PS: I have worked with the scoring for dance comps with multiple judges (5-7) and calculating scores. This is not new to me.


Boyd Trimmell aka Hitechcoach
Database Architect and Problem Solver
Microsoft MVP - Access Expert
25+ years specializing in Accounting, Inventory, and CRM systems
Started with Access 2.0.
"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do."

[LedZled]

Well - here goes nothing...
I tried creating a scoring table but it jsut wouldn't work out for me...
The existing tables are populated and I think the relationships should be clear

- oh, and thanks for your interest!

[HiTechCoach]

Great. I will take a look and get back with you.


Boyd Trimmell aka Hitechcoach
Database Architect and Problem Solver
Microsoft MVP - Access Expert
25+ years specializing in Accounting, Inventory, and CRM systems
Started with Access 2.0.
"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do."

[HiTechCoach]

Took a look at your database. I did not see any fields or table, or anything else related to scores or scoring

When designing a database I think in terms of entities which are defined with object(s) (table(s)).

A simple example:

Entity: People - all types of people

Entity: Teams
Consist of multiple Objects: Team and team members (this is a relationship/junction table between Teams and people)

What/who gets scored?



Boyd Trimmell aka Hitechcoach
Database Architect and Problem Solver
Microsoft MVP - Access Expert
25+ years specializing in Accounting, Inventory, and CRM systems
Started with Access 2.0.
"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do."

[LedZled]

Well, coach...
that's just my problem - couldn't get the scoring table working.
And when I make changes, Access keeps saving everything so once you're in the wrong direction , you have to start over...
Anyways - Got a new example for you.
Scoring principle:
Judges each awards some points, e.g. 78- - 74 - 82
Points may be deducted, and for some technical errors a points percentage - calculated after any points deductions and rounded to half points.
So let's say the entry has 2 points and 10 percent deducted.
Deductions would be:
Judge 1: 2pts+10% of (78-2) = 2+7,5(7,6 rounded to nearest half point)
Judge 2: 2pts+10% of (74-2) = 2+7(7,2 rounded to nearest half point)
Judge 3: 2pts+10% of (82-2) = 2+8
So the score from each judge will be
Judge 1: 78-9=69
Judge 2: 74-9=65
Judge 3: 82-10=72

So I've tried creating a scoring table with fields for the functions above. for calculations I figured I had to use a form. Seems to work out but I neeed that form better in some ways;
1) Want to select Class first, and based on that I want to select the entry.
2) After calculating scores, I would like them entered into the DB.
3) When I've entered scores for all entries in one class, I want to rank them.

Am I on the right track? Or are there any major errors in my setup?

[HiTechCoach]

What/who gets scored?



Boyd Trimmell aka Hitechcoach
Database Architect and Problem Solver
Microsoft MVP - Access Expert
25+ years specializing in Accounting, Inventory, and CRM systems
Started with Access 2.0.
"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do."

[HiTechCoach]

I figured I had to use a form. Seems to work out but I neeed that form better in some ways;

Since you form is not working well that usually is an indicator you still do not have your tables design correctly. If the database is properly normalized then you are proably not using sub forms to view the related data. I am thinking that you still do not have the tables properly normalized.

TIP When the database is design properly the forms usually are a lot easier to create.


Boyd Trimmell aka Hitechcoach
Database Architect and Problem Solver
Microsoft MVP - Access Expert
25+ years specializing in Accounting, Inventory, and CRM systems
Started with Access 2.0.
"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do."

[HiTechCoach]

Took a look at your database

Issues I found:
1) the relationship withe the scores table dif not use a primary key on either side.
2) The scores table is not properly normalized. It looks like the design of a spreadsheet not a relational database. It has what is called repeating fields. Each judge's scores should be a separate record. You sum the records to get the total score. This is a lot different than how a spreadsheet would ado the calculation.

It seams like there is a piece missing.

What are the entries for? Do you have divisions, levels, or something like that?


Boyd Trimmell aka Hitechcoach
Database Architect and Problem Solver
Microsoft MVP - Access Expert
25+ years specializing in Accounting, Inventory, and CRM systems
Started with Access 2.0.
"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do."




I would expect that the big picture of what happens is this:

[Something] > has entries (by a team) >> that is >> scored >> by multiple judges


Boyd Trimmell aka Hitechcoach
Database Architect and Problem Solver
Microsoft MVP - Access Expert
25+ years specializing in Accounting, Inventory, and CRM systems
Started with Access 2.0.
"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do."

[LedZled]

Hi again Boyd and thanks for your patience. I'll see if I can explain this...

The DB is intended for regionals and nationals competitions in Cheer Leading. There are a number of age groups and in each age group there are different levels, based on technical difficulty. I refer to each age/level combination as a class.

Since there are both individual and team classes, I refer to the participating teams or idividuals as "entries".

So, let's look at one class, e.g. Cheer Sr Advanced. Normally, at regionals there are some 5-6 entries. They all perform their 2-3 minute routine in front of 3 judges. The judges evaluate the performance, awarding up to 10 points per category. For Cheer Sr Adv. there are 14 categories, leaving a possible maximum points tally per judge of 140 pts.

The routine is also watched by 2 line refs and 2 tech refs. After the competition there's a judges' conference where all scores are checked. If line refs or tech refs have seen deviations, points and/or percentage deductions are decided. Deductions are made from each judge's points.
Percentage is calculated after points deductions.

The entries are ranked per judge, the ranks are summed up and the smallest rank total is the winner.

I've attached an Excel sheet demonstrating the principles and formulas.
I started out trying to convert that into an Access DB to make it more dynamic. The Excel version works alright, it's just that for each competition, because of different nubmer of participants in the different classes, the ranking formula has to be looked through very thorughly to make sure nothing's missed. It's that error risk I want to reduce.

So -feel free to create your own Access version from scratch and run me through the whys and whats about it.

//Hans

[HiTechCoach]

So to answer my question using your terminology:

What/who gets scored? Entries get scored.

Is that correct?

Boyd Trimmell aka Hitechcoach
Database Architect and Problem Solver
Microsoft MVP - Access Expert
25+ years specializing in Accounting, Inventory, and CRM systems
Started with Access 2.0.
"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do."

[LedZled]

Well - yeah, the performance of the entries get scored. So for each entry there's one row in the "scoring" table.

[LedZled]

Come to think of it - the scores could basically be added to the "entries" table. Why split it? Beats me...