If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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? |
#10
|
|||
|
|||
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? |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Total hours from Table into Report | HeatherLou1974 | General Discussion | 3 | March 7th, 2005 08:04 PM |
Double-Clicking a cell for Multiple Options | andym | General Discussion | 1 | February 24th, 2005 09:46 PM |
Ambiguous outer joins | Renwick | New Users | 1 | February 22nd, 2005 02:08 PM |
A multiple option group | Bernd | Using Forms | 7 | October 14th, 2004 07:29 PM |
Multiple Many-To-Many Tables | Tom | Database Design | 7 | May 15th, 2004 03:47 AM |