Subform Link?

[turtle]

I am creating a database that tracks the Techinical Skills of our
engineers. There are about about 200 techinical skills that they will
need to fill out by giving themselves a ranking. Some fields they
will not fill out and others they will. Here is an example:

Employee # 1

Languages Skill Experience (yrs)
C++ 3 4
VB 2 2
Java
BASIC 5 10

Neworking Skill Experience
Bridges 1 1
Hubs
LAN 4 5

Here is my question...
I figured I would make a form with general info and a subform with the
different major categories (languages, networking, Databases). How do
I link the form/subform. If i link on employee name it will only show
fields that already have the employee name therefore the subform will
show no records.

[Allen Browne]

You have a many-to-many relationship between your employees and skills, i.e.
one employee may have many skills, and one skill may be possessed by many
different employees. To resolve that, you need another table that contains a
row for every skill every person has:

tblEmployee: one row for each employee
-----------------
EmployeeID AutoNumber primary key (p.k.)
Surname Text
...

tblSkillCategor y
---------------
SkillCatID AutoNumber p.k.
SkillCat Text Name of skill category

tblSkill: one row for each skill.
----------
SkillID AutoNumber
Skill Text Name of skill
SkillCatID Number (Long) f.k. to tblSkillCat.Ski llCatID

tblEmployeeSkil l: one row for every combination of employee and skill.
----------------------
EmployeeID Number (Long) f.k. to tblEmployee.Emp loyeeID
SkillID Number (Long) f.k. to tblSkill.SkillI D
BeginYear Number (Integer) Year person acquired this skill.

For the interface, you will have a main form bound to tblEmployee, with a
subform bound to tblEmployeeSkil l. The continuous subform will have a combo
box for selecting the skill. Select as many skills as apply to the employee,
one per row.

BTW, I've suggest a BeginYear rather than storing the number of years the
employee has had the skill. This way you can continue to calculate the right
number of years as time marches on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"turtle" <ko****@vistaco ntrols.com> wrote in message
news:58******** *************** ***@posting.goo gle.com...

I am creating a database that tracks the Techinical Skills of our
engineers. There are about about 200 techinical skills that they will
need to fill out by giving themselves a ranking. Some fields they
will not fill out and others they will. Here is an example:

Employee # 1

Languages Skill Experience (yrs)
C++ 3 4
VB 2 2
Java
BASIC 5 10

Neworking Skill Experience
Bridges 1 1
Hubs
LAN 4 5

Here is my question...
I figured I would make a form with general info and a subform with the
different major categories (languages, networking, Databases). How do
I link the form/subform. If i link on employee name it will only show
fields that already have the employee name therefore the subform will
show no records.

[turtle]

Allen,

Thank you so much for your detailed response. It is going to help out
a great deal. One question: For the tblEmployeeSkil l you said "one
row for every combination of employee and skill. Does this mean that
if I have 200 different skills that every employee needs to have a
record in the tblEmployeeSkil l with those 200 skills. Which would
mean that everytime a new employee starts i would need to add 200
skills under his name. Is that correct?

Once again... thank you so much for your help

Keith

[Bob Quintal]

ko****@vistacon trols.com (turtle) wrote in
news:58******** *************** **@posting.goog le.com:

Allen,

Thank you so much for your detailed response. It is going to help out
a great deal. One question: For the tblEmployeeSkil l you said "one
row for every combination of employee and skill. Does this mean that
if I have 200 different skills that every employee needs to have a
record in the tblEmployeeSkil l with those 200 skills. Which would
mean that everytime a new employee starts i would need to add 200
skills under his name. Is that correct?

Once again... thank you so much for your help

Keith

If every employee has 200 skills you have one great workforce. At my work
skills (and training) database that manages about 60 skills which require
certification and periodic training, spread over 600 employees.

Some employees have 4 or 5 skills, others 2 or three. Even if you have
200 skills you only need to add a record to the skills a new employee
acquires as the skill is acquired. When an employee or a group go through
a refresher, we add new entries. there are only about 5000 entries in the
table after 10 years, in our case.

If an emplyee doesn't have a skill no record gets added.
You can base queries on the presence or absence of an empID-skillID
record.

It's not a difficult task to create a form that brings up an employee's
record and a multiselect listbox to select which skills to add, and a
little code or even a macro to create those records.

Hope this helps.

Bob Q

[Allen Browne]

Yes, as Bob says, you only add the skills applicable to each employee, so if
you have 10 skills, you appear 10 times in the junction table.

You will use the Relationships window (Tools menu) to create a relationship
between this table and your Employee table, and another relationship to the
Skill table. When you create the relationships, check the box for
Referential Integrity. When you do that, Access creates (hidden) indexes on
these fields. Even if you had 1000 employees and each one had all 200
skills, Access will use these indexes and retrieve the values
instantaneously from the 200,000 records.

This is exactly how relational databases are designed to work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"turtle" <ko****@vistaco ntrols.com> wrote in message
news:58******** *************** **@posting.goog le.com...

Allen,

Thank you so much for your detailed response. It is going to help out
a great deal. One question: For the tblEmployeeSkil l you said "one
row for every combination of employee and skill. Does this mean that
if I have 200 different skills that every employee needs to have a
record in the tblEmployeeSkil l with those 200 skills. Which would
mean that everytime a new employee starts i would need to add 200
skills under his name. Is that correct?

Once again... thank you so much for your help

Keith

[turtle]

Bob and Allen... Thank you for your responses. I think I am beginning
to understand a little better (i'm a little slow). So, do I need to
create a multi select listbox with all the skills? Then when they
select them they go into the subform? Or is there a way where they
all show up in the subform but only if they fill out the information
it saved that record into the table.

Thanks for your help once again.

Keith

[Allen Browne]

The simplest way is to do away with the list box.
User selects from the combo (drop down list) in the subform.
After selecting one value on one row, another row appears, and they can move
to the next row and select another one, as many times as needed.
The combos (one per row) provide a visual list of the person's skills.

If you really wanted to provide a listbox on the main form, you could write
code that used its DblClick event to AddNew to the RecordsetClone of the
subform if the record is not already there, but personally I don't see the
point of going to that trouble.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"turtle" <ko****@vistaco ntrols.com> wrote in message
news:58******** *************** ***@posting.goo gle.com...

Bob and Allen... Thank you for your responses. I think I am beginning
to understand a little better (i'm a little slow). So, do I need to
create a multi select listbox with all the skills? Then when they
select them they go into the subform? Or is there a way where they
all show up in the subform but only if they fill out the information
it saved that record into the table.

Thanks for your help once again.

Keith

[turtle]

Allen,

I got the subform to work with the drop down box... The only problem
is, there still needs to be a record for every possible instance of
skill to employee in the the tblemplskill table. Therefore, every
employee needs to have every skill in the database in order for there
not to be an error when selecting that skill for that employee. What
am I doing wrong... I can't have it so everytime a new employee starts
i add 200 possible combinations.

Thanks for you time... it is well appreciated.

Keith

[Allen Browne]

Not sure I like that approach, but if you want to automatically create 200
related records every time the user enters a new employee, use the
AfterInsert event procedure of your form.

In that event, execute an append query statement. The statement will need
the EmployeeID of the newly entered employee. Something like this:

Private Sub Form_AfterInser t()
Dim strSql As String

strSQL = "INSERT INTO tblEmployeeSkil l ( SkillID, EmployeeID ) " & _
"SELECT tblSkill.SkillI D, " & Me.EmployeeID & " AS EmployeeID FROM
tblSkill;"

dbEngine(0)(0). Execute strSql, dbFailOnError

End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"turtle" <ko****@vistaco ntrols.com> wrote in message
news:58******** *************** ***@posting.goo gle.com...

I got the subform to work with the drop down box... The only problem
is, there still needs to be a record for every possible instance of
skill to employee in the the tblemplskill table. Therefore, every
employee needs to have every skill in the database in order for there
not to be an error when selecting that skill for that employee. What
am I doing wrong... I can't have it so everytime a new employee starts
i add 200 possible combinations.

[Bob Quintal]

ko****@vistacon trols.com (turtle) wrote in
news:58******** *************** ***@posting.goo gle.com:

Allen,

I got the subform to work with the drop down box... The only problem
is, there still needs to be a record for every possible instance of
skill to employee in the the tblemplskill table. Therefore, every
employee needs to have every skill in the database in order for there
not to be an error when selecting that skill for that employee. What
am I doing wrong... I can't have it so everytime a new employee starts
i add 200 possible combinations.

Thanks for you time... it is well appreciated.

Keith
You say "there still needs to be a record for every possible instance of skill to employee in the the tblemplskill table.". I ask why?

I see the form as follows.

Mainform with Employee name and details. Subform with Skils Possessed.

subform only shows skills that the employee has. To record a new skill
acquired by an employee, you just need to select the skill from the
combobox, then have code add the employee's ID and append both fileds to
a new record into the employee-skills table.
If you are doing something different, explain it so that we can figure
out how to best solve your problem.

Bob Q