Need help with this report: counting rows returned I THINK :S

[dragginthru]

Hi

I have been asked to do a report which for each training course provided, shows how many staff from each profession has attended (GPs, nurses, admin etc). The idea is that the trainers can then see if groups are getting enough training etc.

I have made the following query (this is the SQL view cut and pasted):

SELECT [Training Record].StaffID AS [Training Record_StaffID], [Training Record].Course_Date, [Staff Details].StaffID AS [Staff Details_StaffID], [Staff Details].Job_Title, [Training Record].CourseID, [Training Courses].Course_Title, [Training Record].CourseID
FROM [Training Courses] INNER JOIN ([Staff Details] INNER JOIN [Training Record] ON [Staff Details].StaffID = [Training Record].StaffID) ON ([Training Record].CourseID = [Training Courses].CourseID) AND ([Training Courses].CourseID = [Training Record].CourseID);

[staff details] contains staff ID and job titles
[training courses] contains course ID and course title
[training record] contains course ID and staff ID

The result I want (in a report) is:

COURSE GPs Nurses Admin
Introduction to practice nursing 4 46 7
Reception skills 0 5 17

So I guess I somehow need to show number of rows returned for each course, for each profession.... this is a bit over my head so maybe someone could help me with it?

Thanks!

 

[ScottGem]

use a Crosstab query