Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Staff Database Design

Staff Database Design

Staff Database Design

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.

RE: Staff Database Design

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

RE: Staff Database Design

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?

RE: Staff Database Design

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

RE: Staff Database Design

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close