Results 1 to 3 of 3
  1. #1
    atran is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    4

    Another unique count question

    Hi,

    I have a database with addresses of fastfood restaurants throughout California, broken down by county. I am trying to create a report to list the different fastfood restaurants in the state and include a count for how many counties have at least one of those restaurants. For example, there may be 50 El Pollo Loco's in California, but I want to know how many counties have at least one of them. So an example of what it would look like is as follows:

    Del Taco: 48 counties
    McDonald's: 58 counties
    El Pollo Loco: 23 counties


    etc.

    I'm fairly green when it comes to this stuff, and I have scoured the internet for the answer to no avail. I have found methods to do counts of unique records which when applied, I can pull the number of counties in California or the number of different types of fastfood restaurants from my database, but I have yet to find something that allows me to get unique counts from a list that is being populated in the report (list of restaurants).

    Any help would be much appreciated!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Try:

    SELECT Restaurant, Count(County) AS CountOfCounty
    FROM (SELECT Restaurant, County FROM table GROUP BY Restaurant, County) As Q1
    GROUP BY Restaurant;

    Since this is a nested query, open query designer in SQL View and paste the statement and edit as appropriate for field and table names. Otherwise, build two Access query objects with the query design grid.
    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
    atran is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    4
    That worked beautifully. Thank you!

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

Similar Threads

  1. Access Unique Count
    By spherehunter in forum Programming
    Replies: 5
    Last Post: 05-17-2012, 11:42 AM
  2. Unique queries - Unique Count
    By bigfish in forum Queries
    Replies: 7
    Last Post: 03-14-2012, 01:28 PM
  3. Count unique records - no duplicates
    By Kevo in forum Queries
    Replies: 4
    Last Post: 08-15-2011, 01:19 AM
  4. Count Unique Records
    By PonderingAccess in forum Queries
    Replies: 2
    Last Post: 08-19-2010, 06:54 AM
  5. Count of Unique Values
    By vsmk in forum Queries
    Replies: 2
    Last Post: 03-14-2010, 12:07 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