Create table from db

[green'n'white]

Hi,

I have a table in MS Access that contains football game results, including the players playing in the game. A new row is created for each game and the name of each player, sub and goalscorer/s is entered in each field of 1-11, subs1-3 and goals.

What I am struggling to work out is how to create a table on the site that will contain all of the players names that have appeared, add up how any games they have played in, how many times thay have been a sub and how many each has scored and return tham as a number.

Any help will be greatly appreciated,

Thanks

[NogDog]

Probably you should think about breaking your single table out into a number of tables. You might do something like this:

table: player
player_id
last_name
first_name
(any other data you want to know about each player)

table: game
game_id
date
home_team
visitor_team
home_score
visitor_score

table: game_player
game_id
player_id
status ("starter", "sub")
sub_for (player_id of player that was subbed for, null if status = "starter")

table: goal
game_id
player_id
time

With a structure such as this, you could then write queries to get any of the information you specified, as well as others you haven't thought about yet.

[green'n'white]

Thanks,

I hadnt really thought about doing it like this, ill have a look at it, and am sure I will be back in the near future.

[NogDog]

No problem. And of course that was pretty much an "off the top of my head" design, just to give you some ideas. There may well be better ways to organize it; I just wanted to get you thinking in a relational database manner. :)