Staff Database Design

[beaniebear]

I have a table of staff which has a primary key called staff emp. I want to be able to store who each member of staff reports to, this will be someone else in the same table. A member of staff can also possibly report to more than one person. Any advice on the table structure that I would need to use would be helpful. I am using access 2002 to store this information. I will need to pull reports off for each person who has people reporting to them. They will also need to get reports for the any person that reports to someone who reports directly to them. Thanks.

 

[ZmrAbdulla]

Why don't you look into the sample db (Northwind Traders) provided with MS Access. It has an example of this kind

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[beaniebear]

Thanks for that, but this exmaple uses one table and allows a staff member to report to one person only and would also allow members of staff to be deleted but still have people reporting to them. I have the situtation where members of staff may report to more than one person. I also want data integrity so whilst people have staff reporting to them they can't be deleted from the database. Any ideas?

 

[ZmrAbdulla]

For that you need to understand "Cascade Delete/Update" and Many-to-Many relationship.
see

http://r937.com/relational.html

fundmentals of RDBMS.

And

http://www.tek-tips.com/threadminder.cfm?pid=700

is
Microsoft: Access Tables and Relationships Forum. You may get better answer from there too.

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[harebrain]

Zameer is correct: you have a many-to-many relationship, not simply a reflexive relationship. The upshot is that you need a second table to employee-manager/manager-employee relationships.