Results 1 to 6 of 6
  1. #1
    spyfire14 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    4

    Soccer/Football stats

    Hello all,
    I am fairly new to using access and I want to do something that I have no idea what to do.
    The idea:
    I want to have a database with statistics of every soccer/football game of a certain team. What players played, for how long, who scored, posssesion stats etc as well as being able to group together each players individual stats eg how many minutes of football they have played.
    I have all the data in an excel spreadsheet but I thought it would be nicer to present it in access as well as automatically doing the maths side.
    Does anyone have any ideas of what to do or if there is a similar database out there?

    Many thanks.



    P.S Any questions then feel free to ask.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    tblRivals: RivalID, TeamName;
    tblPlayers: PlayerID, FirstName, LastName, DOB, ...;
    tblRoles: RoleID, Role (goalkeeper, forward, etc);
    tblPlayerRoles: PRID, PlayerID, RoleID, ValidFrom;
    tblGames: GameID, CameDate, GameField, RivalID;
    tblGamePlayers, GPID, GameID, PlayerID, PlayerNo, RoleID, InMinute, OutMinute, ... (Player may have a role in team, but it is possible for specific game to have different role. Team role can be used as default role.);
    tblGameGoals: GGID, GameID, GoalSide, PlayerID, GoalTime (GoalSide: 1 - our team, 2 - rivals team, PlayerID: rivals goal - 0 [unless you have in tblPlayers players of all teams], own team - PlayerID);
    tblGameCards: GCID: GameID, CardSide, Card, PlayerID, CardTime (Card: 1 - green, 2 - red, other fields like in tblGameGoals)
    ... you can have separate tables for other game events, like penalties, corners, etc..

  3. #3
    spyfire14 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    4

    Linking

    Quote Originally Posted by ArviLaanemets View Post
    tblRivals: RivalID, TeamName;
    tblPlayers: PlayerID, FirstName, LastName, DOB, ...;
    tblRoles: RoleID, Role (goalkeeper, forward, etc);
    tblPlayerRoles: PRID, PlayerID, RoleID, ValidFrom;
    tblGames: GameID, CameDate, GameField, RivalID;
    tblGamePlayers, GPID, GameID, PlayerID, PlayerNo, RoleID, InMinute, OutMinute, ... (Player may have a role in team, but it is possible for specific game to have different role. Team role can be used as default role.);
    tblGameGoals: GGID, GameID, GoalSide, PlayerID, GoalTime (GoalSide: 1 - our team, 2 - rivals team, PlayerID: rivals goal - 0 [unless you have in tblPlayers players of all teams], own team - PlayerID);
    tblGameCards: GCID: GameID, CardSide, Card, PlayerID, CardTime (Card: 1 - green, 2 - red, other fields like in tblGameGoals)
    ... you can have separate tables for other game events, like penalties, corners, etc..
    How would you link them? eg. one to many, many to many etc

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by spyfire14 View Post
    How would you link them? eg. one to many, many to many etc
    You mean table relationships? I almost never use them.

    When I create form - subform pairs, and PK and FK in linked tables have same names, then Access creates form level relationships automatically, when those aren't set at database level. I set the relationship type for relationships created automatically at form level then manually, according my needs.

    Queries for reports I usually design in SQL view. And as currently I use SQL Server as back-end, unless I design the query directly as report datasource, those are really SQL views linked to Access DB.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by ArviLaanemets View Post
    You mean table relationships? I almost never use them.

    When I create form - subform pairs, and PK and FK in linked tables have same names, then Access creates form level relationships automatically, when those aren't set at database level. I set the relationship type for relationships created automatically at form level then manually, according my needs.

    Queries for reports I usually design in SQL view. And as currently I use SQL Server as back-end, unless I design the query directly as report datasource, those are really SQL views linked to Access DB.
    I always define the relationships. IMHO it's the best way to enforce referential integrity.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Bob Fitz View Post
    I always define the relationships. IMHO it's the best way to enforce referential integrity.
    When I started with Access, I used them too. But then (some 15 years ago) I did run into problems in multi-form applications, when I needed different relationships for different form-subform pairs.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Football/Soccer database
    By spyfire14 in forum Access
    Replies: 4
    Last Post: 04-06-2018, 05:03 PM
  2. soccer database
    By delboy2405 in forum Database Design
    Replies: 1
    Last Post: 11-28-2015, 06:57 AM
  3. Replies: 4
    Last Post: 10-16-2014, 08:57 AM
  4. Replies: 5
    Last Post: 02-07-2012, 07:06 AM
  5. Generating stats - newbie question
    By FavouredEnemy in forum Reports
    Replies: 3
    Last Post: 04-15-2010, 08:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums