Two related select querys in series. How to design tables?

[BaBaBo]

Thanks @ advance.

Got two comboboxes in series.
Firs combobox defines skill area e.g. Running.
Second combobx uses the value selected in the first combobox to narrow the
list of choices in the second combobox.

Question: What is the most economical way of setting up the tables?

I am currently repeating the primary key from the "Skill Area" table as a
Foreign Key in the "Specific Skill Area Ability" table. This repeat of the
info alows me to use a select query to fill the second combobox with only the
skills specific to the skill area. e.g.

100 Meter Hurdle is a specific skill under the "Skill Area" of Running.

Should I only have one table with the following rows.

PrimaryKey.
Skill
SkillAbility

But if I do this I will be repeating the Skill for each Skill Ability. This
is counter to the point of Relational Data Bases?

 

[Armen Stein]

Thanks @ advance.

Got two comboboxes in series.
Firs combobox defines skill area e.g. Running.
Second combobx uses the value selected in the first combobox to narrow the
list of choices in the second combobox.

Question: What is the most economical way of setting up the tables?

I am currently repeating the primary key from the "Skill Area" table as a
Foreign Key in the "Specific Skill Area Ability" table. This repeat of the
info alows me to use a select query to fill the second combobox with only the
skills specific to the skill area. e.g.

100 Meter Hurdle is a specific skill under the "Skill Area" of Running.

Should I only have one table with the following rows.

PrimaryKey.
Skill
SkillAbility

But if I do this I will be repeating the Skill for each Skill Ability. This
is counter to the point of Relational Data Bases?

Hi Anthony,

Your two-table structure is the correct way. You wouldn't want to
repeat all the Skill Area information (even the name) any more than
necessary - just its primary key in the Specific Ability will suffice.
Ideally your Skill Area primary key is something short and static,
like an AutoNumber.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Steve]

NEVER design your tables around how you want to display the data. Tables
should be designed around the data; ie, normalized.

TblSkill
SkillID
Skill

TblSkillArea
SkillAreaID
SkillID
SkillArea

From the above you can create a form/subform for data entry. The main form
will be based on TblSkill and the subform will be based on TblSkillArea. The
LinkMaster and LinkChild properties will be SkillID. You can then ...
1. Enter a new skill in the main form
2. Add skill areas to a new skill in the subform
3. Add skill areas to an existing skill in the subform
4. Edit/Delete skill areas in the subform
5. Delete a skill and all associated skill areas in the main form

Steve
(e-mail address removed)

 

[BaBaBo]

Dear Steve and Armen:

Thanks for your help, both usefull but I am getting this a bit ass about tit
as we say it over in the UK.

This is not to be used to enter info but used to populate dropdown lists. I
will have already populated the fields with the information about the skills,
and the skill ability. My question is concerned with setting up a look up
table in the most efficent form.
Can be that your answer still stands. But surely it is easier to create 1
table with 3 fields:

Primary Key
Skill ID
Skill Name
SKill Ability ID
Skill Ability

Ok I am did not go back and change what I had written so you could see my
learning process. I will get right to it. I stated earlier I only needed 3
fields and I see now I need 5.

This is a very simple problem when you look at it from entering the info. I
was looking at it as a fill the combo boxes on the form using the info in the
table to then populate the drop down lists. e.g.

I have someone who wants to join the track team and for the first combobox I
select the skill "Running". From the second combobox I am given the options
that are only concerned with running, 100m, 400m, 1 mile etc.

Thanks all so much.
Thanks all so much for your help.

 

[Steve]

"Running" and such goes in TblSkill. 100m, 400m, 1 mile etc. go in
TblSkillArea. Your data entry form for these two tables is as previously
described. TblTrackTeam looks like:
TblTrackTeam
TrackTeamID
PersonID (from TblPerson or could be StudentID from TblStudent)
SkillID
SkillAreaID
DateJoinedTrackTeam

SkillID and SkillAreaID are both entered in comboboxes. As you said, the
SkillID combobox will be designed to limit the choices in the SkillAreaID
combobox. This is called "cascading comboboxes". If you don't know how to
set them up, refer to mvps.org/access.

Steve
(e-mail address removed)

 

[BaBaBo]

Dear Steve:

Well you helped me out no end. This is fantastic. I looked up Cascading
Lists and found exactly the information. Funny the guy is postulating exactly
what I was questioning the fastest way to build the tables. There is more
than one way.

Give a man an answere and you fix the problem, give a man a tearm to search
and he learns the answer.

Again this is the best answer I HAVE ever got.

I got the info on how to build the cascading list boxes from:

http://www.fontstuff.com/access/acctut10.htm

Man this weeked is going to be fun.

 

[Steve]

Hi Anthony,

I'm always glad to help someone out!

Thank you for graciously showing your appreciation. If you need further
help, post back here; I will be watching.

Good luck!

Steve