hierarchy in access db design

[Sammy]

Hey guys,
I have this db with the following tables
tblstaff
ID -->PK
DepartmentID --FK to tblDepartments
FirstName
LastName

tblDepartments
ID --PK
DepartmentName
DepartmentSup -->FK to tblStaff

Supervisors can have multiple departments to supervise.
My problem is with the hierarchy when it comes to the supervisors's manager
"Manage more than one supervisors" and the Manager of the Supervisor's
Manager.

I need to be able to allow the supervisors to see the records of their
departments, the manager of the supervisor can see the supervisor's records
and the departments the run, finally the Big managers should have access to
everyone's records?

Any help or advise would be greatly appreciated.

Thanks

 

[strive4peace]

Hi Sammy,

Instead of putting DepartmentSup (which I would name StaffID_sup if it
were in that table) in the Departments table...

Create a table

*Supervisors*
SupID, autonumber
DeptID, long integer -- FK to Departments
StaffID, long integer -- FK to Staff
SupID_, long integer, DefaultValue --> Null -- SupID of Manager

There will probably be cases where one department may have multiple
supervisors -- when that happens, you will be ready!

ALSO

The ID in Staff should be named StaffID

The ID in Departments should be named DeptID

You should always qualify the name of an ID with what type of ID it is...

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*