In a query to return conditional counts you SUM an expression which returns a
value of 0 or 1 depending on the value in the column for each row, e.g.
SELECT StudentID, FirstName, LastName,
SUM(IIF(Result = "Pass", 1,0)) AS Passes,
SUM(IIF(Result = "Fail", 1,0)) AS Failures
FROM Students INNER JOIN Exams
ON Exam.StudentID = Student.StudentID
GROUP BY StudentID, LastName, FirstName;
That would give you the number of passes and failures for each student,
using tables Students and Exams in a on-to-many relationship. To do this in
query design view, having added the tables and added the StudentID, FirstName
and LastName columns to the design grid, you'd then select Totals from the
View menu and enter:
Passes: IIf([Result] = "Pass", 1,0)
Failures: IIf([Result] = "Fail", 1,0)
in the 'field' rows of two blank columns in the design grid and select 'Sum'
in the 'Totals' row to create the computed Passes and Failures columns. The
StudentID, FirstName and LastName columns would be left as 'Group By' in the
'Totals' row.
Alternatively you could base a report on a query such as:
SELECT StudentID, FirstName, LastName, Result
FROM Students INNER JOIN Exams
ON Exam.StudentID = Student.StudentID;
and group the report on StudentID, putting the first and last names in the
group header, the result in the detail section and in the group footer put
two unbound text boxes with ControlSource properties of for passes:
=Sum(IIf([Result] = "Pass", 1,0))
and for failures:
=Sum(IIf([Result] = "Fail", 1,0))
To get grand totals for the whole report put identical text boxes in the
report footer.
BTW don't be tempted to use an expression such as =Sum(Abs([Result] =
"Pass")). This relies on the implementation of Boolean True or False values
as -1 or 0; reliance on the implementation is not best practice and should be
avoided.
However, when you say you need the totals 'at the bottom of each page', if
this is a page of a report then you can't use text boxes with ControlSource
properties as above in a Page Footer as aggregating functions like Sum can
only be used in report or group footers. To give totals per page put unbound
text boxes in the page footer, txtTotalPasses and txtTotalFailures say,
leaving their ControlSource properties blank. You then need to compute the
values for them in code like so:
1. In the Page Header's Print event procedure initialize the two text boxes
to zero with:
Me.txtTotalPasses = 0
Me.txtTotalFailures
2. In the detail section's Print event procedure increment their values,
examining the PrintCount property to avoid any inadvertent double counting:
If PrintCount = 1
If Me.Result = "Pass" Then
Me.txtTotalPasses = Me.txtTotalPasses + 1
Else
If Me.Result = "Fail" Then
Me.txtTotalFailures = Me.txtTotalFailures + 1
End If
End If
If there's anything in the above on which you are unclear please don't
hesitate to post back.
Ken Sheridan
Stafford, England
