Results 1 to 8 of 8
  1. #1
    bigfish is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    5

    Unique queries - Unique Count

    Hi everyone!

    I have a query there, associated with one table.
    One column is the year, the next is the specific month, and the next columns are the cities with the number of brands associated to the respective city. My problem is that I can't seem to be able to count unique brands for each city. It only counts the sum of them. For instance, for the city of Seattle, by manual count I get 127 brands, but the query Count shows me 169 brands.
    Cheers!

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    SELECT City, Count(Brands) As CountOfBrands
    FROM TableName
    GROUP BY City;

    That's should work. If the number you get is more, then there are duplicates for City>Brand

  3. #3
    bigfish is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    5
    Thanks for your answer.

    I had this, but it doesn't count uniques!

  4. #4
    bigfish is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    5
    I think I should use the Distinct function, anyone could help me with that?
    I already have TRANSFORM Count(TableName.BrandID) AS CountOfBrandID
    How do I include the Distinct in this?

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post your query Sql?

    Have you tried Count and Group By?
    http://www.techonthenet.com/access/f...eric/count.php

  6. #6
    bigfish is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    5
    TRANSFORM Count(tablename.BrandID) AS CountOfBrandID
    SELECT tablename.Year, tablename.Month
    FROM tablename
    GROUP BY tablename.Year, tablename.Month
    PIVOT tablename.City;

  7. #7
    bigfish is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    5
    Any idea?
    Cheers

  8. #8
    Bdowns is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    28
    Hey bigfish, I am also trying to count the number of unique entries. I am trying to count the number of unique organisms by one minute bin from an ecological survey. In some minutes, the same organism type is seen over an over. If you figure out how to count not total but unique entries, please let me know how it is done. If I figure it out, I'll let you know. Thanks

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

Similar Threads

  1. Count unique records - no duplicates
    By Kevo in forum Queries
    Replies: 4
    Last Post: 08-15-2011, 01:19 AM
  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. how to set a unique ID
    By archie in forum Access
    Replies: 1
    Last Post: 09-08-2009, 04:28 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