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

 

[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.

 

[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.