Results 1 to 2 of 2
  1. #1
    yousillygoose is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010

    Count function on query

    I have a question about using Count in Access.

    I have a database that includes every zip code, and the corresponding city and state for each zip code. NOTE:Each city may have more than one zip code associated to it.

    My task is to make a list in descending order of the cities. This list is intended to COUNT all of the instances of the city in the database. An exception must be made though: Only 1 instance of the city per state should be counted. This means that only 1 instance of a city name in each given state should be counted.

    I start off by loading in my ZIPCODES table in the Query Design windows. I then set Unique Values to "Yes" so that it only lists 1 instance of the city per state. I bring down State and City from my table. If I run this it creates a table of all of the cities and states, only listing each city once (per state). What I want to do is include a column next to the cities that counts the instances of the cities in the new table (not the original table). If I simply add another CITY field and set it to COUNT in the Total row it outputs the counts from the original ZIPCODES table and does not limit the results to the query.

    How do I go about doing this without creating a query, saving it, and loading that query to do the count.


  2. #2
    llkhoutx is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Houston, Texas USA
    Paste the following in the SQL view of a query:
    SELECT CityStateZipcodeCount(Zipcode AS ZipcodeCount
    FROM YourTableName
    GROUP BY City
    View the query in the QBE view to see how it's built.

    I built it in SQL so as to not use a database.

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

Similar Threads

  1. Count function in Query always Read Only?
    By terbs in forum Queries
    Replies: 3
    Last Post: 01-19-2010, 05:43 PM
  2. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  3. Running Count Query
    By monkey2003 in forum Queries
    Replies: 0
    Last Post: 09-21-2009, 12:24 PM
  4. Result of Count Query not known elsewhere
    By Carole in forum Access
    Replies: 1
    Last Post: 09-07-2008, 09:39 AM
  5. Query using count function
    By wasim_sono in forum Queries
    Replies: 0
    Last Post: 11-28-2007, 03:16 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