I have several queries which are used to sort employees into departments. I now want to create a report which shows the number of employees in each department i.e. the number of records returned by the query. I am using Access2000.
You could use the code that was provided in the previous message, or for a simpler solution...
In your report, you are probaby grouping on the department field, right? Just add a field using this expression: =count([employee]) and put the field in your Department Header or Footer. This will count the number of employees within each department.
absolutely.... just to distinguish between the two methods, use my query if you only want that information in a separate report, and use Melanie's solution if you want the grouping to be part of a report that displays your individual records, and you want to add a a grouping total on the bottom of the report, or section.
I don't really consider either of them difficult, the query builder of Access makes it really easy to group.
Originally posted by Torgue SELECT Count(tblDepartmentEmployees.Employee) AS CountOfEmployee, tblDepartmentEmployees.Department
GROUP BY tblDepartmentEmployees.Department;
Hope this helps.....
Melanies method is not really what I am after as I have to create a summary report...all the record details are not needed. Could you please clarify where your commands should be used ? ; in the actual report, in the underlying query or in a control somewhere ?.
This is turning out to be unexpectedly difficult...and it is not over yet as I will have some cross referemces and calculations to put in...
Go to query design mode, SQL view, and then paste the SQL statement in there. You can toggle back to design mode to see how Access nicely and clearly displays it in query builder mode, here you can also add some stuff if you want.