Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: One to Many relationship

    How do I create a table or a form with multiple fields (Manager information so fname, lname, performance review, and people managed). Under people managed, HR should have the ability to select multiple people because a manager can manage multiple people. Also would it be smarter to create a drop down list or check box for HR to select all the names or better for HR to type in the names of all the people the manager manages?

    Please help as I tried creating a subform in the manager form and it came out weird.

    Thank you!

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Relationship table required

    Your problem presupposes that you have a table which is a list of managers and another which is a list of people. To link them with a one-to-many relationship, you will need a relationship table which, at its simplest, holds the primary key of the manager and the primary key of a person.
    There are two ways to set up the form. Either it can be opened with a particular manager nominated or you can select a manager from a combo box. In the latter case, clicking on the combo box will invoke some VBA code which will set the form filter appropriately and load the manager data.
    The subform will reference the relationship table and join to the main form through the managers primary key. The person key will be a combo box showing the person's details in the person table with the primary key being hidden unless it is part of the person's ID (personally, I always use numeric primary keys via the Autonumber facility). To add a person to the list, simply click on the star selector and pick the person from the combo box.
    If you wish to edit the person record, it gets a bit more complicated, but the most straightforward way is to open the person record in a new form by double-clicking on the selector field in the appropriate line in the subform (some VBA needed here also). After editing the record, close it and return to the manager's list.
    I hope this is helpful.

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    Thanks for the prompt reply Jim!

    Yeah all employees (managers and regular employees have an EmployeeID) so I guess I can create another table called employeesManaged which will have ManagerID and EmployeeID as fields.

    As for the second part, I want it the other way around. I know it makes more sense to be able to select the managers from the employee form, however the purpose of this table/form is to rate the managers. Therefore it is more convenient to know which employees this person manages. For example: I want to rate John Doe from 1-5, then I want to know everything about John Doe such as name, employeeID, whether the manager is above plan, on plan or below plan and employees and interns managed.

    Any thoughts on that?

  4. #4
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Actually, I had specified the main form to be the manager details with a list of employees in the subform, which I think is what you want. However, it does raise the point that your relationship table could be many to many, so that you could call up an employee record and list all the managers he/she had worked for, together with date ranges and so on. This can be useful in determining how managers rate in terms of employee relationships; e.g is a manager's behaviour triggered by having to work with particular employees.
    Last edited by Jim Wright; 03-22-10 at 21:10. Reason: SPELLING !!!

  5. #5
    Join Date
    Mar 2010
    Posts
    4
    Ah yes sorry about that. I got thrown off by the selecting managers from a combo box. But yes I def. want a list of employees in the subform. Do not want a many-to-many relationship although its a great idea but boss just wants a basic DB to search for managers and see their rating. The list of employees managed by the Manager will change every 6-8 months as it is a rotational program.

  6. #6
    Join Date
    Mar 2010
    Posts
    4
    I am trying to create the Relationship table, so I go into Tools --> Relationships and mess around there. However, the Primary Key in the Managers table is their EmployeeID, and the primary key for the Managed Employees is Fullname...how would I go about creating a one to many relationship there?

    Thanks

  7. #7
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    The first thing is that the relationship diagram does not have anything to do with table creation as such. The relationship helps manage indexes and referential integrity. The relationship table is just another table created in the ordinary way. You can create a one-to-many relationship between manager and employee in the diagram. Then, having created your managerEmployee table, you can create a one-to-many relationship between each of the entity tables and its own key in the relationship table. This helps to avoid duplications and so forth.
    As to creating the "logical" relationship, add the tables to the relationship screen, drag the primary key from the entity table to the relationship table and then, when the type of relationship form comes up, select the appropriate one-to-many option through the JoinType button. The employee side will be one-to-one and the manager side one-to-many. You can get away with adding a manager key in the employee record, but as you say, your rotate employees every few months. By using a separate relationship table, you can maintain historical data if you need to run old reports, or compare old with new. In that case, make the employee join a one-to-many in the relational table and add start and end dates to it. The direct link means that old data is lost.
    This is an interesting little problem as it illustrates how database design can depend as much on movement forward in time, as it does on the static information.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •