Multiple Options Group

I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?

Employee table needs EmpID and EmpName
Skills table needs SkillID and SkillDescription
Employee SkillsTable needs EmpID and SkillID
Create a one to many relation between Employee table and Employee Skills
table on EmpID
Create a one to many relation between Employee Skills table and Skills table
on SkillID

"Patty Stoddard" wrote:

I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?

I completed the tables as you indicated. Now the junction table only has two
fields. What is the data type for each of these fields? Number? Do I need
a root key for the junction table?

"Klatuu" wrote:

Employee table needs EmpID and EmpName
Skills table needs SkillID and SkillDescription
Employee SkillsTable needs EmpID and SkillID
Create a one to many relation between Employee table and Employee Skills
table on EmpID
Create a one to many relation between Employee Skills table and Skills table
on SkillID

"Patty Stoddard" wrote:

I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?

I would think all your ID fields would be autonumber.
The Junction table (Employee Skills Table) does not need anthing else. You
can make its primary key the combination of the two fields.
This table is only used to group the skills the employee has and keep track
of information about that skill for the employee. For example, you may will
have a Skill Level. It would go in the Employee Skills Table.
So, when you want to look at the skills an employee has, you would navigate
to that employee's record. Your Employee Skills table would then appear to
be filtered on that employee. The description for the skill would be in the
Skills table.

"Patty Stoddard" wrote:

I completed the tables as you indicated. Now the junction table only has two
fields. What is the data type for each of these fields? Number? Do I need
a root key for the junction table?

"Klatuu" wrote:

Employee table needs EmpID and EmpName
Skills table needs SkillID and SkillDescription
Employee SkillsTable needs EmpID and SkillID
Create a one to many relation between Employee table and Employee Skills
table on EmpID
Create a one to many relation between Employee Skills table and Skills table
on SkillID

"Patty Stoddard" wrote:

I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?

How do I make the skill description show in the subform in the combo box?

"Klatuu" wrote:

I would think all your ID fields would be autonumber.
The Junction table (Employee Skills Table) does not need anthing else. You
can make its primary key the combination of the two fields.
This table is only used to group the skills the employee has and keep track
of information about that skill for the employee. For example, you may will
have a Skill Level. It would go in the Employee Skills Table.
So, when you want to look at the skills an employee has, you would navigate
to that employee's record. Your Employee Skills table would then appear to
be filtered on that employee. The description for the skill would be in the
Skills table.

"Patty Stoddard" wrote:

I completed the tables as you indicated. Now the junction table only has two
fields. What is the data type for each of these fields? Number? Do I need
a root key for the junction table?

"Klatuu" wrote:

Employee table needs EmpID and EmpName
Skills table needs SkillID and SkillDescription
Employee SkillsTable needs EmpID and SkillID
Create a one to many relation between Employee table and Employee Skills
table on EmpID
Create a one to many relation between Employee Skills table and Skills table
on SkillID

"Patty Stoddard" wrote:

I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?

Depends on what the record source for the sub form is. It should probably be
a query that has both the Employee Skill table and the Skill table included.
Then make the control source for the skill description whatever field has the
description. If you are considering putting the description in the Employee
Skill table, DON'T! That would violate one of the basic rules of database
normalization.

"Patty Stoddard" wrote:

How do I make the skill description show in the subform in the combo box?

"Klatuu" wrote:

I would think all your ID fields would be autonumber.
The Junction table (Employee Skills Table) does not need anthing else. You
can make its primary key the combination of the two fields.
This table is only used to group the skills the employee has and keep track
of information about that skill for the employee. For example, you may will
have a Skill Level. It would go in the Employee Skills Table.
So, when you want to look at the skills an employee has, you would navigate
to that employee's record. Your Employee Skills table would then appear to
be filtered on that employee. The description for the skill would be in the
Skills table.

"Patty Stoddard" wrote:

I completed the tables as you indicated. Now the junction table only has two
fields. What is the data type for each of these fields? Number? Do I need
a root key for the junction table?

"Klatuu" wrote:

Employee table needs EmpID and EmpName
Skills table needs SkillID and SkillDescription
Employee SkillsTable needs EmpID and SkillID
Create a one to many relation between Employee table and Employee Skills
table on EmpID
Create a one to many relation between Employee Skills table and Skills table
on SkillID

"Patty Stoddard" wrote:

I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?

I have two fields in the skill table. SkillID and skill. SkillID is an
autonumber and skill is a text field describing the skill i.e. clerical,
accounting, housekeeping, grounds worker, etc.

I want to create records for the junction table by selecting from a list in
a form. I have created a subform to accomplish this. The problem is that
the drop down box shows me the skill ID to choose from instead of the field
with the skill description.

"Klatuu" wrote:

Depends on what the record source for the sub form is. It should probably be
a query that has both the Employee Skill table and the Skill table included.
Then make the control source for the skill description whatever field has the
description. If you are considering putting the description in the Employee
Skill table, DON'T! That would violate one of the basic rules of database
normalization.

"Patty Stoddard" wrote:

How do I make the skill description show in the subform in the combo box?

"Klatuu" wrote:

I would think all your ID fields would be autonumber.
The Junction table (Employee Skills Table) does not need anthing else. You
can make its primary key the combination of the two fields.
This table is only used to group the skills the employee has and keep track
of information about that skill for the employee. For example, you may will
have a Skill Level. It would go in the Employee Skills Table.
So, when you want to look at the skills an employee has, you would navigate
to that employee's record. Your Employee Skills table would then appear to
be filtered on that employee. The description for the skill would be in the
Skills table.

"Patty Stoddard" wrote:

I completed the tables as you indicated. Now the junction table only has two
fields. What is the data type for each of these fields? Number? Do I need
a root key for the junction table?

"Klatuu" wrote:

Employee table needs EmpID and EmpName
Skills table needs SkillID and SkillDescription
Employee SkillsTable needs EmpID and SkillID
Create a one to many relation between Employee table and Employee Skills
table on EmpID
Create a one to many relation between Employee Skills table and Skills table
on SkillID

"Patty Stoddard" wrote:

I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?

No Problem. You can have multiple columns for a combo box. The bound column
should be the SkillID because that is what you will use to retrieve it from
the database. The column with skill can be the one the user sees. Let's
assume you make column 0 the SkillID and column 1 the skill. Set the column
width for column 0 to 0, and for column2 set it wide enough to see the entire
description. So now, if you reference Me.cboSkill, it will return the value
of the SkillID for the selected row of the combo box. You will get the same
results with Me.cboSkill.Column(0). Me.cboSkill.Column(1) will return the
description.

(you are working hard at this, aren't you?)

"Patty Stoddard" wrote:

I have two fields in the skill table. SkillID and skill. SkillID is an
autonumber and skill is a text field describing the skill i.e. clerical,
accounting, housekeeping, grounds worker, etc.

I want to create records for the junction table by selecting from a list in
a form. I have created a subform to accomplish this. The problem is that
the drop down box shows me the skill ID to choose from instead of the field
with the skill description.

"Klatuu" wrote:

Depends on what the record source for the sub form is. It should probably be
a query that has both the Employee Skill table and the Skill table included.
Then make the control source for the skill description whatever field has the
description. If you are considering putting the description in the Employee
Skill table, DON'T! That would violate one of the basic rules of database
normalization.

"Patty Stoddard" wrote:

How do I make the skill description show in the subform in the combo box?

"Klatuu" wrote:

I would think all your ID fields would be autonumber.
The Junction table (Employee Skills Table) does not need anthing else. You
can make its primary key the combination of the two fields.
This table is only used to group the skills the employee has and keep track
of information about that skill for the employee. For example, you may will
have a Skill Level. It would go in the Employee Skills Table.
So, when you want to look at the skills an employee has, you would navigate
to that employee's record. Your Employee Skills table would then appear to
be filtered on that employee. The description for the skill would be in the
Skills table.

"Patty Stoddard" wrote:

I completed the tables as you indicated. Now the junction table only has two
fields. What is the data type for each of these fields? Number? Do I need
a root key for the junction table?

"Klatuu" wrote:

Employee table needs EmpID and EmpName
Skills table needs SkillID and SkillDescription
Employee SkillsTable needs EmpID and SkillID
Create a one to many relation between Employee table and Employee Skills
table on EmpID
Create a one to many relation between Employee Skills table and Skills table
on SkillID

"Patty Stoddard" wrote:

I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?

I think I need to go back to the drawing board. I created a form from data
in the Employee Applicant Database. I then attempted to create a subform to
lookup the job skills for each employee and choose from a drop down box all
that apply. This I expected would update the Junction Table with records
relating the ApplicantID with one or more skillIDs.

The subform uses fields from the junction table. Is this right? I had to
add a text field named "job skill" to this table to show the related skill in
the skills table. I don't know how to define this field so it knows where to
get the proper text though.

Maybe I should use the skills table. But then how do I define which fields
link the main form to the subform?

I just thourghly confused.....but I really appreciate your patience with me.

"Klatuu" wrote:

No Problem. You can have multiple columns for a combo box. The bound column
should be the SkillID because that is what you will use to retrieve it from
the database. The column with skill can be the one the user sees. Let's
assume you make column 0 the SkillID and column 1 the skill. Set the column
width for column 0 to 0, and for column2 set it wide enough to see the entire
description. So now, if you reference Me.cboSkill, it will return the value
of the SkillID for the selected row of the combo box. You will get the same
results with Me.cboSkill.Column(0). Me.cboSkill.Column(1) will return the
description.

(you are working hard at this, aren't you?)

"Patty Stoddard" wrote:

I have two fields in the skill table. SkillID and skill. SkillID is an
autonumber and skill is a text field describing the skill i.e. clerical,
accounting, housekeeping, grounds worker, etc.

I want to create records for the junction table by selecting from a list in
a form. I have created a subform to accomplish this. The problem is that
the drop down box shows me the skill ID to choose from instead of the field
with the skill description.

"Klatuu" wrote:

Depends on what the record source for the sub form is. It should probably be
a query that has both the Employee Skill table and the Skill table included.
Then make the control source for the skill description whatever field has the
description. If you are considering putting the description in the Employee
Skill table, DON'T! That would violate one of the basic rules of database
normalization.

"Patty Stoddard" wrote:

How do I make the skill description show in the subform in the combo box?

"Klatuu" wrote:

I would think all your ID fields would be autonumber.
The Junction table (Employee Skills Table) does not need anthing else. You
can make its primary key the combination of the two fields.
This table is only used to group the skills the employee has and keep track
of information about that skill for the employee. For example, you may will
have a Skill Level. It would go in the Employee Skills Table.
So, when you want to look at the skills an employee has, you would navigate
to that employee's record. Your Employee Skills table would then appear to
be filtered on that employee. The description for the skill would be in the
Skills table.

"Patty Stoddard" wrote:

I completed the tables as you indicated. Now the junction table only has two
fields. What is the data type for each of these fields? Number? Do I need
a root key for the junction table?

"Klatuu" wrote:

Employee table needs EmpID and EmpName
Skills table needs SkillID and SkillDescription
Employee SkillsTable needs EmpID and SkillID
Create a one to many relation between Employee table and Employee Skills
table on EmpID
Create a one to many relation between Employee Skills table and Skills table
on SkillID

"Patty Stoddard" wrote:

I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?

No, remeber what I said about putting the description in the Employee Skills
table. It should be in the Skills table.
Your main form should be based on the Employee table.
Your subform should be based on the Employees Skills table.
For your subform, create a query that would be based on the Skill table and
the Employee Skill table. In that query you would need:
Skill![EmpID]
Skill![SkillID]
Skill![SkillDescription]
EmpSkill![EmpID]
and whatever other fields you have in the Employee Skill table.
Also on the subform your combo box would be unbound and visible only when
you want to add a new skill for the employee. The row source should be the
Skill table. It would have the SkillID and SkillDescription as I described
earlier. Then when you select the skill, in the After Update event of the
combo box, put the SkillID value in the bound text box for the SkillID. You
will get the value for Skill![EmpID] from the main form.

I know this all seems confusing now, but follow it through and it will make
sense once it is all done. This will be a valuable lesson because this is a
very common situation you will run into a lot.

"Patty Stoddard" wrote:

I think I need to go back to the drawing board. I created a form from data
in the Employee Applicant Database. I then attempted to create a subform to
lookup the job skills for each employee and choose from a drop down box all
that apply. This I expected would update the Junction Table with records
relating the ApplicantID with one or more skillIDs.

The subform uses fields from the junction table. Is this right? I had to
add a text field named "job skill" to this table to show the related skill in
the skills table. I don't know how to define this field so it knows where to
get the proper text though.

Maybe I should use the skills table. But then how do I define which fields
link the main form to the subform?

I just thourghly confused.....but I really appreciate your patience with me.

"Klatuu" wrote:

No Problem. You can have multiple columns for a combo box. The bound column
should be the SkillID because that is what you will use to retrieve it from
the database. The column with skill can be the one the user sees. Let's
assume you make column 0 the SkillID and column 1 the skill. Set the column
width for column 0 to 0, and for column2 set it wide enough to see the entire
description. So now, if you reference Me.cboSkill, it will return the value
of the SkillID for the selected row of the combo box. You will get the same
results with Me.cboSkill.Column(0). Me.cboSkill.Column(1) will return the
description.

(you are working hard at this, aren't you?)

"Patty Stoddard" wrote:

I have two fields in the skill table. SkillID and skill. SkillID is an
autonumber and skill is a text field describing the skill i.e. clerical,
accounting, housekeeping, grounds worker, etc.

I want to create records for the junction table by selecting from a list in
a form. I have created a subform to accomplish this. The problem is that
the drop down box shows me the skill ID to choose from instead of the field
with the skill description.

"Klatuu" wrote:

Depends on what the record source for the sub form is. It should probably be
a query that has both the Employee Skill table and the Skill table included.
Then make the control source for the skill description whatever field has the
description. If you are considering putting the description in the Employee
Skill table, DON'T! That would violate one of the basic rules of database
normalization.

"Patty Stoddard" wrote:

How do I make the skill description show in the subform in the combo box?

"Klatuu" wrote:

I would think all your ID fields would be autonumber.
The Junction table (Employee Skills Table) does not need anthing else. You
can make its primary key the combination of the two fields.
This table is only used to group the skills the employee has and keep track
of information about that skill for the employee. For example, you may will
have a Skill Level. It would go in the Employee Skills Table.
So, when you want to look at the skills an employee has, you would navigate
to that employee's record. Your Employee Skills table would then appear to
be filtered on that employee. The description for the skill would be in the
Skills table.

"Patty Stoddard" wrote:

I completed the tables as you indicated. Now the junction table only has two
fields. What is the data type for each of these fields? Number? Do I need
a root key for the junction table?

"Klatuu" wrote:

Employee table needs EmpID and EmpName
Skills table needs SkillID and SkillDescription
Employee SkillsTable needs EmpID and SkillID
Create a one to many relation between Employee table and Employee Skills
table on EmpID
Create a one to many relation between Employee Skills table and Skills table
on SkillID

"Patty Stoddard" wrote:

I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?