table relationships

[KK]

Hello,

I seem to need to set up 2 relationships between the same 2 tables - which
is forbidden & almost certainly means I'm designing the tables wrongly.

The 2 tables concerned are the 'managers' and 'staff' tables. Our staff have
2 managers, ie a line manager and a project manager.
Table 'managers' has fields such as manager_ID, name, etc etc. The 'staff'
table has fields such as staff_ID, name, etc, etc, linemanager_ID,
projectmanager_ID.

I want a query to show members of staff with both their managers names. I
can set up a relationship between manager_ID and linemanager_ID OK, but I
can't then relate manager_ID with projectmanager_ID. I think I'm
misunderstanding something fundamental.

Thanks for all help

KK

 

[Jeff Boyce]

I'm a little confused. You say you "can set up a relationship between
manager_ID and linemanager_ID". I thought you were trying to set up
relationships between each of those and your staff persons? And how many
manager types do you have -- from your description it sounds like three
(manager, project manager, line manager).

If you have a tblStaff, with a manager_ID (?projectmanager_ID?) and
linemanager_ID foreign key fields, you could simply create a query and use
two copies of your tblManager, one connected to each of the two "manager"
fields.

By the way, if your "managers" also work for the same company, they are
"staff", too, right?! You may be able to use self-joins and a single table.

 

[KK]

Thanks for your help

I had wondered about creating a duplicate of 'managers', and connecting one
copy to 'linemanager' and the other to 'projectmanager' .

I'm not sure what are self-joins ?

Thanks again.

KK

 

[Lynn Trapp]

KK,
I'm wondering why you have 2 tables. You can accomplish what you want with a
single table -- the Staff table. Simply put the Staff_id of a line manager
in an individual persons LineManager_ID field and the Staff_ID of a project
manager in that person's ProjectManager_ID field. You may also want to add a
field called Position that shows whether or not a person is a Line Manager,
a Project Manager, or whatever. A person who is a Line Manager might not
have anyone in their ProjectManager_ID field and a Line Manager who is at
the top of the chain would not have anyone in thier LineManager_ID field.

Let me know if you have any questions.

 

[Lynn Trapp]

Jeff's response didn't show up when I saw this, but hopefully my other
response will get you started in the right direction of a self-join.

 

[Bruce]

To elaborate a little on Lynn's answer (I hope I
understand it correctly) and on something Jeff wrote,
everybody works for the same company. Just set up an
Employee table for everybody in the company. You could
have a simple list containing line manager, project
manager, staff, or whatever title you would select for
that person, as Lynn suggested. In that way you could
easily generate queries that return only line managers,
for instance. This could be used to populate a combo box
or list box so that you can select a staff member's
managers.
DO NOT duplicate information in a second table. That
would be bad in most cases, certainly in yours.
If you are making a database to keep track of any sort of
employee information, if possible make an Employee table
that includes everybody in the company. You will probably
need such a listing at some point anyhow. Now if you need
an employee's name (or ID) to appear in another table (for
instance, as a staff member's line manager), you already
have the list of everybody's name, and you can use a query
to extract a subset of records. If the information
appears already, don't create it again. I maintain a
training records database in which some employees are
sometimes trainers and sometimes trainees. I manage all
names from a single Employee table.
By the way, consider what needs to happen if an employee's
name changes. We have a situation in which the actual
name at the time of a report needs to be stored in the
database. If I store EmployeeID, any name change will be
carried over to old records, so I store the name instead.

 

[KK]

Thanks, this was very helpful. My original instinct was to put all staff
into one table, but I didn't realise that I could create a link between one
field and another field in the same table - presumably what was meant by
self-join? What I'm aiming for is a query which shows project leader & line
manager details for each employee, and I think I can see how to do it now.

Thanks again

KK