Count where

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hello All, I'm kind of desperate for an answer so please help me. It's my
birthday!

I'm trying to find out the number of classeses a lecturer teaches. I need
to display this for each lecturer.

It will produce a result like

lecturer Courses
----------------- -------
John smith 7
Samantha Jones 2
Pete Pergo 9
....

Tables

tblLecturer
-------------
lecturer ID
FName
SName

tblLectCourse
---------------
LectCourseID
LecturerID
CourseID

tblCourse
-------------
CourseID
course

I would like this result to be displayed on a listbox so if it coud be based
on a query then this would be my preffered option.
 
To build the query through the graphical query builder, open a new query and
drag tblLecturer and tblLectCourse into the top half (you don't need to use
tblCourse in this query).

Make sure that there's a line connecting the two tables on LecturerID (the
line will occur naturally if you've added relationships to the database,
otherwise you'll have to add it yourself)

In the grid, type "Lecturer: [tblLecturer].[FName] & " " &
[tblLecturer].[SName]" (without the first and last quote) into the Field row
of the first column, and drag CourseID from the tblLectCourse table into the
Field row of the second column.

Change the query into a Totals query (either by clicking on the Sigma icon
on the button bar, or by clicking on Totals on the View menu). Make sure it
says "Group By" in the Total row of the first column (under the field you
typed above), and select "Count" for the Total row of the second column
(under CourseID).

(Optional) Change what's in the Field row of the second column from CourseID
to "Courses: [tblLectCourse].[CourseID]" (again, without the quotes).

You can sort by either field.

Hope this helps.
 
Back
Top