Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

MS Access Query

  • Please log in to reply



    New Member

  • Member
  • Pip
  • 4 posts
I'm working with a table that only has two fields; a list cities, and a list of addresses within those cities. Naturally each city occurs many times within the table, while the addresses are unique. I need a query to give me a list of all the cities in the table. Sounds simple, but me and my reference book can't figure it out. Any suggestions?

I'm using Access 2003.
  • 0





  • Member
  • PipPip
  • 98 posts
I have taken a guess at what your data looks like from the description you provided.
Lets assume your table is named tblLocn and the 2 fields are City and Address.

City, Address
Paris, Le Bigge St
London, Badger St
New York, Squirrel St
Sydney, Kangaroo St
Auckland, Kiwi St
Paris, Eiffel St

To obtain a unique list of Cities you can create a group by query on the City field.
I have shown below the SQL output of a suitable group by query.
A group by query is acheived in the query design mode by clicking the sum/total button.
The attached file has a screen shot of the query design panel from Access 97

SELECT tblLocn.City, Count(tblLocn.City) AS CountOfCity
FROM tblLocn
GROUP BY tblLocn.City
ORDER BY tblLocn.City;

Hope this helps


Attached Files

  • 0



    New Member

  • Topic Starter
  • Member
  • Pip
  • 4 posts
This is exactly what I was looking for. Thank you for your help.
  • 0

Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP