City and Provinces

I've read a few posts that deal with address information but am not getting a
consistent view from the experts here so I thought I would ask the question.

I am creating a database that will contain address information. Many of the
cities and provinces will be the same. Should I have a separate tblCity and
tblProv tables with the tblCity linking to the tblProv table?

I am looking at ease of data entry also. I would like the users to be able
to easily select the information. Should they just select from a list and if
not in the list, add it. Ideally a combo box will include an city and
province combination in the file for use in any of three possible tables that
will include address information. Or should my users select the province and
this displays a list of cities (this was suggested by a user). Or should
they select a city and the province show up in the next combo? If the
province is wrong then they change it to one that is correct? eg. Toronto
can be in Ontario or PEI.

TIA rasinc

from a tables standpoint, as long as all you're dealing with are *names* of
cities only (and NO other data that describes a city), recommend a
tblProvinces, a tblCities, and a tblProvinceCities. since one province may
have many cities, and one city *name* may appear in multiple provinces,
there is a many-to-many relationship between those two entities.
tblProvinceCities provides the link between the two with the following
one-to-many relationships, as

tblProvinces.ProvinceID 1:n tblProvincesCities.ProvinceID
tblCities.CityID 1:n tblProvinceCities.CityID

as you've seen, there are several approaches to this, from a data entry
standpoint. i'd recommend that you listen to your user(s). table design is
based on relational data principles, but form design should support the
process that the user follows. so if it makes more sense from the users'
standpoint to choose from a list of provinces first, then choose from a
filtered list of cities in that province - that's what you should give them.

one word of caution: be careful in letting your user(s) update the table
underlying a combo box control's RowSource. you should set it up so that the
user must put a modicum of thought into adding new cities; make it too slick
and easy, and you'll end up with a mess on your hands - incomplete and
misspelled entries that proliferate through the data records. from the
standpoint of aggregating data, that's a disaster - and guess who will have
to go into the tables and clean it up, again and again!

hth


"rasinc" wrote in message
...
I've read a few posts that deal with address information but am not
getting a
consistent view from the experts here so I thought I would ask the
question.

I am creating a database that will contain address information. Many of
the
cities and provinces will be the same. Should I have a separate tblCity
and
tblProv tables with the tblCity linking to the tblProv table?

I am looking at ease of data entry also. I would like the users to be
able
to easily select the information. Should they just select from a list and
if
not in the list, add it. Ideally a combo box will include an city and
province combination in the file for use in any of three possible tables
that
will include address information. Or should my users select the province
and
this displays a list of cities (this was suggested by a user). Or should
they select a city and the province show up in the next combo? If the
province is wrong then they change it to one that is correct? eg. Toronto
can be in Ontario or PEI.

TIA rasinc

All that is really needed is one table with City/Province.
For data entry, use cascading combos - That is, where the second combo is
filtered on the content of the first combo.

For example:

tblCityProvince
CityProvID - Autonumber pk
CityName - Text - Indexed - Allow Duplicates
ProvinceName - Text - Indexed - Allow Duplicates

Two combos for the form:
cboCity
row source = SELECT DISTINCT CityName FROM tblCityProvince;

After Update event:
Me.cboProvince.Requery

cboProvince
row source = SELECT ProvinceName FROM tblCityProvince WHERE CityName = '" &
Me.cboCity;

When the user selects a city from cboCity, cboProvince will present only
those Provinces that have the selected city name.
--
Dave Hargis, Microsoft Access MVP


"rasinc" wrote:

I've read a few posts that deal with address information but am not getting a
consistent view from the experts here so I thought I would ask the question.

I am creating a database that will contain address information. Many of the
cities and provinces will be the same. Should I have a separate tblCity and
tblProv tables with the tblCity linking to the tblProv table?

I am looking at ease of data entry also. I would like the users to be able
to easily select the information. Should they just select from a list and if
not in the list, add it. Ideally a combo box will include an city and
province combination in the file for use in any of three possible tables that
will include address information. Or should my users select the province and
this displays a list of cities (this was suggested by a user). Or should
they select a city and the province show up in the next combo? If the
province is wrong then they change it to one that is correct? eg. Toronto
can be in Ontario or PEI.

TIA rasinc