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

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

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