Database Program for a Tournament?

[Bombay]

I am looking into designing a Access Database that would help run a Tournament.

Features it would need to do.
-Have a way of Entering in Players
-Have a way of Pairing Players for Each Round
-Have a way for entering in results for each "Match"
-Have a way to show Standings

Results
-Entering Results for each round would need to then determine a
winner.(Someone may score 500 points to someones 400.)
- A win with 'Max' Score = 3 points, normal win is 2 points. Loss where you
break a plateua of say 400 points gives you 1 loss.

Standings
-Overall Points
-POints differential based on points scored during the games.

Anyone know if this is feasible in Access and if so could you suggest how
each section should be done?

 

[akphidelt]

Yes this can be done. I would create at least 2 tables. One for each player.
Then I would create another table with the matchups. Such as

Player 1---Score---Player 2---Score

Add in any dates or anything else you want to track.

Where all the magic will happen in terms of point gathering and stuff you
would use a couple queries that can do the calculations for you.

Also you can use forms and reports using DCount or DSum formulas.
Theres plenty of ways you can go about it, but it's best to get a nice table
structure down first then you can dissect it any way you please.

 

[Bombay]

Tables
Players(Player Name, Player Points, Players Score Differential)

Round 1 Matchup(Player A, Player B, Points A, Points B)
Round 2 Matchup(Same as above)
Round 3 Matchup etc...

Would this be the Structure used? There were 32 people at this last
tournament, I think having 32 tables for each player is a bit much? Overkill?

 

[akphidelt]

No no, definitely never have a table for each player.

You want a table just for the players, preferably you would use their Auto
ID that is generated from access. This table you insert nothing in to except
a new player.

The 2nd table is your matchups so it might look like this

Player A--Player B--Points A--Points B--Round Number

You don't need to keep a table that has point differentials and player total
points and all that stuff because you can do all that using queries. All you
need is to track the matchup, the round, and the points... then everything
else can be calculated automatically in the queries.

 

[Bombay]

Okay I am following a good amount of this, The Functionality of this program
should do the following.

Step 1
-Have a form to Enter in Players.

Step 2
-Run a Query to Pair Round 1

Step 3
-Have a Form to Enter in Results for Round 1 Matches

Step 4
-Run a Report that displays the Overall Rankings for the Tournament

Step 5
-Run Pairings for Round 2.

Question.
-How would you run a table that pairs up players and then populates the
table for the matchups. It would be a daunting task to have to manually
setup matchups in the table.

 

[akphidelt]

Well, how do you generate the matchups? Are they just random? And does it
even matter who wins or loses the matchup or is it all about the points they
score?

 

[Bombay]

Matchups are based by this.

Overall Standings
1 plays 2
3 plays 4 and so on...

Scoring is a tad bit tricky.
3 point win = 500 points
2 point win = 400+ points scored and scoring more than your opponent.
1 point loss = scoring 400+ points but your opponent outscored you.

This lets say after round 1.
Player A scores 500 and Player B 410
Player C scores 450 and Player D 300

Standings would be
Player A 3
Player C 2
Player B 1
Player D 0

 

[akphidelt]

Alright, so you have a decently complex system going on there that might
require some fanagalling. So how is round 1 set up? Do you already have a
ranking or is round 1 done by just the order of the players set up in the
table?

One way I like to do things is to copy and paste access data in to excel, do
my formulas and manipulation there, then paste append that data back in to
excel. Might sound tricky if you are not used to it but it really is fairly
simple and a lot faster then doing tons of access manipulations.

I'm sure some expert around here can help you out more with doing those kind
of things. You might want to ask another question here about how to
automatically set up matches based on those conditions.

 

[Bombay]

Ive been doing this in excel already, so the formulas are done.

Round 1 needs to be paired completely Random.

Rounds 2-X need to be paired based on how they are ranked.

Question still remains, what function or action would I do to run the Pairs
that populate the table with the matchups.

 

[akphidelt]

I would start a completely new question asking specifically how to do that. I
don't know any way of automatically doing it. If I were doing it, after each
round I would just copy that data, paste it in excel... then do my sorts and
other functions to get the matchups in the format that fit the access table.
And then just paste the matchups in to the matchup up table. Then you can use
a form to update the scores for those matchups.

Otherwise I have no clue, but im sure some expert on here does

 

[Bombay]

I assume Id use Update Queries?

 

[Steve Schapel]

Bombay,

More likely you would be using Append Queries, to write the competitors
into the second round based on the first round results.

Just out of interest, what sport are we dealing with here?

 

[Bombay]

Update Queries to Update the Player Table with information such as Total
Points and Points Scored.

Append Queries to populate the Round 1-5 Tables?

 

[Bombay]

How would I correctly do this Update Query

Looking to update Player Total Points(in table Players)
Where Player Name = Player A(Field names)

The update is taking Total Points.Round1 and updating them to Total
Points.Players but adding them to the points that already exist.

So if in Table Players
Player = Kinseth
Total Points = 500

Table Round 1
Player A = Kinseth
Total Points = 600

After the update

Table Players
Player = Kinseth
Total Poitns = 1,100

 

[Steve Schapel]

Bombay,

Yes, along those lines anyway. I wouldn't expoect you would have
separate tables for Rounds 1-5 though. I would expect you would have
all of this in a single table, with a field to identify which Round the
game belongs to.

 

[Steve Schapel]

Bombay,

Just out of interest, what sport are we dealing with here?

You will see that I have an interest in this area:
http://www.sportsrunner.biz

What you are suggesting here is not correct.

There should not be any Table Round 1. Forget the idea of a separate
table for each Round, it is not correct.

Also, forget the idea of a Total Points field in the Players table, this
is not correct. The Total Points for each player should be calculated
as and when required, via a query. It should not be stored in a table.

 

[Bombay]

It is not a sport, it is a hobby game called Heroscape.

Okay I only need 2 tables then.

Table for Players and a Table for Matches

Have a feild in table Matches for Round #

Have a Query that Runs a Standings Report based on all the rounds for each
player.

Have a Append Query that will populate each Round based on the Standings
Report?

 

[Steve Schapel]

Bombay,

It is not a sport, it is a hobby game called Heroscape.

Ok, I suppose the same principles apply to tournament administration.

Okay I only need 2 tables then.

Table for Players and a Table for Matches

Yes, if this only relates to one tournament. If it will manage multiple
tournaments, and players will be involved in more than one tournament,
then the table design should relect that from the outset.

Have a feild in table Matches for Round #

Yes. But (as an asdie) it is not a good idea to use a # as part of the
name of a field.

Have a Query that Runs a Standings Report based on all the rounds for each
player.

Um, approximately... I think it will need to show Standings for all
Players for any given Round.

Have a Append Query that will populate each Round based on the Standings
Report?

Yes. I would express it as - Have a Append Query that will populate the
next Round based on the Standings Query for the previous Round.