tdbgrid help

[henryhill]

hi,

i'm using an access database to create a program for recording bridge scores. i'm using tdbgrid to display the scores from a query in the database. the columns are as follows: team_name, round1_score, round2_score, round3_score, round4_score, round5_score, round6_score, round7_score, round8_score, round9_score, round10_score, round11_score, round12_score.

what i'd like, is for their an extra field that tells me the average of all the round scores for each team.

is this possible? any ideas how?

thanks

 

[imterpsfan3]

What I would do first is normalize your database. It looks like you are dumping everything into one table, where 3 tables would be better. The advantage of normalizing a database allows you to extract data like you mentioned above more easily. I would recommend creating these tables:

tblTeam     tblRounds               tblTeamScores
Team_ID     Round_ID                Team_ID
Team_Name   Round_Number            Round_ID 
                                    Score

Here is your query to get the average score (qryAverageScores), which you can use as a datasource for a datagrid.

SELECT b.Team_Name, Avg(a.Score) As AverageScore
FROM ((tblTeamScores As a
INNER JOIN tblTeams AS b
ON a.Team_ID = b.Team_ID)
INNER JOIN tblRounds AS c
ON a.Round_ID = c.Round_ID)
GROUP BY b.Team_Name;

I'm a rookie but learning...