If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linking tables
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. |
#2
|
|||
|
|||
Linking tables
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. THINKINGWAY wrote: 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. -- Bob Heifler My Access Program, Owner www.myaccessprogram.com info (At) MyAccessProgram (Dot) com Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Linking tables
On Fri, 9 Nov 2007 10:18:00 -0800, THINKINGWAY
wrote: 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] |
#4
|
|||
|
|||
Linking tables
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. "heifler via AccessMonster.com" wrote: 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. THINKINGWAY wrote: 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. -- Bob Heifler My Access Program, Owner www.myaccessprogram.com info (At) MyAccessProgram (Dot) com Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Linking tables
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. "THINKINGWAY" wrote: 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. "heifler via AccessMonster.com" wrote: 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. THINKINGWAY wrote: 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. -- Bob Heifler My Access Program, Owner www.myaccessprogram.com info (At) MyAccessProgram (Dot) com Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|