Can't figure out expr needed

[dp1981]

I finally am figuring out this Access db stuff. I have managed to produce what I believe are normalized tables, built a query or two, and now a report. However, what I need to do now is something that I don't even know is possible. Further explanation is that I need to produce a query that automatically counts how many of a particular course has been taken. So, in a sense the query would show that 500 people have taken course A. If I have to build a query for each course, that is fine (because there is a course B, C, D, etc.). The problem is that the tbl the courses are contained within has the following properties:

EmployeeCourseID [auto#]
EmployeeID [#]
CourseID [#]
CompletionDate [date/time]

I thought the easiest way to build a query would be by the CourseID. Problem? Well obviously ALL courses are contained within this field/column.

I was able to produce a query with the following SQL that produced totals for all courses...which was fine.

SELECT tbl_EmployeeCourses.CourseID, Count(*) AS Expr1
FROM tbl_EmpoyeeCourses
GROUP BY tbl_EmployeeCourses.CourseID;

But the problem is that I can't get these totals in their OWN queries. When I print the report, I want it to say in the report footer (obviously in separate textboxes) "Course A total:___" or "Course B total:___"

I know I will later be able to build on that to produce a grand total if the need should arise. but it's pulling these apart now that's whipping me.

Does anyone have any pointers or can tell me what I am doing wrong please? I would greatly appreciate it. Thanks so much!

 

[neileg]

If you are grouping all the courses in the report, i.e. all Course A followed by the count of Course A, then all Course B followed by the count of Course B, then you don't do the count in the query, you do it in the report.

 

[dp1981]

If you are grouping all the courses in the report, i.e. all Course A followed by the count of Course A, then all Course B followed by the count of Course B, then you don't do the count in the query, you do it in the report.

That's the problem. The way my supervisor wants the report is grouped by employee location. So the report has many different locations. Then alphabetical employees. Then the courses/dates. That's why I can't get it to work by just counting over groups in the report. But thanks for the quick response!

 

[Rich]

Create a ToTALS query, group by Location and then Count use DLookup on the query in the Footer to get the results