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

tblSkillCategory
---------------
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.SkillCatID

tblEmployeeSkill: one row for every combination of employee and skill.
----------------------
EmployeeID Number (Long) f.k. to tblEmployee.EmployeeID
SkillID Number (Long) f.k. to tblSkill.SkillID
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 tblEmployeeSkill. 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****@vistacontrols.com> wrote in message
news:58**************************@posting.google.c om...

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 tblEmployeeSkill 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 tblEmployeeSkill 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****@vistacontrols.com (turtle) wrote in
news:58*************************@posting.google.co m:

Allen,

Thank you so much for your detailed response. It is going to help out
a great deal. One question: For the tblEmployeeSkill 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 tblEmployeeSkill 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****@vistacontrols.com> wrote in message
news:58*************************@posting.google.co m...

Allen,

Thank you so much for your detailed response. It is going to help out
a great deal. One question: For the tblEmployeeSkill 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 tblEmployeeSkill 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****@vistacontrols.com> wrote in message
news:58**************************@posting.google.c om...

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_AfterInsert()
Dim strSql As String

strSQL = "INSERT INTO tblEmployeeSkill ( SkillID, EmployeeID ) " & _
"SELECT tblSkill.SkillID, " & 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****@vistacontrols.com> wrote in message
news:58**************************@posting.google.c om...

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****@vistacontrols.com (turtle) wrote in
news:58**************************@posting.google.c om:

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

[turtle]

Allen,

I really don't like that approach either. Is there any other way. I
believe in one of your first post you said I would need a record for
each skill for each employee... if this is true, how do I do that
without creating a record for each skill everytime a new employee
starts?
thanks
Keith

[Allen Browne]

Rather than create a record for every possible combination of employee and
skill, what I originally suggested was that the link table would contain a
record for each actual combinationof employee and skill. That is, if an
employee has 12 skills, they appear in 12 rows of the link table. It would
be a very unusual workplace if all employees had all 200 skills.

To create the records for the 12 skills is as easy as picking a skill on 12
rows of the subform. There is no way the program can do this
programmatically, since it cannot know which skills an employee has.

If appropriate, you could add a Yes/No field to the Skills table to indicate
if this skill is considered to be a default. You could then use the
Form_AfterInsert event to only add the skills " ... WHERE (IsDefault = True)
....".

--
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****@vistacontrols.com> wrote in message
news:58**************************@posting.google.c om...

Allen,

I really don't like that approach either. Is there any other way. I
believe in one of your first post you said I would need a record for
each skill for each employee... if this is true, how do I do that
without creating a record for each skill everytime a new employee
starts?
thanks
Keith

[turtle]

thanks again guys...
I think I am going to go ahead and just add all the skills for all the
employees in the tblemployeeskill table. Basically my boss likes how
it looks with all the skills showing up for each employee in the
subform... I have it set that if he double clicks the subform skill it
will run a report showing anyone else with that skill. Thank you for
taking the time to answer my questions. I think I only have one more
but I can probably figure it out by using Allen's append query code.
If I add a new skill I will need to make sure that skill gets
incorporated for every employee. I think i will just need to write
another append query.
Once again, thank you guys so much for your time.

Keith