473,372 Members | 1,062 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Subform Link?

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.
Nov 12 '05 #1
12 3471
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.

Nov 12 '05 #2
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
Nov 12 '05 #3
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
Nov 12 '05 #4
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

Nov 12 '05 #5
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
Nov 12 '05 #6
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

Nov 12 '05 #7
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
Nov 12 '05 #8
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.

Nov 12 '05 #9
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


Nov 12 '05 #10
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
Nov 12 '05 #11
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

Nov 12 '05 #12
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
Nov 12 '05 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: chrisM | last post by:
Hi, Hope someone can help me. I have the following setup in an Access database A master form 'Customers' in 'Single Form' view that has a RecordSource of 'myCustomerTable' CustomerCode...
2
by: Scott | last post by:
In my database, I have a form with a subform. I had everything working correctly - the correct records would display on the subform for each record on the form. I could edit, add, and delete on the...
6
by: Wendy Powley | last post by:
I have a subform which represents a 1:N relationship with the main form. I would like to be able to read values from an external file, fill the subform with the values read & allow the user to...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
1
by: kkrizl | last post by:
I have a form that displays general information about an alarm permit location. There's a subform that shows detailed information about burglar alarms that have gone off at the location. When a...
1
by: MP | last post by:
I have a main form that has a subform which also has a subform: the main form is the first subform is the second subform is When I click on the button »AddNewSubSubRecord« (add a new record...
9
by: robert d via AccessMonster.com | last post by:
I'm not sure why the following isn't working. The subform loads correctly, but no data is displayed. I'm certain that there is data and I have checked that the SQL statement does in fact return...
9
by: Ecohouse | last post by:
I have a main form with two subforms. The first subform has the child link to the main form identity key. subform1 - Master Field: SK Child Field: TrainingMasterSK The second subform has a...
7
by: wwwords | last post by:
I have a form in Access 2007 containing a subform. The control source of the latter is SELECT DISTINCTROW ., ., ., ., . FROM ; which is undoubtedly correct. However, if I click on the down...
7
by: TinyTom | last post by:
I really need help with this code. I have made a pretty complicated subform that automatically updates filter criteria based off of inputs in various fields within the search form. What I need to...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.