Trying to get the avg of records...........

[Guest]

Hello Everyone,

I am trying to get a average per game for my players and I am having
troubles using avg function

In my query I can setup an expression to calculate the Total Points (3PTM*3,
2PTM*2, FMT*1) for each game but now I want to calculate AVG Points per game.


Also, is it better to caluculate expressions in the report or in a query?

I am teaching myself, is there any good book that will help me with these
kind of questions, I bought the Microsoft book but it deals with a existing
database, I want to create one from scratch and want to know the fundamentals
of building a database.

Thanks for your help.

Tony

 

[Al Camp]

Tony,
The Average calculation involves adding a series of numbers and dividing
by the count of numbers added. Your example does not indicate any addition
or division. The average of 3PTM*3 = 3PTM.
Please be clearer as to what "average" is trying to calculate, with field
names and values... and simple examples. Please also indicate any
"grouping" that might effect the calculation.

Regarding Averages in the query vs. in the report... if there is a
calculation needed to get the score that will be averaged on the report, do
it in the query. A calculated field, calculated only on the report, can not
be Averaged (or Summed, or Max, etc...) in the report footers. If the
score to be averaged is a "bound" field value, they can be directly averaged
in any footer.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Hello Al,

Thanks for your help.

Here is a little more background on my project.

I coach basketball and I am trying to use an access database to keep track
of stats. I input the stats for every game into a table. Each record
contains players name, 2PTM, 2PTA, Turnovers, etc..... for that game.

I have a season summary query that combines the stats for the season for
each player. Now I want to start doing avg per game for each player. The
formula in ther query below ((3PTM*3)+(2PTM*2)+( FTM*1)) calculates total
points for that record but I nee to also take the total points and divide
that by the number of records, because the only time I create a record is if
the player plays.

So I need the field total/number of games (number of records for that
player).

Thanks for the help and I am looking forward to hearing back from you.

 

[Al Camp]

I think I understand... but... not knowing your table structure (Games
associated to players, or players associated to games), try this...

You want to divide a Players current point score by the number of games that
player played in.

Each player in your database should have a unique identifier field... a key
field... that is associated only to that player. An example would be a
[PlayerID]

To calculate their average on a form with a PlayerID and his scores...
divide the sum ((3PTM*3)+(2PTM*2)+( FTM*1)) by...

=DCount("[PlayerID]","tblYourGameTable","[PlayerID] = " & [PlayerID])

(use your own names)
That should count every record where that player has a score.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions