Calculatinng Totals

[munday63]

Hi i have two problems.

I am creating a football dream team database with two tables:

Team table - Team ID, Team Name, Player 1, Player 2, Player 3, Player 4, Player 5, Player 6, Player 7, Player 8, Player 9, Player 10, Player 11 and Points Total,

All the player columns are lookup functions which are linked to player table

players table - Player ID, PLayer Name, Position, Club, Value, Points

My problem is i cannot figure out how to calculate the points total in the team table from inputting the points into the player table.

I also would like to know how to create a 'total' column by adding together other columns in a table.

Thanks in advance

 

[neileg]

You don't store the total in the team table, you caculate it in a query any time you need the result.

 

[munday63]

ok but how would i write the query because when i add the players together, it adds all of the teams, not each one on a seperate line and it only adds the player ID, not the player points

 

[Rich]

It would be easier if you normalised the db instead of re-creating a spreadsheet

 

[munday63]

how do i do that i am new to databases

 

[neileg]

You need to have two table, one for your teams and one for the players. You hold the ID of the team in the player table so you know which team the player plays for.

tblTeam
TeamID Primary key, autonumber
Team Name

tblPlayers
PlayerID Primary key, autonumber
PLayer Name
Position
TeamID Foreign Key
Value
Points

If you run a totals query on tblPlayer, grouping by TeamID and summing the points field, this will give you the team points total.

 

[munday63]

but the same players can play for more than one team, will this affect anything?

 

[neileg]

Ah, then you have a many to many relationship and you will need a junction table. Have a look in the sample databases section on this site for more info.

 

[Rich]

but the same players can play for more than one team, will this affect anything?

Not at the same time, surely?

 

[munday63]

can you post a link to the sample of a junction table

 

[neileg]

http://www.access-programmers.co.uk/forums/showthread.php?t=62564&highlight=many-to-many