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).
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