I have two tables:

tblEmployees:
EmployeeIDNum (primary key)
Name

tblEmployeeReviews
EmployeeReviewIDNum (primary key)
EmployeeIDNum (foreign key)
Score (an integer 1, 2 or 3)

This is a one-to-many relationship in that every employee will have multiple
reviews.

What I want is to create a query such that for each employee in
tblEmployees, I have three fields (Score1, Score2, Score3) containing the
percentage of that employee's Scores that were 1, 2 and 3. For each Employee
(i.e. each record in the query), these three fields should add up to 100%.

I figure I can start with tblEmployees and add fields using some form of the
DCount function eg. for Score1:

Score1: DCount("EmployeeReviewIDNum", "tblEmployeeReviews",
tblEmployeeReviews!EmployeeIDNum=tblEmployees!EmployeeIDNum AND
tblEmployeeReviews!Score= 1")/DCount("EmployeeReviewIDNum",
"tblEmployeeReviews",
tblEmployeeReviews!EmployeeIDNum=tblEmployees!EmployeeIDNum")

I can't get this to work. Does anyone know what I'm doing wrong or is there
an easier way to do this?

John

UNTESTED SQL Statement follows

SELECT E.Name
, Abs(Sum(Score=1)) as Ones
, Abs(Sum(Score=2)) as Twos
, Abs(Sum(Score=1)) as Threes
, Count(Score) as NumberReviews
, Ones/NumberReviews as OneScore
, Twos/NumberReviews as TwoScore
, Threes/NumberReviews as ThreeScore
FROM TblEmployees as E Inner Join tblEmployeeReviews as R
ON E.EmployeeIDNum = R.EmployeeID.Num
GROUP BY E.Name

You might have to substitute the following for the percentage calculations
Abs(Sum(Score=1)) /Count(Score) as OneScore

"John S. Ford, MD" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have two tables:
>
> tblEmployees:
> EmployeeIDNum (primary key)
> Name
>
> tblEmployeeReviews
> EmployeeReviewIDNum (primary key)
> EmployeeIDNum (foreign key)
> Score (an integer 1, 2 or 3)
>
> This is a one-to-many relationship in that every employee will have
> multiple
> reviews.
>
> What I want is to create a query such that for each employee in
> tblEmployees, I have three fields (Score1, Score2, Score3) containing the
> percentage of that employee's Scores that were 1, 2 and 3. For each
> Employee
> (i.e. each record in the query), these three fields should add up to 100%.
>
> I figure I can start with tblEmployees and add fields using some form of
> the
> DCount function eg. for Score1:
>
> Score1: DCount("EmployeeReviewIDNum", "tblEmployeeReviews",
> tblEmployeeReviews!EmployeeIDNum=tblEmployees!EmployeeIDNum AND
> tblEmployeeReviews!Score= 1")/DCount("EmployeeReviewIDNum",
> "tblEmployeeReviews",
> tblEmployeeReviews!EmployeeIDNum=tblEmployees!EmployeeIDNum")
>
> I can't get this to work. Does anyone know what I'm doing wrong or is
> there
> an easier way to do this?
>
> John
>
>
>

Thanks John, I'm going to try that. But shouldn't I use:

Abs(Count(Score=1))/Count(Score) As OneScore instead of

Abs(Sum(Score=1))/Count(Score) As OneScore?

John

"John Spencer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> UNTESTED SQL Statement follows
>
> SELECT E.Name
> , Abs(Sum(Score=1)) as Ones
> , Abs(Sum(Score=2)) as Twos
> , Abs(Sum(Score=1)) as Threes
> , Count(Score) as NumberReviews
> , Ones/NumberReviews as OneScore
> , Twos/NumberReviews as TwoScore
> , Threes/NumberReviews as ThreeScore
> FROM TblEmployees as E Inner Join tblEmployeeReviews as R
> ON E.EmployeeIDNum = R.EmployeeID.Num
> GROUP BY E.Name
>
> You might have to substitute the following for the percentage calculations
> Abs(Sum(Score=1)) /Count(Score) as OneScore
>
> "John S. Ford, MD" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I have two tables:
> >
> > tblEmployees:
> > EmployeeIDNum (primary key)
> > Name
> >
> > tblEmployeeReviews
> > EmployeeReviewIDNum (primary key)
> > EmployeeIDNum (foreign key)
> > Score (an integer 1, 2 or 3)
> >
> > This is a one-to-many relationship in that every employee will have
> > multiple
> > reviews.
> >
> > What I want is to create a query such that for each employee in
> > tblEmployees, I have three fields (Score1, Score2, Score3) containing
the
> > percentage of that employee's Scores that were 1, 2 and 3. For each
> > Employee
> > (i.e. each record in the query), these three fields should add up to
100%.
> >
> > I figure I can start with tblEmployees and add fields using some form of
> > the
> > DCount function eg. for Score1:
> >
> > Score1: DCount("EmployeeReviewIDNum", "tblEmployeeReviews",
> > tblEmployeeReviews!EmployeeIDNum=tblEmployees!EmployeeIDNum AND
> > tblEmployeeReviews!Score= 1")/DCount("EmployeeReviewIDNum",
> > "tblEmployeeReviews",
> > tblEmployeeReviews!EmployeeIDNum=tblEmployees!EmployeeIDNum")
> >
> > I can't get this to work. Does anyone know what I'm doing wrong or is
> > there
> > an easier way to do this?
> >
> > John
> >
> >
> >
>
>