Linking tables

[Guest]

I have a PROJECT table that has a field called PROJECT_CITY. I want to
create additional tables as follows: CITY, STATEPROVINCE, REGION, DIVISION
and COUNTRY. I don't want to have all these fields in the PROJECT table for
each project. Questions: 1) Is this the correct approach and 2) I am not
sure how to link these tables so that I can determine from the PROJECT_CITY
field, via queries, what COUNTRY (or any other lower hiearchical item) the
CITY is located within.

Thank you,
Your assistance is appreciated.

 

[heifler via AccessMonster.com]

These additional items like Project_City are just more data related to a
project and would just be additional fields in that table.
See my signature for more help.

 

[John W. Vinson]

I have a PROJECT table that has a field called PROJECT_CITY. I want to
create additional tables as follows: CITY, STATEPROVINCE, REGION, DIVISION
and COUNTRY. I don't want to have all these fields in the PROJECT table for
each project. Questions: 1) Is this the correct approach and 2) I am not
sure how to link these tables so that I can determine from the PROJECT_CITY
field, via queries, what COUNTRY (or any other lower hiearchical item) the
CITY is located within.

Be careful. City names are not unique!

There is a city named Paris in France. There's another in Texas in the US.
Every state in the United States, I've read, has a town named Springfield.
I live in Parma, Idaho. Parma, Ohio and Parma, Italy are much bigger cities.
Even within a state you're not home free - there are two towns named Los
Alamos in New Mexico!

You'll need either a unique CityID linked to the primary key of a table with
the fields you mention; or include STATEPROVINCE and COUNTRY in your projects
table.

John W. Vinson [MVP]

 

[Guest]

Ooops, I apologize. What I asked was not all that clear. Here is what I
think I should do as far as linking the tables.

tblPROJECT with field fk CITY_ID to tblCITY

tblCITY with field pk CITY_ID
tblCITY with field fk STATEPROV_D

tblSTATE with field pk STATEPROV_ID
tblSTATE with field fk REGION_ID

tblREGION with field pk REGION_ID
tblREGION with filed fk DIVISION_ID

tblDIVISION with field pk DIVISION_ID
tblDIVISION with field fk COUNTRY_ID

tblCOUNTRY with field pk COUNTRY_ID

Of course there will be a field in each table called <blank>_NAME where
<blank> is a placeholder for CITY, STATE, DIVISION, etc. The idea is that I
have only one location field in project table. I believe this gives me more
query flexibility and speed. Please correct me if my assumptions are
incorrect.

 

[Guest]

Ooops, I apologize. What I asked was not all that clear. Here is what I
think I should do as far as linking the tables.

tblPROJECT with field fk CITY_ID to tblCITY

tblCITY with field pk CITY_ID
tblCITY with field fk STATEPROV_D

tblSTATE with field pk STATEPROV_ID
tblSTATE with field fk REGION_ID

tblREGION with field pk REGION_ID
tblREGION with filed fk DIVISION_ID

tblDIVISION with field pk DIVISION_ID
tblDIVISION with field fk COUNTRY_ID

tblCOUNTRY with field pk COUNTRY_ID

Of course there will be a field in each table called <blank>_NAME where
<blank> is a placeholder for CITY, STATE, DIVISION, etc. The idea is that I
have only one location field in project table. I believe this gives me more
query flexibility and speed. Please correct me if my assumptions are
incorrect.