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
|
|||
|
|||
Connecting 2 tables, use Query or single field or double field joi
Dear All:
I have two tables I want to connect and here are the questions I have. 1. Should I connect using a the primary and foreign keys only. 2. Should I connect using the primary key and the "Skill" Field that appears in both tables. 2a. Do I need the "Skill" Field in both tables. 3. Should I have a new table for each Skill and put the abilities learned under that skill heading in the new tables. 4. Should I not create a relationship and use a query instead. 4a. What is the basis for deciding if I should use a query or relationship for connecting a look up table to a table? First table is Core Skills. Second Table Look up table for Skill Ability EG. Welding Weling Car Doors Closed The First Table (Core Skills) is used in a sub form to identify what skill types a person has used at a previous job. The Second Table is used to define what particular skills where practiced under the skill type. For the First Table( Core Skills) There are a finite number of skill practices that are important, about 5. In the Second Table ( Skill Ability) a total of 5 abilities under EACH skill class, that I am intrested in. The First Table "Core Skills" has the following Fields: PKCoreSkills FKWorkHistoryFacilityInfo Skill The Second Table "Skill Ability" has the following fileds: PKSkillAbility FKCoreSkills Skill AspectOfSkillAbility I will have populated all of the "AspectsOfSkillAbility" with all of the different aspects of all the different skills I am intrested in. So back to the questions: 1. Have I set up my tables incorrectly? 2. Do I need a connection or just a query. 3. Is my questions pointless, just do what you want? 4. Should I create a relationship, should I use just the primary keys or use the primary keys and the "Skill" Filed that appears in each table. 4a. (Which brings us back to question 1 in this list) Surly I do not need the same field "Skill" in two different tables? Referential integrity etc. Much thanks in advance for all your help. Audi TT Car of choice -- Anthony |
#2
|
|||
|
|||
Connecting 2 tables, use Query or single field or double field joi
Too many questions at one time, but let's go through a few.
1. Normally yes. There can be exceptions, but that is rare. 2. Normally not since you have PK and FKs relationships between the two tables. 2a. Most likely not. 3. Almost certainly not. All skills should be in a Skill table. 4. Ideally you will always create a relationship between appropriate tables with Referiential Integrity enables. 4a. Wrong question. The question should be "Do I use lookups at table level. The answer is a resounding NO. Below is a great list of reasons not to use lookups in tables. Http://www.mvps.org/access/lookupfields.htm Further check out the second commandment he http://www.mvps.org/access/tencommandments.htm Your best bet is to create relationships between your table in the Relationships window (hopefully with Referential Integrity enabled) and create lookups or subforms on your forms. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "BaBaBo" wrote: Dear All: I have two tables I want to connect and here are the questions I have. 1. Should I connect using a the primary and foreign keys only. 2. Should I connect using the primary key and the "Skill" Field that appears in both tables. 2a. Do I need the "Skill" Field in both tables. 3. Should I have a new table for each Skill and put the abilities learned under that skill heading in the new tables. 4. Should I not create a relationship and use a query instead. 4a. What is the basis for deciding if I should use a query or relationship for connecting a look up table to a table? First table is Core Skills. Second Table Look up table for Skill Ability EG. Welding Weling Car Doors Closed The First Table (Core Skills) is used in a sub form to identify what skill types a person has used at a previous job. The Second Table is used to define what particular skills where practiced under the skill type. For the First Table( Core Skills) There are a finite number of skill practices that are important, about 5. In the Second Table ( Skill Ability) a total of 5 abilities under EACH skill class, that I am intrested in. The First Table "Core Skills" has the following Fields: PKCoreSkills FKWorkHistoryFacilityInfo Skill The Second Table "Skill Ability" has the following fileds: PKSkillAbility FKCoreSkills Skill AspectOfSkillAbility I will have populated all of the "AspectsOfSkillAbility" with all of the different aspects of all the different skills I am intrested in. So back to the questions: 1. Have I set up my tables incorrectly? 2. Do I need a connection or just a query. 3. Is my questions pointless, just do what you want? 4. Should I create a relationship, should I use just the primary keys or use the primary keys and the "Skill" Filed that appears in each table. 4a. (Which brings us back to question 1 in this list) Surly I do not need the same field "Skill" in two different tables? Referential integrity etc. Much thanks in advance for all your help. Audi TT Car of choice -- Anthony |
#3
|
|||
|
|||
Connecting 2 tables, use Query or single field or double field
Dear Jerry:
Thanks for the response. Much appreciated. I asked two many questions and got important information but I missed out the crux of the problem. I have skills and those skills have sub specialites. In my form I want to be on the phone with the aplicant and say, So Bob you can do welding?, "Yes" Bob replies. So under my skills drop down list I choose welding. Then in my welding tab of my form I look down the drop down list and only see options for skill associated with welding. QUESTION: Assuming I have one table with all the skills, (eg body work, welding, tire roation)How do I not reenter welding 5 times in the skills field of the table, for every skill ability concerned with welding. (skill ability being the next field over I populate) I am doing this because: How will the query know to only populate the dropdown list with the skill ability for "Welding", as apposed to "Body Work" skill abilitys? I want this because: I do not want to make a mistake and get the skills abilities mixxed up and in the wrong catagory when recording the skills the person has, will make searching rather difficult. Other than retyping the skills 5 times (See example below), the only other way to do it I see is to create separate tables for each skills ability. So 1 table for Welding Skills, One table for Body Work Skills. Field: Skill Field:SkillAbility Welding Welding Car doors Shut Welding Welding Bumpers Back on. BodyWork Make the car look nice BodyWork Make the car look mean. I am very appreciated of the time you have give me, thank you. -- Anthony "Jerry Whittle" wrote: Too many questions at one time, but let's go through a few. 1. Normally yes. There can be exceptions, but that is rare. 2. Normally not since you have PK and FKs relationships between the two tables. 2a. Most likely not. 3. Almost certainly not. All skills should be in a Skill table. 4. Ideally you will always create a relationship between appropriate tables with Referiential Integrity enables. 4a. Wrong question. The question should be "Do I use lookups at table level. The answer is a resounding NO. Below is a great list of reasons not to use lookups in tables. Http://www.mvps.org/access/lookupfields.htm Further check out the second commandment he http://www.mvps.org/access/tencommandments.htm Your best bet is to create relationships between your table in the Relationships window (hopefully with Referential Integrity enabled) and create lookups or subforms on your forms. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "BaBaBo" wrote: Dear All: I have two tables I want to connect and here are the questions I have. 1. Should I connect using a the primary and foreign keys only. 2. Should I connect using the primary key and the "Skill" Field that appears in both tables. 2a. Do I need the "Skill" Field in both tables. 3. Should I have a new table for each Skill and put the abilities learned under that skill heading in the new tables. 4. Should I not create a relationship and use a query instead. 4a. What is the basis for deciding if I should use a query or relationship for connecting a look up table to a table? First table is Core Skills. Second Table Look up table for Skill Ability EG. Welding Weling Car Doors Closed The First Table (Core Skills) is used in a sub form to identify what skill types a person has used at a previous job. The Second Table is used to define what particular skills where practiced under the skill type. For the First Table( Core Skills) There are a finite number of skill practices that are important, about 5. In the Second Table ( Skill Ability) a total of 5 abilities under EACH skill class, that I am intrested in. The First Table "Core Skills" has the following Fields: PKCoreSkills FKWorkHistoryFacilityInfo Skill The Second Table "Skill Ability" has the following fileds: PKSkillAbility FKCoreSkills Skill AspectOfSkillAbility I will have populated all of the "AspectsOfSkillAbility" with all of the different aspects of all the different skills I am intrested in. So back to the questions: 1. Have I set up my tables incorrectly? 2. Do I need a connection or just a query. 3. Is my questions pointless, just do what you want? 4. Should I create a relationship, should I use just the primary keys or use the primary keys and the "Skill" Filed that appears in each table. 4a. (Which brings us back to question 1 in this list) Surly I do not need the same field "Skill" in two different tables? Referential integrity etc. Much thanks in advance for all your help. Audi TT Car of choice -- Anthony |
Thread Tools | |
Display Modes | |
|
|