Results 1 to 4 of 4
  1. #1
    MrAsaboa is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    2

    Help requested: adding team lineups to my football db

    Hi all, relative Access newbie here (using Access2016 btw), but I've spent a lot of time building and learning before reaching a brick wall. I'll try to make this query easy to read while providing full context:

    Overall Objective:
    Create a database for a football league (in the example i have uploaded I have used the National League South as an example), where I can fill in a form for each match that's completed which will include the teams, score, who played, and who scored. So that I can see the latest league table, and I can run a report and see how many appearances each player has made and how many goals they have scored.

    Progress So Far:
    I have created a database where I can fill in a form setting out the match date, home team, away team, and score. Through a couple of queries I have built a league table query which functions perfectly, pulling through all the teams, showing their wins, losses, draws, points, goal difference, etc. I'm really happy with it.

    My Request for Help:
    I'm really stuck on how best to capture the team line ups for each game in such a way that I will end up being able to report on how many games and goals each player has at the end of the season. I can't even get the db right, much less the complex task of designing a form to capture it. Something that will look a bit like this screenshot, and with something to indicate whether a sub cam on and played (and therefore is credited with an extra appearance).



    Click image for larger version. 

Name:	2019-08-15_211802.jpg 
Views:	21 
Size:	82.6 KB 
ID:	39452

    In terms of other issues:
    • I don't fully understand the difference between a lookup between one table and another and setting relationships between tables
    • As a result of the above, my attempt to associate players with clubs in the Players table is a bit bodged (it should be a drop down list)


    Database Schema (Tables):
    What I have so far is:
    • Clubs - contains team names and link to the Stadium table
    • tblFixtures - when I add information via the Input Results form, the results get dumped in here
    • Players - should contain a list of all players and the club they are signed to. I dont know if things like appearances and goals should go here, or if that should go in another table, or a query or something.
    • Stadiums - purely cosmetic at the mo. Should be a 1:1 relationship with clubs, but I dont think it is
    • Seasons - in case in the future I want to have stats for multiple seasons in the same db (but this can go if it makes it too complex)
    • AllApps - just a table where I was experimenting adding appearance data to.


    The Queries all work well, especially the FullTable which is the league table. The Lineup form is just an experiment. The Input Results form is simple and works fine, and ideally I would add lineup info on this same form, per the screenshot.

    I've uploaded my efforts so far - I'm grateful for any help or direction you can share - thanks!

    Footballdb.zip

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some ideas from this older link re Soccer and Football.
    Good luck with your project.

  3. #3
    MrAsaboa is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    2
    Thanks - that link was super helpful and really helped me get through a lot of my project. I've got a follow on question, which is to do with VBA/SQL. I reckon it's probably quite simple if you know SQL (I am brand new to it):

    In a test db, here are my table relationships:
    Click image for larger version. 

Name:	Relationships.jpg 
Views:	13 
Size:	24.4 KB 
ID:	39489

    And here is a form I have created:
    Click image for larger version. 

Name:	Form.jpg 
Views:	12 
Size:	10.9 KB 
ID:	39490

    What I am trying to do is to create one new record in two tables. First, the Team and number of team goals should be added as a new record in the FixtureResult table (this should create a new FixtureID as that's an autonumber). Second, the player I selected in the drop-down list should be added to the PlayerApps table as a new record, along with the Fixture ID that was created in step one.

    I'm almost there! Here's my code:

    DoCmd.RunSQL "INSERT INTO FixtureResults ([Team], [Goals]) VALUES ([Team].Value, [Goals].Value)"
    DoCmd.RunSQL "INSERT INTO PlayerApps ([FixtureID]) SELECT LAST (FixtureID) FROM FixtureResults"
    DoCmd.RunSQL "INSERT INTO PlayerApps ([PlayerID]) VALUES ([Player].Value)"

    First line works well. Second line also works well. Third line, however, creates a new record. I know this is because I'm using INSERT INTO. What I need is for lines 2 and to insert information from the form into the same new record.

    Please bear in mind, this is from a proof of concept database I have made. My main one is larger - but if I can make it work in the POC I should be able to understand how to make it work in the proper one. Thanks!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You should apply referential integrity to your set up.
    There are several Access and SQL video tutorials on internet/youtube.
    I like your POC approach -- get the concept and technique understood -- before jumping into physical, operational database.

    Many will advise you NOT to use First/Last --instead using another field (perhaps Date/Time) and opt for Min/Max for consistent results. I'm sure you have Date type fields in your database.
    Good luck with your project.

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

Similar Threads

  1. Soccer/Football stats
    By spyfire14 in forum Access
    Replies: 5
    Last Post: 05-24-2018, 12:48 AM
  2. Football/Soccer database
    By spyfire14 in forum Access
    Replies: 4
    Last Post: 04-06-2018, 05:03 PM
  3. Replies: 26
    Last Post: 10-11-2017, 06:57 AM
  4. Replies: 4
    Last Post: 10-16-2014, 08:57 AM
  5. Replies: 5
    Last Post: 02-07-2012, 07:06 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