Results 1 to 3 of 3
  1. #1
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82

    How to count distinct records? COUNT() is counting ALL records...

    Hi All,

    I have a table which tracks all the courses an employee has taken. They may have multiple entries for multiple courses. For example, they may have taken first aid several times through their career. I am trying to generate a report that count the number of employees that have taken the course, but the query I currently have is counting employees multiple times. Even though the office may only have 10 employees, the query using count() is saying that 15 employees have taken it.



    I have tried doing a SELECT DISTINCT, but even though it returns the proper records, it still returns the total count. Is there a way to only count the records returned???

    Why can't this be easy???

    Thanks,

    Scott

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Depending on the fields you show alters distinct.
    make Q1 to pull only the fields you need to count.
    select distinct CoName from tCompanies.

    then Q2 to count the recs...
    select count(coName) as CoCount from Q1

  3. #3
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    Makes sense. Now to make it a little more challenging! I have a district where I am trying to get the count for each office. So I've created qry1 with distinct employees in the district. It shows 72 records. qry2 is count(*) from qry1. It returns one value, 72. Perfect. Now for the count of each office.

    qry1:
    Code:
    SELECT DISTINCT tblOffices.District, tblOffices.OfficeNO, tblOffices.OfficeName, tblIndividualLearning.CatelogID, tblEmployee.Active, tblIndividualLearning.EmpID
    FROM tblOffices INNER JOIN (tblEmployee INNER JOIN tblIndividualLearning ON (tblEmployee.EmpID = tblIndividualLearning.EmpID) AND (tblEmployee.EmpID = tblIndividualLearning.EmpID)) ON tblOffices.ID = tblEmployee.Office
    GROUP BY tblOffices.District, tblOffices.OfficeNO, tblOffices.OfficeName, tblIndividualLearning.CatelogID, tblEmployee.Active, tblIndividualLearning.EmpID
    HAVING (((tblOffices.District)=54) AND ((tblIndividualLearning.CatelogID)=15) AND ((tblEmployee.Active)=True));
    qry2:
    Code:
    SELECT Count(*) AS TotalNO
    FROM qry1;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Distinct Count of Records on an Access Report
    By vinsavant in forum Access
    Replies: 5
    Last Post: 01-14-2018, 08:19 AM
  2. Replies: 6
    Last Post: 06-20-2017, 08:56 AM
  3. Replies: 5
    Last Post: 02-24-2017, 08:09 AM
  4. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  5. Count distinct records in parameterized query
    By SilverSN95 in forum Access
    Replies: 5
    Last Post: 07-27-2010, 09:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums