Results 1 to 5 of 5
  1. #1
    Kevo is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2011
    Location
    Kodiak, Alaska
    Posts
    86

    Count unique records - no duplicates

    Good Afternoon,



    Can you tell me how to have the "Count" function in a query only display unique count?

    i.e. - I want to count total "Loads" per "Department", trouble is that "Department" is only referenced in the "LoadDetails" table....

    LoadID - 19, Department - Surgery, Instrument - Clamp, Qty - 2

    LoadID - 19, Department - Surgery, Instrument - Scissors, Qty - 4

    etc...

    I want the query to count the above as 1 Load for Surgery on specified date.

    Thanks for any help!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    Try a report. Use the report Grouping & Sorting with aggregate calcs. Group by date and Department. Do Count calc in the Department group header. If you want to show other details of records that contribute to this, such as the instruments, put those fields in the Detail section.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Kevo is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2011
    Location
    Kodiak, Alaska
    Posts
    86
    Thank you June7, but I am really looking for a query or other way to display this info...

    I have attached my db if anyone cares to look at the issue.

    The funtionality I would like is under the "Main Menu", click "Query Menu" and see the Total Loads Query....It gives user option to enter date range.

    I would like to add a combo box for "Department" and have the total loads be total for "Department" during the date range.

    Thanks for looking.

  4. #4
    Kevo is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2011
    Location
    Kodiak, Alaska
    Posts
    86
    Well I found this and it seems to work as far as returning distinct record count. It also does not count nulls.

    SELECT count(*) FROM (SELECT DISTINCT LoadID FROM tblLoadDetails WHERE LoadID is not NULL);

    Now, how am I gonna get it to return count for only certain "Departments" according to user input in combobox? I know I want something like:

    Where DepartmentID = Forms!frmQueryMenu.cboDepartment but, where to put it in the above statement?

    Any ideas?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    Try this:

    SELECT Count(Q1.DepartmentID) AS CountOfDepartmentID, Q1.DepartmentID, Q1.LoadDate
    FROM (SELECT [tblLoadDetails].DepartmentID, tblLoad.LoadDate FROM tblLoadDetails LEFT JOIN tblLoad ON [tblLoadDetails].LoadID = tblLoad.LoadID WHERE tblLoad.LoadID Is Not Null GROUP BY [tblLoadDetails].DepartmentID, tblLoad.LoadDate) AS Q1 GROUP BY Q1.DepartmentID, Q1.LoadDate
    HAVING (((Q1.DepartmentID)=[Forms]![frmQueryMenu].[cbxDept]) AND ((Q1.LoadDate)>=[Forms]![frmQueryMenu].[btnStartDate] And (Q1.LoadDate)<[Forms]![frmQueryMenu].[btnEndDate]));


    I do wonder why tblLoadDetails records would not have LoadID
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Don't count duplicates
    By shenberry in forum Queries
    Replies: 1
    Last Post: 10-27-2010, 09:28 PM
  2. Count Unique Records
    By PonderingAccess in forum Queries
    Replies: 2
    Last Post: 08-19-2010, 06:54 AM
  3. Replies: 1
    Last Post: 08-18-2010, 02:36 AM
  4. Count of Unique Values
    By vsmk in forum Queries
    Replies: 2
    Last Post: 03-14-2010, 12:07 AM
  5. query that will contain all unique records
    By halcolm1 in forum Queries
    Replies: 0
    Last Post: 01-19-2007, 05:34 PM

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