Country - State - City

[kimx]

Hello All,

I come across a question and don't know how to integrate the followin into my tables.
This must be very common as it's seen/used everywhere on the Internet etc...

I need people to choose locations for customers, companies etc...
Let's say, my table tblCustomers needs to contain in which city/country/state people live.

3 columns:
- tblCountries with CountryID
- tblStates with StateID and link to countries fkCountryID
- tblCities with CityID and link to countries fkCountryID and link to states fkStateID

A country can be 'United States', so in this case a state is needed and a city within this state.
If a country is not the US, the customer only needs country and city.
First I thought to only put fkCityID in my customers table as for non-US, the country is known as well
through fkCountryID in tblCities. But... the city is not always known so only the
country will be provided.

So... do I need to put 3 fields in my customer table:
fkCountryID, fkStateID, fkCityID and see which ones are filled in ?
Do these 3 tables make sense or... ?

Does anybody did something similar before ? Any suggestions or tips about how this is done
so many times ???

Thanks sooo much.

Evelyne

 

[AlexMidd]

Not much help but I have been trying to do something similar for an expenses database. Basically. I need certain categories to have different data to be entered from others, i.e. different types of fields and different numbers of fields. I have not been able to solve this problem, so if anyone can help kimx, it may help me too. Have fun! )

Alex Middleton

 

[braindead2]

check out this FAQ listed under access froms FAQ section. Sounds like what you have in mind

http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/27/spid/702/sfid/681

 

[tlbroadbent]

Use the three reference tables and include three columns on the customers table - one for country, one for state and one for city. You would then join the tables using OUTER JOINS rather than INNER JOINS to account for the possibility of null columns in the customers table.

Select a.*, b.CountryName, c.StateName, d.CityName
From tblCustomers a
Left Join tblCountries b
On a.CountryID=b.CountryID
Left Join tblStates b
On a.StateID=b.StateID
Left Join tblCountries b
On a.CityID=b.CityID

This query would pull matching data from the reference tables and allow records to exist on tblCustomers that did not have all 3 coulmns populated. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln