Criteria on Totals

[Lynn]

I have a report that summarizes how many times a student has not shown up for
training. It is based on a parameter query that prompts the user to enter a
Start Date & End Date. The query also includes critieria such as Cancelled =
0 and Attended = 0. The totals were calculated in the report by using the
following formula placed in the student group header:
=Count(qryStudentsAbsent!ScheduleID)

What I need to do now, is generate the same report but show only students
where =Count(qryStudentsAbsent!ScheduleID) >1

How can I do this? I tried using the total button in my query & specifying
the criteria there but it did not let me keep all the columns that I need.

 

[KARL DEWEY]

The totals were calculated in the report
Change to calculating in the query and set criteria to >1 for the count
column.

 

[Lynn]

Hi Karl,

I tried that...how can I group by student in the query, perform the count
calculation that I need but still keep all the other columns that I need?
When I used the sum button in my query, each column either had to be Group By
or a calculation.

 

[KARL DEWEY]

If you know subqueries then do that. Otherwise create a second query to just
count for the student. Then inner join that query in your regular select
query.
If you can not follow what I laid out then post your query SQL. Open query
in design view, click on menu VIEW - SQL View, highlight all, copy, and paste
in a post.

 

[Lynn]

Hi Karl,

Your help is much appreciated. I almost have the results I want but the
problem now is that the parameter in my regular select query is not being
applied to the count in my other query. I want it to only display the # of
times a student was absent more than once within the given date parameters.
Here is my code:

Query that counts (qryStudentsAbsentRepeat1)
SELECT dbo_tblRegistrations1.EmployeeNum,
Sum(IIf(dbo_tblRegistrations1!Attended=0,1,0)) AS NotAttend
FROM dbo_tblRegistrations1
GROUP BY dbo_tblRegistrations1.EmployeeNum
HAVING (((Sum(IIf([dbo_tblRegistrations1]![Attended]=0,1,0)))>1));


Regular Select Query (qryStudentsAbsentRepeat2)
SELECT dbo_tblParticipants1.EmployeeID, dbo_tblParticipants1!FirstName & " "
& dbo_tblParticipants1!LastName AS [Student Name],
qryStudentsAbsentRepeat1.NotAttend, dbo_tblScheduledCourses1.Date, [Start
Date] AS Start, [End Date] AS [End], dbo_tblParticipants1.Email,
dbo_tblParticipants1.Department, dbo_tblParticipants1.JobTitle,
dbo_tblParticipants1.PhoneNum
FROM (dbo_tblParticipants1 INNER JOIN ((dbo_tblCoursesEvents1 INNER JOIN
dbo_tblScheduledCourses1 ON dbo_tblCoursesEvents1.CourseID =
dbo_tblScheduledCourses1.CourseID) INNER JOIN dbo_tblRegistrations1 ON
dbo_tblScheduledCourses1.ScheduleID = dbo_tblRegistrations1.ScheduleID) ON
dbo_tblParticipants1.EmployeeID = dbo_tblRegistrations1.EmployeeNum) INNER
JOIN qryStudentsAbsentRepeat1 ON dbo_tblParticipants1.EmployeeID =
qryStudentsAbsentRepeat1.EmployeeNum
WHERE (((dbo_tblScheduledCourses1.Date) Between [Start Date] And [End Date]));