Calculating Totals and Percentages

[Guest]

I am creating a database for trainees. Every case the trainees complete will
have to be reviewed for accuracy. Each review is entered into a single
table. There is a field in the table that tells me if the case was correct
(YES/NO). I would like to have a report that tells me how many cases a
certain trainee completed and his/her accuracy rate (% correct) I am
stumped. How do I create a query that will give me this information?

 

[Guest]

You should not use separate tables for each review. I think this table
structure will work for you.
tblTrainee--
TrainID - Autonumber - Primary Key (PK)
LName - text
FName - text
MI - text
etc

tblReviews--
ReviewID - Autonumber - PK
Title - text
Discription - text (limit of 255 characters) or memo

tblTrainReview --
TrainID - number - long interger
ReviewID - onumber - long interger
ReviewDate - date
Complete - Yes/No
CMTS - text

Create a one-to-many relation tblTrainee.TrainID to tblTrainReview.TrainID
Create a one-to-many relation tblReviews.ReviewID to tblTrainReview.ReviewID

Use this query --
SELECT tblTrainee.LName, tblTrainee.FName, tblTrainee.MI,
Sum(IIf([Complete]=-1,1,0))/Count([Complete]) AS [Accuracy Rate]
FROM tblReview INNER JOIN (tblTrainee INNER JOIN tblTrainReview ON
tblTrainee.lTrainID = tblTrainReview.TrainID) ON tblReview.ReviewID =
tblTrainReview.ReviewID
GROUP BY tblTrainee.LName, tblTrainee.FName, tblTrainee.MI;

Set format as percent.