linking multiple field values to a field selected from combobox

[ba_boy]

Hi,

I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football(soccer) team's players and match statistics.

What I have so farsample attached)

Tables:
* Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player)
* Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box)
* Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box
* Venue - similar to Competition table
* Opponent - Similar to above two tables
* Match - MatchID, Competition, Venue, etc (form corresponding to table attached)

Forms:
* Player form
* Match form

Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided.

I have no idea how to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).

Can someone please tell me if its possible to do so in access and if so then I would really like to know how. If there is a sample access database with a stats tracking layout as mine that would also be great.

These stats will later be sorted by how many goals a player scored in a competition or year and so on..

If I'm too confusing or left out certain details, please feel free to ask me.

 

[namliam]

well for starters I believe your design is flawed.
A player may have a (default) position, but his actual position may change per game/Match

A player's position probably and his stats definatly are reliant on the MATCH not.
Thus you would want an additional table: tblMatchStats
MatchID, TeamID *, PlayerID, Stats, etc

* Possibly TeamID would be a given at any one time, so perhaps that shouldnt be needed here. More so assuming in tblMatch you have HomeTeam and AwayTeam already

I would also change your Opponent table to a Team table, that way you can also (if you like) record stats on other teams in the same database quite easily instead of for only one team and make easier distinctions between home and away games.

Perhaps from here your form design may also be more appearent. Take your time setting up your normalized tables first and make sure you get that right, else it will bite you later on.