Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Posts
    5

    Unanswered: Count records in a query ?

    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.

    TIA...

  2. #2
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    SELECT Count(tblDepartmentEmployees.Employee) AS CountOfEmployee, tblDepartmentEmployees.Department
    FROM tblDepartmentEmployees
    GROUP BY tblDepartmentEmployees.Department;

    Hope this helps.....

  3. #3
    Join Date
    Dec 2001
    Posts
    9
    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.

    Hope this helps.

  4. #4
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    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.

  5. #5
    Join Date
    Jan 2002
    Posts
    5
    Originally posted by Torgue
    SELECT Count(tblDepartmentEmployees.Employee) AS CountOfEmployee, tblDepartmentEmployees.Department
    FROM tblDepartmentEmployees
    GROUP BY tblDepartmentEmployees.Department;

    Hope this helps.....
    Torque,

    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...

    TIA

  6. #6
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    in the underlying query.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •