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 |
#11
|
|||
|
|||
Thank you for the clarification. I think my junction table is what you call
the "Employee Skills" table. What you call the Skill table is named "Job Skill Categories". It has two fields: SkillID - autonumber - root key Job Skill description - text My junction table is now renamed to "Employee Job Skills". It has the following fields: SkillID - number ApplicantID - number Then I created a query based on the "Employee Job Skills" table and added fields from the main Employee Applicant Table. So far, so good. Now I tried to create a subform called "Job Skills" in my main form called "Employee Applicants". I based the subform on the query and defined the relationship between the main form and the subform as ApplicantID. Now, how do I create a combo box in the subform? Do I change the field "Job Skill Description" to a combo box? If so, how do I make it unbound? When I tried this, the properties for the field changed to a combo box said it is bound to column 1. What am I doing wrong? "Klatuu" wrote: 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? |
#12
|
|||
|
|||
No, you are exactly on track.
You can just change the row source of the combo box to be the Skills table. Then create the text box and identify the field in your query as the control source. You have defined the tables precisely as they should be. Good work. And yes, the junction table is the Employee Skills table. "Patty Stoddard" wrote: Thank you for the clarification. I think my junction table is what you call the "Employee Skills" table. What you call the Skill table is named "Job Skill Categories". It has two fields: SkillID - autonumber - root key Job Skill description - text My junction table is now renamed to "Employee Job Skills". It has the following fields: SkillID - number ApplicantID - number Then I created a query based on the "Employee Job Skills" table and added fields from the main Employee Applicant Table. So far, so good. Now I tried to create a subform called "Job Skills" in my main form called "Employee Applicants". I based the subform on the query and defined the relationship between the main form and the subform as ApplicantID. Now, how do I create a combo box in the subform? Do I change the field "Job Skill Description" to a combo box? If so, how do I make it unbound? When I tried this, the properties for the field changed to a combo box said it is bound to column 1. What am I doing wrong? "Klatuu" wrote: 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? |
#13
|
|||
|
|||
I had not defined the "Job Skill Description" in the "Job Skill Categories"
Table as a Lookup combo box with the Row Source Type "Table/Query" and Row Source "Employee Job Skills". Then, I defined my one-to-many relationships as: ApplicantID in "Employee Applicants" table to ApplicantID in "Employee Job Skills" table SkillID in "Job Skill Categories" table to SkillID in "Employee Job Skills" Howis the Many-to-many relationship defined? Let me know if that is what I should have done. Then, tell me how I should create the subform? Should I go to the main form and drag the subform icon to the detail section? If so, should I base it on the query? If so, should I include all fields from the query? Is so, do I change one of these fields, like "Job Skill Description" to a combo box? Or, should I add a combo box to the subform? When I tried this, I used the query for the values of my combo box. Then, I defined the applicantID as the field linking my main form to the subform. When I tried to view the subform, a box appeared that said I had typed the expression wrong. "Klatuu" wrote: No, you are exactly on track. You can just change the row source of the combo box to be the Skills table. Then create the text box and identify the field in your query as the control source. You have defined the tables precisely as they should be. Good work. And yes, the junction table is the Employee Skills table. "Patty Stoddard" wrote: Thank you for the clarification. I think my junction table is what you call the "Employee Skills" table. What you call the Skill table is named "Job Skill Categories". It has two fields: SkillID - autonumber - root key Job Skill description - text My junction table is now renamed to "Employee Job Skills". It has the following fields: SkillID - number ApplicantID - number Then I created a query based on the "Employee Job Skills" table and added fields from the main Employee Applicant Table. So far, so good. Now I tried to create a subform called "Job Skills" in my main form called "Employee Applicants". I based the subform on the query and defined the relationship between the main form and the subform as ApplicantID. Now, how do I create a combo box in the subform? Do I change the field "Job Skill Description" to a combo box? If so, how do I make it unbound? When I tried this, the properties for the field changed to a combo box said it is bound to column 1. What am I doing wrong? "Klatuu" wrote: 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? |
#14
|
|||
|
|||
There is no many to many relationship. That is what your Employee Job Skills
table is for, to resolve the many to many. So, the relationship is: Employee Applicant is one to many to Employee Job Skills Based on EmpID Employee Job Skills is one to many to JobSkill Categories Based on SkillID First create your sub form stand alone with the query as the record source. Then in design mode for the main form, use the subform/report icon to place and size the area for the sub form, and identify your subform as the form to be incldued as the sub form. "Patty Stoddard" wrote: I had not defined the "Job Skill Description" in the "Job Skill Categories" Table as a Lookup combo box with the Row Source Type "Table/Query" and Row Source "Employee Job Skills". Then, I defined my one-to-many relationships as: ApplicantID in "Employee Applicants" table to ApplicantID in "Employee Job Skills" table SkillID in "Job Skill Categories" table to SkillID in "Employee Job Skills" Howis the Many-to-many relationship defined? Let me know if that is what I should have done. Then, tell me how I should create the subform? Should I go to the main form and drag the subform icon to the detail section? If so, should I base it on the query? If so, should I include all fields from the query? Is so, do I change one of these fields, like "Job Skill Description" to a combo box? Or, should I add a combo box to the subform? When I tried this, I used the query for the values of my combo box. Then, I defined the applicantID as the field linking my main form to the subform. When I tried to view the subform, a box appeared that said I had typed the expression wrong. "Klatuu" wrote: No, you are exactly on track. You can just change the row source of the combo box to be the Skills table. Then create the text box and identify the field in your query as the control source. You have defined the tables precisely as they should be. Good work. And yes, the junction table is the Employee Skills table. "Patty Stoddard" wrote: Thank you for the clarification. I think my junction table is what you call the "Employee Skills" table. What you call the Skill table is named "Job Skill Categories". It has two fields: SkillID - autonumber - root key Job Skill description - text My junction table is now renamed to "Employee Job Skills". It has the following fields: SkillID - number ApplicantID - number Then I created a query based on the "Employee Job Skills" table and added fields from the main Employee Applicant Table. So far, so good. Now I tried to create a subform called "Job Skills" in my main form called "Employee Applicants". I based the subform on the query and defined the relationship between the main form and the subform as ApplicantID. Now, how do I create a combo box in the subform? Do I change the field "Job Skill Description" to a combo box? If so, how do I make it unbound? When I tried this, the properties for the field changed to a combo box said it is bound to column 1. What am I doing wrong? "Klatuu" wrote: 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? |
#15
|
|||
|
|||
I have created the subform and inserted it into the main form. But when I
view it, I get a box that says "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the experssion by assigning parts of the expression to variables." Note: When establishing the subform in the main form, I I defined the applicantID as the field linking my main form to the subform. Is this the problem? "Klatuu" wrote: There is no many to many relationship. That is what your Employee Job Skills table is for, to resolve the many to many. So, the relationship is: Employee Applicant is one to many to Employee Job Skills Based on EmpID Employee Job Skills is one to many to JobSkill Categories Based on SkillID First create your sub form stand alone with the query as the record source. Then in design mode for the main form, use the subform/report icon to place and size the area for the sub form, and identify your subform as the form to be incldued as the sub form. "Patty Stoddard" wrote: I had not defined the "Job Skill Description" in the "Job Skill Categories" Table as a Lookup combo box with the Row Source Type "Table/Query" and Row Source "Employee Job Skills". Then, I defined my one-to-many relationships as: ApplicantID in "Employee Applicants" table to ApplicantID in "Employee Job Skills" table SkillID in "Job Skill Categories" table to SkillID in "Employee Job Skills" Howis the Many-to-many relationship defined? Let me know if that is what I should have done. Then, tell me how I should create the subform? Should I go to the main form and drag the subform icon to the detail section? If so, should I base it on the query? If so, should I include all fields from the query? Is so, do I change one of these fields, like "Job Skill Description" to a combo box? Or, should I add a combo box to the subform? When I tried this, I used the query for the values of my combo box. Then, I defined the applicantID as the field linking my main form to the subform. When I tried to view the subform, a box appeared that said I had typed the expression wrong. "Klatuu" wrote: No, you are exactly on track. You can just change the row source of the combo box to be the Skills table. Then create the text box and identify the field in your query as the control source. You have defined the tables precisely as they should be. Good work. And yes, the junction table is the Employee Skills table. "Patty Stoddard" wrote: Thank you for the clarification. I think my junction table is what you call the "Employee Skills" table. What you call the Skill table is named "Job Skill Categories". It has two fields: SkillID - autonumber - root key Job Skill description - text My junction table is now renamed to "Employee Job Skills". It has the following fields: SkillID - number ApplicantID - number Then I created a query based on the "Employee Job Skills" table and added fields from the main Employee Applicant Table. So far, so good. Now I tried to create a subform called "Job Skills" in my main form called "Employee Applicants". I based the subform on the query and defined the relationship between the main form and the subform as ApplicantID. Now, how do I create a combo box in the subform? Do I change the field "Job Skill Description" to a combo box? If so, how do I make it unbound? When I tried this, the properties for the field changed to a combo box said it is bound to column 1. What am I doing wrong? "Klatuu" wrote: 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? |
#16
|
|||
|
|||
If we are talking about the same field I have been calling Employee ID that
you are calling Applicant ID, then yes, it is the correct field. Why you are getting that message, I don't know. When does the message appear? "Patty Stoddard" wrote: I have created the subform and inserted it into the main form. But when I view it, I get a box that says "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the experssion by assigning parts of the expression to variables." Note: When establishing the subform in the main form, I I defined the applicantID as the field linking my main form to the subform. Is this the problem? "Klatuu" wrote: There is no many to many relationship. That is what your Employee Job Skills table is for, to resolve the many to many. So, the relationship is: Employee Applicant is one to many to Employee Job Skills Based on EmpID Employee Job Skills is one to many to JobSkill Categories Based on SkillID First create your sub form stand alone with the query as the record source. Then in design mode for the main form, use the subform/report icon to place and size the area for the sub form, and identify your subform as the form to be incldued as the sub form. "Patty Stoddard" wrote: I had not defined the "Job Skill Description" in the "Job Skill Categories" Table as a Lookup combo box with the Row Source Type "Table/Query" and Row Source "Employee Job Skills". Then, I defined my one-to-many relationships as: ApplicantID in "Employee Applicants" table to ApplicantID in "Employee Job Skills" table SkillID in "Job Skill Categories" table to SkillID in "Employee Job Skills" Howis the Many-to-many relationship defined? Let me know if that is what I should have done. Then, tell me how I should create the subform? Should I go to the main form and drag the subform icon to the detail section? If so, should I base it on the query? If so, should I include all fields from the query? Is so, do I change one of these fields, like "Job Skill Description" to a combo box? Or, should I add a combo box to the subform? When I tried this, I used the query for the values of my combo box. Then, I defined the applicantID as the field linking my main form to the subform. When I tried to view the subform, a box appeared that said I had typed the expression wrong. "Klatuu" wrote: No, you are exactly on track. You can just change the row source of the combo box to be the Skills table. Then create the text box and identify the field in your query as the control source. You have defined the tables precisely as they should be. Good work. And yes, the junction table is the Employee Skills table. "Patty Stoddard" wrote: Thank you for the clarification. I think my junction table is what you call the "Employee Skills" table. What you call the Skill table is named "Job Skill Categories". It has two fields: SkillID - autonumber - root key Job Skill description - text My junction table is now renamed to "Employee Job Skills". It has the following fields: SkillID - number ApplicantID - number Then I created a query based on the "Employee Job Skills" table and added fields from the main Employee Applicant Table. So far, so good. Now I tried to create a subform called "Job Skills" in my main form called "Employee Applicants". I based the subform on the query and defined the relationship between the main form and the subform as ApplicantID. Now, how do I create a combo box in the subform? Do I change the field "Job Skill Description" to a combo box? If so, how do I make it unbound? When I tried this, the properties for the field changed to a combo box said it is bound to column 1. What am I doing wrong? "Klatuu" wrote: 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? |
#17
|
|||
|
|||
I get the message when I change from design view on the main for to form view.
"Klatuu" wrote: If we are talking about the same field I have been calling Employee ID that you are calling Applicant ID, then yes, it is the correct field. Why you are getting that message, I don't know. When does the message appear? "Patty Stoddard" wrote: I have created the subform and inserted it into the main form. But when I view it, I get a box that says "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the experssion by assigning parts of the expression to variables." Note: When establishing the subform in the main form, I I defined the applicantID as the field linking my main form to the subform. Is this the problem? "Klatuu" wrote: There is no many to many relationship. That is what your Employee Job Skills table is for, to resolve the many to many. So, the relationship is: Employee Applicant is one to many to Employee Job Skills Based on EmpID Employee Job Skills is one to many to JobSkill Categories Based on SkillID First create your sub form stand alone with the query as the record source. Then in design mode for the main form, use the subform/report icon to place and size the area for the sub form, and identify your subform as the form to be incldued as the sub form. "Patty Stoddard" wrote: I had not defined the "Job Skill Description" in the "Job Skill Categories" Table as a Lookup combo box with the Row Source Type "Table/Query" and Row Source "Employee Job Skills". Then, I defined my one-to-many relationships as: ApplicantID in "Employee Applicants" table to ApplicantID in "Employee Job Skills" table SkillID in "Job Skill Categories" table to SkillID in "Employee Job Skills" Howis the Many-to-many relationship defined? Let me know if that is what I should have done. Then, tell me how I should create the subform? Should I go to the main form and drag the subform icon to the detail section? If so, should I base it on the query? If so, should I include all fields from the query? Is so, do I change one of these fields, like "Job Skill Description" to a combo box? Or, should I add a combo box to the subform? When I tried this, I used the query for the values of my combo box. Then, I defined the applicantID as the field linking my main form to the subform. When I tried to view the subform, a box appeared that said I had typed the expression wrong. "Klatuu" wrote: No, you are exactly on track. You can just change the row source of the combo box to be the Skills table. Then create the text box and identify the field in your query as the control source. You have defined the tables precisely as they should be. Good work. And yes, the junction table is the Employee Skills table. "Patty Stoddard" wrote: Thank you for the clarification. I think my junction table is what you call the "Employee Skills" table. What you call the Skill table is named "Job Skill Categories". It has two fields: SkillID - autonumber - root key Job Skill description - text My junction table is now renamed to "Employee Job Skills". It has the following fields: SkillID - number ApplicantID - number Then I created a query based on the "Employee Job Skills" table and added fields from the main Employee Applicant Table. So far, so good. Now I tried to create a subform called "Job Skills" in my main form called "Employee Applicants". I based the subform on the query and defined the relationship between the main form and the subform as ApplicantID. Now, how do I create a combo box in the subform? Do I change the field "Job Skill Description" to a combo box? If so, how do I make it unbound? When I tried this, the properties for the field changed to a combo box said it is bound to column 1. What am I doing wrong? "Klatuu" wrote: 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? |
#18
|
|||
|
|||
This is usually a problem with a query. What queries do you have in the main
form and in the sub form? Some things you may not recognize as queries are like the row source for a combo box. Can you post back with any queries you have? For the record source of the sub form, if it is a store query (built in Access QBE), then change to SQL view and copy and paste it into your post. "Patty Stoddard" wrote: I get the message when I change from design view on the main for to form view. "Klatuu" wrote: If we are talking about the same field I have been calling Employee ID that you are calling Applicant ID, then yes, it is the correct field. Why you are getting that message, I don't know. When does the message appear? "Patty Stoddard" wrote: I have created the subform and inserted it into the main form. But when I view it, I get a box that says "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the experssion by assigning parts of the expression to variables." Note: When establishing the subform in the main form, I I defined the applicantID as the field linking my main form to the subform. Is this the problem? "Klatuu" wrote: There is no many to many relationship. That is what your Employee Job Skills table is for, to resolve the many to many. So, the relationship is: Employee Applicant is one to many to Employee Job Skills Based on EmpID Employee Job Skills is one to many to JobSkill Categories Based on SkillID First create your sub form stand alone with the query as the record source. Then in design mode for the main form, use the subform/report icon to place and size the area for the sub form, and identify your subform as the form to be incldued as the sub form. "Patty Stoddard" wrote: I had not defined the "Job Skill Description" in the "Job Skill Categories" Table as a Lookup combo box with the Row Source Type "Table/Query" and Row Source "Employee Job Skills". Then, I defined my one-to-many relationships as: ApplicantID in "Employee Applicants" table to ApplicantID in "Employee Job Skills" table SkillID in "Job Skill Categories" table to SkillID in "Employee Job Skills" Howis the Many-to-many relationship defined? Let me know if that is what I should have done. Then, tell me how I should create the subform? Should I go to the main form and drag the subform icon to the detail section? If so, should I base it on the query? If so, should I include all fields from the query? Is so, do I change one of these fields, like "Job Skill Description" to a combo box? Or, should I add a combo box to the subform? When I tried this, I used the query for the values of my combo box. Then, I defined the applicantID as the field linking my main form to the subform. When I tried to view the subform, a box appeared that said I had typed the expression wrong. "Klatuu" wrote: No, you are exactly on track. You can just change the row source of the combo box to be the Skills table. Then create the text box and identify the field in your query as the control source. You have defined the tables precisely as they should be. Good work. And yes, the junction table is the Employee Skills table. "Patty Stoddard" wrote: Thank you for the clarification. I think my junction table is what you call the "Employee Skills" table. What you call the Skill table is named "Job Skill Categories". It has two fields: SkillID - autonumber - root key Job Skill description - text My junction table is now renamed to "Employee Job Skills". It has the following fields: SkillID - number ApplicantID - number Then I created a query based on the "Employee Job Skills" table and added fields from the main Employee Applicant Table. So far, so good. Now I tried to create a subform called "Job Skills" in my main form called "Employee Applicants". I based the subform on the query and defined the relationship between the main form and the subform as ApplicantID. Now, how do I create a combo box in the subform? Do I change the field "Job Skill Description" to a combo box? If so, how do I make it unbound? When I tried this, the properties for the field changed to a combo box said it is bound to column 1. What am I doing wrong? "Klatuu" wrote: 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? |
#19
|
|||
|
|||
When I went to 'Tools' 'Analyzer' 'Documenter' for the only query I have
"Employee Job Skills Query" I got a message that said "Table 'doc_tbl objects' already exists. This is after I deleted all combo boxes from the form. How else can I get the information from the query/s to you? I am using AutoNum for the root key in both the Employee Applicant Table and the Job Skill Category Table. Does this cause a problem? I wonder how it will be able to distinguish between record 1 in one talbel and record 11 in the other table when the two are combined in the Employee Job Skills table as a root key for that table. Right now, however, I don't have a field in the Employee Job Skills Table that combines the ApplicantID and the SkillID. "Klatuu" wrote: This is usually a problem with a query. What queries do you have in the main form and in the sub form? Some things you may not recognize as queries are like the row source for a combo box. Can you post back with any queries you have? For the record source of the sub form, if it is a store query (built in Access QBE), then change to SQL view and copy and paste it into your post. "Patty Stoddard" wrote: I get the message when I change from design view on the main for to form view. "Klatuu" wrote: If we are talking about the same field I have been calling Employee ID that you are calling Applicant ID, then yes, it is the correct field. Why you are getting that message, I don't know. When does the message appear? "Patty Stoddard" wrote: I have created the subform and inserted it into the main form. But when I view it, I get a box that says "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the experssion by assigning parts of the expression to variables." Note: When establishing the subform in the main form, I I defined the applicantID as the field linking my main form to the subform. Is this the problem? "Klatuu" wrote: There is no many to many relationship. That is what your Employee Job Skills table is for, to resolve the many to many. So, the relationship is: Employee Applicant is one to many to Employee Job Skills Based on EmpID Employee Job Skills is one to many to JobSkill Categories Based on SkillID First create your sub form stand alone with the query as the record source. Then in design mode for the main form, use the subform/report icon to place and size the area for the sub form, and identify your subform as the form to be incldued as the sub form. "Patty Stoddard" wrote: I had not defined the "Job Skill Description" in the "Job Skill Categories" Table as a Lookup combo box with the Row Source Type "Table/Query" and Row Source "Employee Job Skills". Then, I defined my one-to-many relationships as: ApplicantID in "Employee Applicants" table to ApplicantID in "Employee Job Skills" table SkillID in "Job Skill Categories" table to SkillID in "Employee Job Skills" Howis the Many-to-many relationship defined? Let me know if that is what I should have done. Then, tell me how I should create the subform? Should I go to the main form and drag the subform icon to the detail section? If so, should I base it on the query? If so, should I include all fields from the query? Is so, do I change one of these fields, like "Job Skill Description" to a combo box? Or, should I add a combo box to the subform? When I tried this, I used the query for the values of my combo box. Then, I defined the applicantID as the field linking my main form to the subform. When I tried to view the subform, a box appeared that said I had typed the expression wrong. "Klatuu" wrote: No, you are exactly on track. You can just change the row source of the combo box to be the Skills table. Then create the text box and identify the field in your query as the control source. You have defined the tables precisely as they should be. Good work. And yes, the junction table is the Employee Skills table. "Patty Stoddard" wrote: Thank you for the clarification. I think my junction table is what you call the "Employee Skills" table. What you call the Skill table is named "Job Skill Categories". It has two fields: SkillID - autonumber - root key Job Skill description - text My junction table is now renamed to "Employee Job Skills". It has the following fields: SkillID - number ApplicantID - number Then I created a query based on the "Employee Job Skills" table and added fields from the main Employee Applicant Table. So far, so good. Now I tried to create a subform called "Job Skills" in my main form called "Employee Applicants". I based the subform on the query and defined the relationship between the main form and the subform as ApplicantID. Now, how do I create a combo box in the subform? Do I change the field "Job Skill Description" to a combo box? If so, how do I make it unbound? When I tried this, the properties for the field changed to a combo box said it is bound to column 1. What am I doing wrong? "Klatuu" wrote: 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? |
#20
|
|||
|
|||
The easiest way would be to open the query in design mode, select the SQL
view, and copy and paste it into a post. "Klatuu" wrote: This is usually a problem with a query. What queries do you have in the main form and in the sub form? Some things you may not recognize as queries are like the row source for a combo box. Can you post back with any queries you have? For the record source of the sub form, if it is a store query (built in Access QBE), then change to SQL view and copy and paste it into your post. "Patty Stoddard" wrote: I get the message when I change from design view on the main for to form view. "Klatuu" wrote: If we are talking about the same field I have been calling Employee ID that you are calling Applicant ID, then yes, it is the correct field. Why you are getting that message, I don't know. When does the message appear? "Patty Stoddard" wrote: I have created the subform and inserted it into the main form. But when I view it, I get a box that says "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the experssion by assigning parts of the expression to variables." Note: When establishing the subform in the main form, I I defined the applicantID as the field linking my main form to the subform. Is this the problem? "Klatuu" wrote: There is no many to many relationship. That is what your Employee Job Skills table is for, to resolve the many to many. So, the relationship is: Employee Applicant is one to many to Employee Job Skills Based on EmpID Employee Job Skills is one to many to JobSkill Categories Based on SkillID First create your sub form stand alone with the query as the record source. Then in design mode for the main form, use the subform/report icon to place and size the area for the sub form, and identify your subform as the form to be incldued as the sub form. "Patty Stoddard" wrote: I had not defined the "Job Skill Description" in the "Job Skill Categories" Table as a Lookup combo box with the Row Source Type "Table/Query" and Row Source "Employee Job Skills". Then, I defined my one-to-many relationships as: ApplicantID in "Employee Applicants" table to ApplicantID in "Employee Job Skills" table SkillID in "Job Skill Categories" table to SkillID in "Employee Job Skills" Howis the Many-to-many relationship defined? Let me know if that is what I should have done. Then, tell me how I should create the subform? Should I go to the main form and drag the subform icon to the detail section? If so, should I base it on the query? If so, should I include all fields from the query? Is so, do I change one of these fields, like "Job Skill Description" to a combo box? Or, should I add a combo box to the subform? When I tried this, I used the query for the values of my combo box. Then, I defined the applicantID as the field linking my main form to the subform. When I tried to view the subform, a box appeared that said I had typed the expression wrong. "Klatuu" wrote: No, you are exactly on track. You can just change the row source of the combo box to be the Skills table. Then create the text box and identify the field in your query as the control source. You have defined the tables precisely as they should be. Good work. And yes, the junction table is the Employee Skills table. "Patty Stoddard" wrote: Thank you for the clarification. I think my junction table is what you call the "Employee Skills" table. What you call the Skill table is named "Job Skill Categories". It has two fields: SkillID - autonumber - root key Job Skill description - text My junction table is now renamed to "Employee Job Skills". It has the following fields: SkillID - number ApplicantID - number Then I created a query based on the "Employee Job Skills" table and added fields from the main Employee Applicant Table. So far, so good. Now I tried to create a subform called "Job Skills" in my main form called "Employee Applicants". I based the subform on the query and defined the relationship between the main form and the subform as ApplicantID. Now, how do I create a combo box in the subform? Do I change the field "Job Skill Description" to a combo box? If so, how do I make it unbound? When I tried this, the properties for the field changed to a combo box said it is bound to column 1. What am I doing wrong? "Klatuu" wrote: 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 |