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

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.

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.

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.

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.