New DB setup help

[Lensmeister]

Hi all,

I hope someone can help me out with the various problems I am having with my (almost) new project.

I am recording a football club's history. Currently it is in Excel, but I was hoping to draw it into Access and make it report and query better for things like stats etc.

I need some help setting this with relationships etc.

Ok all the results are held in table tbl_Matches with the fields

fd_MatchID (Autonumber)
fd_Season (text)
fd_Date (date)
fd_Team (text)
fd_V (text) (this is the venue either H, A or N)
fd_F (number) (goals scored for my club)
fd_A (number) (goals scored against my club)
fd_comp (text) (Competition)
fd_Rnd (text) (Round of competition)
fd_Xta (Yes/No)
fd_Replay (Yes/No)
fd_Att (number)
fd_Comments (memo)

Next is the players table to record each line up in each match:

tbl_startingXI

fd_MatchID (not sure yet)
fd_Player (text)
fd_TimeOn (number)
fd_TimeOff (number)
fd_YellowCard (Yes/No)
fd_YellowTime (number)
fd_RedCard (Yes/No)
fd_Subbed (Yes//No)

I also have the tbl_Subs. Not sure I need this one. I suspect I could put these records in the tbl_StartingXI and have another field of something like fd_SubUsed (Yes/No)

fd_MatchID (not sure yet)
fd_SubPlayer (text)
fd_TimeOn (number)
fd_TimeOff (number)

I also need to record the goal scorers in tbl_Goals

fd_MatchID (not sure advice needed)
fd_Player (text) (This I suspect will need to be the player ID from tbl_Players but help needed.
fd_GoalTime (number)



Now to add to this I also have the table that record info on Players.

tbl_Players.

fd_PlayerID (autonumber indexed no dups)
fd_Name (text) (these are recorded as first name surname)
fd_Pos (text) (Position like Goal keeper, defender, midfield, forward)
fd_DOB (Date)
fd_totYell (need this to show the total yellow card picked up in games in the players career with the club.
fd_totRed (as above)
fd_Starts (as per fd_totYell but recording the starts a player made in his career)
fd_Subs (as fd_Starts but only if they were used as a sub)
fd_TotalApp (a summation of fd_Starts and fd_Subs)
fd_Goals (a count of summing up of the goals scored in a players career from the tbl_Goals)
fd_as (text) (In excel I used an individual item i.e. Smith.M so that I can have things like Smith.Mt or Smith.Mr to use with countif)



All help gratefully received. I have been knocking things together for a few years for this but I always appear to come across problems and find it very hard to get someone to help me and I am almost insane trying to get this going properly.

I suspect in the past I have done relationships wrong or the normalisation is wrong (what ever that is related to this) or something else I have managed to make a pigs ear out of.

 

[Lensmeister]

Bump


Can anyone help me please?

 

[jackd]

I don't know if this is helpful, but there is a free data model representing "football clubs" here.
http://www.databaseanswers.org/data_models/football_clubs/index.htm

 

[Norie]

You should use the PlayerID field in the players table in the lineup table instead of the players name.

When it comes to calculating the no of cards for each player you can use a totals query based on the lineup table - don't store that in a table.

You can link that back to the player table to get their name etc.

When I think about it further what you have is a many-to-many relationship between matches and players.

You might have already seen that as your tables seem set up that way.

One thing you might want to change though is the startingIX table.

How about a MatchPlayers 'junction' table?

It would have the MatchID and PlayerID as foreign keys, and would basically contain information on all the players involved in a match.

So you could include subs in at, and probably goals, though that might warrant a table of it's own because a player can of course score multiple goals in one match.

Hope that makes sense in some way.

 

[Lensmeister]

Thanks. I will have a look at these soon and get back with any questions