Staff Skills Database

[hobbit2612 via AccessMonster.com]

Hi,

I wonder is someone can help me please. I did post a similar thread a week or
so ago but I didn't do too well on receiving replies, perhaps because I
didn't explain it well enough so I'm trying again.

What I'm trying to do is to create a database that shows a member of staffs
level of skill against a predefined list of skills.

What I would like to have is a main form with staff details, a continuous
subform linked by StaffID showing the static list of skills, then to have a
drop down box showng the levels aganinst each skill, which can be selected by
the administrator. From here I can then create the relevant reports.

I have tried so many ways of trying to get this to work, multiple queries,
left joins, right joins, but I can't get the three sets of information to
come together correctly.

Can anyone offer any advice please?

Many thanks and regards

Chris

 

[Steve]

Hi Chris,

How about the following tables ......

TblStaff
StaffID
FirstName
LastName
<Other Staff Details>

TblSkill
SkillID
Skill

TblSkillLevel
SkillLevelID
SkillLevel (Assumes something like Novice, Intermediate, Expert)

TblStaffSkillLevel
StaffSkillLevelID
StaffID
SkillID
SkillLevelID


Steve
(e-mail address removed)

 

[hobbit2612 via AccessMonster.com]

Hi Steve,

Thanks very much for getting back to me.

I'm a bit of a newbie so please bear with me.

I've created the tables and have made unique keys for:

StaffID out of TblStaff
SkillID out of TblSkill
SkillLevelID out of tableTblSkillLevel

I have linked these three fields to the relevant fields in TblStaffSkillLevel
with One to Many relationships, I think this is right?

I have designed my main form using the TblStaff, but I'm not quite sure how
to create my subform. Should I use the TblStaffSkillLevel as the data source
for this?

Many thanks for your time

Regards

Chris

Hi Chris,

How about the following tables ......

TblStaff
StaffID
FirstName
LastName
<Other Staff Details>

TblSkill
SkillID
Skill

TblSkillLevel
SkillLevelID
SkillLevel (Assumes something like Novice, Intermediate, Expert)

TblStaffSkillLevel
StaffSkillLevelID
StaffID
SkillID
SkillLevelID

Steve
(e-mail address removed)

Hi,

[quoted text clipped - 23 lines]

Chris

 

[Fred]

What you are describing (and the structure that Steve described) is the best
way to do it. Even if it doesn't precisely do what you originally asked
for, which would have required a an awkward join directly from staff to
skills.

 

[Steve]

Create a form named SFrmStaffSkillLevel based on TblStaffSkillLevel. Use a
combobox or listbox for the fields SkillID and SkillLevelID. For SkillID use
a query based on TblSkill and sort ascending on Skill for the rowsource.
Open properties and set Bound Column to 1, Column Count to 2 and Column
Width to 0,2. For SkillLevelID use a query based on TblSkillLevel and sort
from lowest to highest on SkillLevel for the rowsource. Open properties and
set Bound Column to 1, Column Count to 2 and Column Width to 0,2. Open your
main form in design view. Click on Window in the menu at the top of
thescreen and tile vertically. Click and drag SFrmStaffSkillLevel and drop
it on your form. Close. Reopen your form in design view, select the subform
control and open properties. On the data tab, set the LinlMaster and
LinkChild properties to Staffid.

Steve
(e-mail address removed)

Hi Steve,

Thanks very much for getting back to me.

I'm a bit of a newbie so please bear with me.

I've created the tables and have made unique keys for:

StaffID out of TblStaff
SkillID out of TblSkill
SkillLevelID out of tableTblSkillLevel

I have linked these three fields to the relevant fields in
TblStaffSkillLevel
with One to Many relationships, I think this is right?

I have designed my main form using the TblStaff, but I'm not quite sure
how
to create my subform. Should I use the TblStaffSkillLevel as the data
source
for this?

Many thanks for your time

Regards

Chris

Hi Chris,

How about the following tables ......

TblStaff
StaffID
FirstName
LastName
<Other Staff Details>

TblSkill
SkillID
Skill

TblSkillLevel
SkillLevelID
SkillLevel (Assumes something like Novice, Intermediate, Expert)

TblStaffSkillLevel
StaffSkillLevelID
StaffID
SkillID
SkillLevelID

Steve
(e-mail address removed)

Hi,

[quoted text clipped - 23 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Steve

Many thanks for all your hard work on this it really is appreciated.

I went for the combo box option and it works a treat! I must admit however I
am still tinkering around just to see if I can enhance it further if it's
possible, and get the list into a static text box. I guess I don't like to be
beaten and I like the challenge

Once again many thanks and regards

Chris

Create a form named SFrmStaffSkillLevel based on TblStaffSkillLevel. Use a
combobox or listbox for the fields SkillID and SkillLevelID. For SkillID use
a query based on TblSkill and sort ascending on Skill for the rowsource.
Open properties and set Bound Column to 1, Column Count to 2 and Column
Width to 0,2. For SkillLevelID use a query based on TblSkillLevel and sort
from lowest to highest on SkillLevel for the rowsource. Open properties and
set Bound Column to 1, Column Count to 2 and Column Width to 0,2. Open your
main form in design view. Click on Window in the menu at the top of
thescreen and tile vertically. Click and drag SFrmStaffSkillLevel and drop
it on your form. Close. Reopen your form in design view, select the subform
control and open properties. On the data tab, set the LinlMaster and
LinkChild properties to Staffid.

Steve
(e-mail address removed)

Hi Steve,

[quoted text clipped - 56 lines]

 

[Steve]

Hi Chris,

Glad to help!

Here's something more toward your end goal ...

For SkillLevelID in your subform use an option group. You can use an option
button to represent each SkillLevelID and then all you need do is check the
correct button for each record.

Steve

Steve

Many thanks for all your hard work on this it really is appreciated.

I went for the combo box option and it works a treat! I must admit however
I
am still tinkering around just to see if I can enhance it further if it's
possible, and get the list into a static text box. I guess I don't like to
be
beaten and I like the challenge

Once again many thanks and regards

Chris

Create a form named SFrmStaffSkillLevel based on TblStaffSkillLevel. Use a
combobox or listbox for the fields SkillID and SkillLevelID. For SkillID
use
a query based on TblSkill and sort ascending on Skill for the rowsource.
Open properties and set Bound Column to 1, Column Count to 2 and Column
Width to 0,2. For SkillLevelID use a query based on TblSkillLevel and sort
from lowest to highest on SkillLevel for the rowsource. Open properties
and
set Bound Column to 1, Column Count to 2 and Column Width to 0,2. Open
your
main form in design view. Click on Window in the menu at the top of
thescreen and tile vertically. Click and drag SFrmStaffSkillLevel and drop
it on your form. Close. Reopen your form in design view, select the
subform
control and open properties. On the data tab, set the LinlMaster and
LinkChild properties to Staffid.

Steve
(e-mail address removed)

Hi Steve,

[quoted text clipped - 56 lines]

Chris