Creating calculated fields for a query using data from another table

[John S. Ford, MD]

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

 

[John Spencer]

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]

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