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.
Thanks