If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Many to many relationship confusion
Hi all,
I am trying to set up an employee development database so that I can record details of an employees development within the company, but am having difficulty with the relationships. 1. Each branch can have several employees 2. Each branch has one branch manager and one regional manager I have set up two tables: branches and employees, and want to be able to record if, for example, an employee is promoted to branch manager or moves to a different branch or if a regional managers region changes. How do I set up the relationships and how do I set up a lookup(s) for each table to do this Any assistance you can offer would be appreciated Lee Kennedy |
#2
|
|||
|
|||
Many to many relationship confusion
Hi Lee,
It sounds as if you have Regions as well as Branches: each Region has one or more branches. Also, there needs to be something to store the successive positions that each employee holds. So, perhaps something like this: tblRegions RegionID (PK) RegionName tblBranches BranchID (PK) BranchName RegionID (FK into tblRegions) tblPositions PositionID PositionName (e.g. "Regional Manager", "Branch Manager", "Other") tblEmployees EmployeeID Name DateHired etc tblEmployeesPositions EmployeeID ) BranchID ) PositionID ) all 4 in primary key StartDate ) EndDate This way, the records in tblEmployeesPositions let you follow one employee's career; or successive managers of a branch; and so on. If this is a real-life organisation, you'll also need to take account of things like - branches being opened, closed, renamed, merged, moved, etc. - secondments, "acting" appointments, and so on - positions that are not attached to individual branches. (E.g., is a regional manager attached to a branch or a region or the centre? Sometimes this can be handled by treating each regional HQ as a branch.) On Sun, 23 May 2004 09:07:21 +0100, "Lee Kennedy" wrote: Hi all, I am trying to set up an employee development database so that I can record details of an employees development within the company, but am having difficulty with the relationships. 1. Each branch can have several employees 2. Each branch has one branch manager and one regional manager I have set up two tables: branches and employees, and want to be able to record if, for example, an employee is promoted to branch manager or moves to a different branch or if a regional managers region changes. How do I set up the relationships and how do I set up a lookup(s) for each table to do this Any assistance you can offer would be appreciated Lee Kennedy -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#3
|
|||
|
|||
Many to many relationship confusion
Thanks John
to clarify, approx 5 brances to a region and approx 4 regions to a division. With your help i've worked that out now. (couldn't see wood for trees!). Should I just leave the branch name field blank for each regional/divisionalas these positions are not attached to individual branches. Would there be any issues if I left it blank or would it be better to class a regional/divisional as "Not Assigned" and update this as a branch name? Lee "John Nurick" wrote in message ... Hi Lee, It sounds as if you have Regions as well as Branches: each Region has one or more branches. Also, there needs to be something to store the successive positions that each employee holds. So, perhaps something like this: tblRegions RegionID (PK) RegionName tblBranches BranchID (PK) BranchName RegionID (FK into tblRegions) tblPositions PositionID PositionName (e.g. "Regional Manager", "Branch Manager", "Other") tblEmployees EmployeeID Name DateHired etc tblEmployeesPositions EmployeeID ) BranchID ) PositionID ) all 4 in primary key StartDate ) EndDate This way, the records in tblEmployeesPositions let you follow one employee's career; or successive managers of a branch; and so on. If this is a real-life organisation, you'll also need to take account of things like - branches being opened, closed, renamed, merged, moved, etc. - secondments, "acting" appointments, and so on - positions that are not attached to individual branches. (E.g., is a regional manager attached to a branch or a region or the centre? Sometimes this can be handled by treating each regional HQ as a branch.) On Sun, 23 May 2004 09:07:21 +0100, "Lee Kennedy" wrote: Hi all, I am trying to set up an employee development database so that I can record details of an employees development within the company, but am having difficulty with the relationships. 1. Each branch can have several employees 2. Each branch has one branch manager and one regional manager I have set up two tables: branches and employees, and want to be able to record if, for example, an employee is promoted to branch manager or moves to a different branch or if a regional managers region changes. How do I set up the relationships and how do I set up a lookup(s) for each table to do this Any assistance you can offer would be appreciated Lee Kennedy -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#4
|
|||
|
|||
Many to many relationship confusion
If you allow blank (null) BranchIDs you'll hit trouble with
tblEmployeesPositions, where BranchID is part of the primary key and therefore nulls are not allowed. I'd probably do this by having dummy branches for each region and each division. On Mon, 24 May 2004 09:59:12 +0100, "Lee Kennedy" wrote: Thanks John to clarify, approx 5 brances to a region and approx 4 regions to a division. With your help i've worked that out now. (couldn't see wood for trees!). Should I just leave the branch name field blank for each regional/divisionalas these positions are not attached to individual branches. Would there be any issues if I left it blank or would it be better to class a regional/divisional as "Not Assigned" and update this as a branch name? Lee "John Nurick" wrote in message .. . Hi Lee, It sounds as if you have Regions as well as Branches: each Region has one or more branches. Also, there needs to be something to store the successive positions that each employee holds. So, perhaps something like this: tblRegions RegionID (PK) RegionName tblBranches BranchID (PK) BranchName RegionID (FK into tblRegions) tblPositions PositionID PositionName (e.g. "Regional Manager", "Branch Manager", "Other") tblEmployees EmployeeID Name DateHired etc tblEmployeesPositions EmployeeID ) BranchID ) PositionID ) all 4 in primary key StartDate ) EndDate This way, the records in tblEmployeesPositions let you follow one employee's career; or successive managers of a branch; and so on. If this is a real-life organisation, you'll also need to take account of things like - branches being opened, closed, renamed, merged, moved, etc. - secondments, "acting" appointments, and so on - positions that are not attached to individual branches. (E.g., is a regional manager attached to a branch or a region or the centre? Sometimes this can be handled by treating each regional HQ as a branch.) On Sun, 23 May 2004 09:07:21 +0100, "Lee Kennedy" wrote: Hi all, I am trying to set up an employee development database so that I can record details of an employees development within the company, but am having difficulty with the relationships. 1. Each branch can have several employees 2. Each branch has one branch manager and one regional manager I have set up two tables: branches and employees, and want to be able to record if, for example, an employee is promoted to branch manager or moves to a different branch or if a regional managers region changes. How do I set up the relationships and how do I set up a lookup(s) for each table to do this Any assistance you can offer would be appreciated Lee Kennedy -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#5
|
|||
|
|||
Many to many relationship confusion
Thanks for your help John
Lee "John Nurick" wrote in message ... If you allow blank (null) BranchIDs you'll hit trouble with tblEmployeesPositions, where BranchID is part of the primary key and therefore nulls are not allowed. I'd probably do this by having dummy branches for each region and each division. On Mon, 24 May 2004 09:59:12 +0100, "Lee Kennedy" wrote: Thanks John to clarify, approx 5 brances to a region and approx 4 regions to a division. With your help i've worked that out now. (couldn't see wood for trees!). Should I just leave the branch name field blank for each regional/divisionalas these positions are not attached to individual branches. Would there be any issues if I left it blank or would it be better to class a regional/divisional as "Not Assigned" and update this as a branch name? Lee "John Nurick" wrote in message .. . Hi Lee, It sounds as if you have Regions as well as Branches: each Region has one or more branches. Also, there needs to be something to store the successive positions that each employee holds. So, perhaps something like this: tblRegions RegionID (PK) RegionName tblBranches BranchID (PK) BranchName RegionID (FK into tblRegions) tblPositions PositionID PositionName (e.g. "Regional Manager", "Branch Manager", "Other") tblEmployees EmployeeID Name DateHired etc tblEmployeesPositions EmployeeID ) BranchID ) PositionID ) all 4 in primary key StartDate ) EndDate This way, the records in tblEmployeesPositions let you follow one employee's career; or successive managers of a branch; and so on. If this is a real-life organisation, you'll also need to take account of things like - branches being opened, closed, renamed, merged, moved, etc. - secondments, "acting" appointments, and so on - positions that are not attached to individual branches. (E.g., is a regional manager attached to a branch or a region or the centre? Sometimes this can be handled by treating each regional HQ as a branch.) On Sun, 23 May 2004 09:07:21 +0100, "Lee Kennedy" wrote: Hi all, I am trying to set up an employee development database so that I can record details of an employees development within the company, but am having difficulty with the relationships. 1. Each branch can have several employees 2. Each branch has one branch manager and one regional manager I have set up two tables: branches and employees, and want to be able to record if, for example, an employee is promoted to branch manager or moves to a different branch or if a regional managers region changes. How do I set up the relationships and how do I set up a lookup(s) for each table to do this Any assistance you can offer would be appreciated Lee Kennedy -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|