Jump to content

Welcome to Geeks to Go - Register now for FREE

Need help with your computer or device? Want to learn new tech skills? You're in the right place!
Geeks to Go is a friendly community of tech experts who can solve any problem you have. Just create a free account and post your question. Our volunteers will reply quickly and guide you through the steps. Don't let tech troubles stop you. Join Geeks to Go now and get the support you need!

How it Works Create Account
Photo

MS Access Query


  • Please log in to reply

#1
jholc

jholc

    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

Advertisements


#2
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
jholc,
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.

tblLocn
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

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


Hope this helps

dsm

Attached Files


  • 0

#3
jholc

jholc

    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