J
JeffE
I'm using Access 2000 and I have three tables in a simple hierarchy.
Table: Countries
Description: A list of primary (sovereign) divisions
Fields:
ShortCountryName (primary key) – the common name of the division
LongCountryName (optional) – the formal name if different from the common name
Table: States
Description: A list of secondary divisions, either political, historic, or
geographic; states, republics, provinces, territories, departments, etc.
Fields:
ShortStateName (primary key) – the common name of the division. I will use
autonumber as the primary key if I come across two or more territories with
the same name. I probably should have done this with all three tables at the
start.
LongStateName (optional) – the formal name of the division
ShortCountryName (foreign key linked to Table: Countries)
Table: Counties
Description: A list of tertiary divisions, either political, historic, or
geographic; counties, idependent cities, regions, districts, prefectures, etc.
Fields:
CountyID (primary key) – a unique identifier (autonumber) as many county
names in the US alone are not unique
CountyName – the common name of the division
ShortStateName (foreign key linked to Table: States)
Do I need a ShortCountryName field in this table with a link to one or both
of the other tables?
I have Countries with States as a subdatasheet and Counties as a
sub-subdatasheet (a subdatasheet within States). I also have forms/subforms
for the purpose of data entry based on these datasheet/subdatasheets. So far
(and for obvious reasons), I have only entered Canada and its provinces and
territories, and the US, its states, territories, insular areas, and its 3140
counties, parishes, independent cities, boroughs, and census areas. The idea
is that other countries and territories will be entered later only when they
are needed.
I want a fourth table of Applicants with fields such as ID#, LastName,
FirstName, and then the various address/origin/location fields like Country,
State, County, etc.
QUESTION: Can I limit my choices in a State combo box based on the value
selected from a Country combo box and then limit my choices in a County combo
box based on the value selected from the State combo box? If so, how would I
do this? Example: There are two choices, United States and Canada, in the
Country combo box. I select United States, then the State combo box shows
only US states/territories (instead of all states/territories from all
countries). I select California from the State combo box, then the County
combo box shows only the 58 counties of California (instead of the thousands
of counties of all states of all countries).
Would it be better to do this on a Applicants form and have the form insert
the values into an underlying table? From some of the things I have read, I
get the idea that a form should DO the work and a table should SHOW the
result of the work.
Table: Countries
Description: A list of primary (sovereign) divisions
Fields:
ShortCountryName (primary key) – the common name of the division
LongCountryName (optional) – the formal name if different from the common name
Table: States
Description: A list of secondary divisions, either political, historic, or
geographic; states, republics, provinces, territories, departments, etc.
Fields:
ShortStateName (primary key) – the common name of the division. I will use
autonumber as the primary key if I come across two or more territories with
the same name. I probably should have done this with all three tables at the
start.
LongStateName (optional) – the formal name of the division
ShortCountryName (foreign key linked to Table: Countries)
Table: Counties
Description: A list of tertiary divisions, either political, historic, or
geographic; counties, idependent cities, regions, districts, prefectures, etc.
Fields:
CountyID (primary key) – a unique identifier (autonumber) as many county
names in the US alone are not unique
CountyName – the common name of the division
ShortStateName (foreign key linked to Table: States)
Do I need a ShortCountryName field in this table with a link to one or both
of the other tables?
I have Countries with States as a subdatasheet and Counties as a
sub-subdatasheet (a subdatasheet within States). I also have forms/subforms
for the purpose of data entry based on these datasheet/subdatasheets. So far
(and for obvious reasons), I have only entered Canada and its provinces and
territories, and the US, its states, territories, insular areas, and its 3140
counties, parishes, independent cities, boroughs, and census areas. The idea
is that other countries and territories will be entered later only when they
are needed.
I want a fourth table of Applicants with fields such as ID#, LastName,
FirstName, and then the various address/origin/location fields like Country,
State, County, etc.
QUESTION: Can I limit my choices in a State combo box based on the value
selected from a Country combo box and then limit my choices in a County combo
box based on the value selected from the State combo box? If so, how would I
do this? Example: There are two choices, United States and Canada, in the
Country combo box. I select United States, then the State combo box shows
only US states/territories (instead of all states/territories from all
countries). I select California from the State combo box, then the County
combo box shows only the 58 counties of California (instead of the thousands
of counties of all states of all countries).
Would it be better to do this on a Applicants form and have the form insert
the values into an underlying table? From some of the things I have read, I
get the idea that a form should DO the work and a table should SHOW the
result of the work.