Tricky database issue

[Peteg]

Hi,

I am trying to create a 5-a-side football database and I am having problems understanding how to structure the basics of it. I have searched the forum and cannot find anything which gives an explanation or an example to help me unfortunately.

The database is collecting data on the matches a group of us play once a week, we basically have a list of about 40 people and each week 10 of them play a 5-aside game. This means the teams change each week, so team ‘Lights’ will have different players one week from the next week (as will teams ‘Darks’ too of course. We collect the data on who played and who won, then if you are on the winning team you get 3 points, draw 1 etc on an individual basis. Over the season we get results for each player which details Games played, Win percentage, Average points per game. This is all in an excel sheet but we want this in an Access database now.

In terms of the setup of the access database it is set out as per below so far, which might not be the best way of doing it.

PlayersTBL
ID
Name

GamesTBL
ID
Date of game
Winner – Darks or lights
Game valid – yes/no – in case of injury to a player the game might not be included in scoring

GamePlayerTBL
ID
Date of game
Darks player 1
Darks player 2
Etc to 5
Lights player 1
Lights player 2
Etc to 5

I am really struggling as to how to set this up so it knows which players won each week, which lost and which didnt play etc. It needs to calculate scores when the players might be a light one week then a dark another, from what I have read it might need further linking tables but I am not really sure. Can anyone help or point me in the right direction? I can share the excel sheet if that helps?


Cheers
Pete

 

[burrina]

You could take a look here and see if something fits your needs,

http://www.databaseanswers.org/data_models/

HTH

 

[plog]

Numerated field/table names (e.g. Player1, Player2, ...) are always a sign of a bad structure. When you want data out I'm sure its going to bite you. I believe this is the structure you want:

PlayersTBL (holds all players)
Player_ID, Player_FirstName, Player_LastName

TeamTBL (holds all teams)
Team_ID, Team_Color

RosterTBL (holds what players go to what teams)
ID_Team, ID_Player

GameTBL (holds all games)
Game_ID, Game_Date, Game_Valid

ScoresTBL (holds team scores of a game)
ID_Game, ID_Team, GoalsFor

The real work will be done in the forms and queries to make sure the data goes in and comes out properly.

 

[Peteg]

Thanks, I will have a go with this format.

 

[Peteg]

I am having problems with the relationships for the games, can anyone help please? I have attached a image of the current relationships.

Thanks
Pete

 

[plog]

TeamTBL.Team_ID should connect to ScoresTBL.Team_ID

RosterTBL shouldn't have a Game_ID field.

There should be a one to many relationship between GameTBL and ScoresTBL.

 

[Peteg]

Thanks Plog.

Is this what you meant?

 

[plog]

Yes that's it. Of course you get points off for Player_FullName--you shouldn't store redundnant data. You can always make their full name by adding their first and last names together:

FullName: Player_FirstName & " " & Player_LastName

 

[Peteg]

Thanks - yeah i had added in that code to the PlayerTBL as a calculated field, is that what you meant?

I have started to sort out the form now for inputting the data after each match, which as you pointed out is tricky. Do you have any suggestions as to how i go about showing a simple input for Darks score and the Lights score?

 

[plog]

No, you wouldn't store it in a table at all, you would most likely use the code I posted in a query.

This isn't going to have a simple input, for your forms, you are going to need 5 different ones (at least). The rule is one form should be based on one table. You are not going to be able to create a game and input players on the same form. You will essentially have to walk through your table hierarchy, entering data as you go. That's how your form system will have to work.

 

[Peteg]

Yes that will be a problem i can see now. Do you know of any tutorials or links which could explain how i could produce a form for the Roster table as i dont understand how you build that at all...

It needs to say which 10 players played of the players table and for which team i.e. darks or lights in a 5 vs 5 format.

I imagine this is through some advance combo boxes...

 

[plog]

No I don't. I would search around the Forms forum, I am sure your project isn't the first of its kind.

It may actually involve a form that has 10 subforms each that appear just like a drop down.