Creating a special query

Topic

New Reply

How would I be able to get my user to pull of all cities in a state. For example, if I wanted to put every city for every state in my database, what do I need to do to be able to pull up all cities in one state.

Reply | Quote

Replies

It could be as simple as a parameter query that asks for a state, if I understand your question.

Reply | Quote

How would I set up my tables for this? Do I created one table with all the states and under each state I put their cities? Or would I create 50 separate tables?

Reply | Quote

You need 2 tables:

tblState has one record for each state. You could include fields for the state abbreviation and the spelled out state name.

tblCity has one record for each city in a given state. That record would have a field for the city name, a field for the StateID (the primary key from the state table) for that City, and anything else you wanted to store about that city.

Reply | Quote

Charlotte, in the tblCity, you wouldn’t want a primary key field, would you? Just courious.

frmorris, did Charlotte’s recommendation help?

Reply | Quote

Yes, you would. I didn’t include every field because I was trying to explain the relationship. The Primary Key for the state table would be a foreign key in the City table. But each city/state combination should have its own unique key, especially since city names are not unique.

Reply | Quote

Yes. Charlotte answer was a huge help. I have everything running smoothly now.

Reply | Quote

Thank you very much Charlotte. Your suggestion was a huge help. Everything is running smoothly.

Reply | Quote