Calculate percentage completed from total attendees

[Guest]

I need to take the total attendees of a particular course and calculate from
that total a percentage of attendees who completed the course. How on earth
do I accomplish that? Any help would be greatly appreciated. I know how to
sort reports "six ways from Sunday" but not that much about filters, etc.

 

[[MVP] S.Clark]

Percent = Part / Whole

So, you first need to create the Whole query.
Next, create the Part query, which links in the Whole query, such that the
Percent can be calculated.

For future query questions, visit the m.p.a.Queries newsgroup.

 

[Guest]

Lets assume you have a table CourseAttendances which has two numeric columns
CourseID and AttendeeID which reference the primary keys of tables Courses
and Attendees. If this table also has a Boolean (Yes/No) column, Completed
to indicate if the attendee completed the course in question then this can be
used to count the attendees who completed the course. If the couse ID is 42
you'd do it in a query like so:

SELECT
(SELECT COUNT(*)
FROM CourseAttendances
WHERE Completed
AND CourseID = 42)/
COUNT(*) *100 As PercentCompleted
FROM CourseAttendances
WHERE CourseID = 42;

What this does is divide the value returned by the subquery (the rows where
Completed is True) by the result returned by the COUNT(*) in the outer query
(all rows). Both the outer and inner query are restricted to rows for course
42.

You could do the same with a text box on a form or report using the
DCountFunction. The ControlSource for the text box would be:

=DCount("*","CourseAttendances","CourseID = 42 And Completed")/
DCount("*","CourseAttendances","CourseID = 42") * 100

In reality you'd probably not hard code the value 42, but get the CourseID
as a parameter, probably a value of a control on a form.

If you have report which lists all the attendees at a particular course you
can do it in a text box in the report footer with a ControlSource of:

=Sum(IIf(Completed,1,0))/Count(*)*100

In this case the count of all who completed the course is obtained by
summing the result of an expression which returns 1 if an attendee completed,
0 if not. If the report is for a number of courses you can put the text box
in the Course group footer to get percentages for each course.