need help with sql and group by

[Danny]

Hello all

I need an sql statement that will do this:

this is my database

name testscore occurance
carol 33 1
carol 99 2
gene 39 1
paul 32 1
carol 45 3
gene 100 2
gene 11 3
gene 44 4

I want the result to be:
name avg(testscore) occurance
carol 59 1
carol 59 2
carol 59 3
gene 48.5 1
gene 48.5 2
gene 48.5 3
gene 48.5 4
paul 32 1
I want to average the scores for each name, but then list each instance of
the name next to the average.
I know I can do this in a few steps with a group by on the average for each
name, and then link to original database

but is there an easier way to do this using one line of sql?

Thanks for your time
Danny

[Ken Snell]

SELECT [Name], DAvg("testscore", "TableName", "[Name]='" & [Name] & "'")
AS AvgTestScore, Occurance
FROM TableName;

--

Ken Snell
<MS ACCESS MVP>

"Danny" <da********@hotmail.com> wrote in message
news:2C*********************@news4.srv.hcvlny.cv.n et...

Hello all

I need an sql statement that will do this:

this is my database

name testscore occurance
carol 33 1
carol 99 2
gene 39 1
paul 32 1
carol 45 3
gene 100 2
gene 11 3
gene 44 4

I want the result to be:
name avg(testscore) occurance
carol 59 1
carol 59 2
carol 59 3
gene 48.5 1
gene 48.5 2
gene 48.5 3
gene 48.5 4
paul 32 1
I want to average the scores for each name, but then list each instance of
the name next to the average.
I know I can do this in a few steps with a group by on the average for each name, and then link to original database

but is there an easier way to do this using one line of sql?

Thanks for your time
Danny