Results 1 to 3 of 3
  1. #1
    vsmk is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    2

    Post Count of Unique Values

    In the query given below which is done in Access

    SELECT [Q Test].[Lead Business], [Q Test].Region, Avg([Q Test].[Cycle time days]) AS [AvgOfCycle time days], Count([Q Test].[Contract ID]) AS [CountOfContract ID]
    FROM [Q Test]


    GROUP BY [Q Test].[Lead Business], [Q Test].Region;

    The field Count([Q Test].[Contract ID]) lists the count of contract ids including duplicates.

    What should I do to let Access Return only count of unique values. For example, there are 100 records having contract id out of which only 10 are unique, balance 90 are repetitions.

    While currently access returns 100 for the field, I want access to return 10 which is unique values for the field.

    Any help would be much appreciated.

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    try to add the word DISTINCT right after the word SELECT.

  3. #3
    vsmk is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    2
    Thanks for your attempt. It is not as simple as that!

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

Similar Threads

  1. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  2. how to set a unique ID
    By archie in forum Access
    Replies: 1
    Last Post: 09-08-2009, 04:28 AM
  3. Need Help Summing UNIQUE Records
    By Millerguitarworks in forum Access
    Replies: 5
    Last Post: 05-27-2009, 04:37 PM
  4. Unique Puzzle
    By Christina G in forum Database Design
    Replies: 0
    Last Post: 03-14-2009, 06:37 PM
  5. query that will contain all unique records
    By halcolm1 in forum Queries
    Replies: 0
    Last Post: 01-19-2007, 05:34 PM

Tags for this Thread

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