[AccessD] Never Take a job for a friend (Three level designquestion)

Joe,

    My sample db named NotesHierarchical has just been uploaded to Rogers Access Library (other developers library). Link - http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.
 
    Apart from meeting the requirements outlined by you, it incorporates certain other desirable features. Brief description is given below.
 
Best wishes,
A.D.Tejpal
---------------

NotesHierarchical (Sample Db)
Brief Description
====================================
    This sample db demonstrates handling of performance notes in an organization. 

    Persons in administrative position are assigned various levels. For example the top boss has level 1. Managers next in command, each have level 2. Supervisors next in command, each have level 3. General employees do not have any level assigned to them. 

    Though three level administrative set up is shown in the db, it can be adapted to any number of levels, simply by assigning levels 4, 5 & so on. In order to identify clear chain of command, the immediate boss for each employee is identified via BossID.  For top boss, BossID field is blank. 

    Performance notes for a given employee as selected, can be recorded at various levels by his superiors in direct chain of command. A person in senior position can not alter the notes recorded by those below him (but can view the same). The row source for combo box meant for selecting employee is set dynamically in such a manner that it is confined only to those in direct chain of command under the selected noter. This involves recursive reference to BossID field in employees table, implemented via user defined function. 

    Any person in administrative position, wishing to record a note, has to log in using a password. On successful log in, the noter has full read/write access to his own notes for the selected employee. At the same time, all the notes for this employee, as recorded by other noters (only those in the chain of command below the current noter) get displayed, but in read-only state. 

    The notes table, has a Yes/No field named Sent. When Sent is set to true, the note in question can no longer be edited, even by the person who recorded it originally. More-over, once set to True, it can no longer be re-set to False and no back dated note can be created. For example, if note dated 31-Dec-2006 for a given combination of employee and noter has been marked sent, and we are now in year 2007, any new note with date prior to 31-Dec-2006 can not be created this noter for the given employee. 

    Version: Access 2000 File Format

    Reference: DAO 3.6
====================================

  ----- Original Message ----- 
  From: Joe Hecht 
  To: 'Access Developers discussion and problem solving' 
  Sent: Sunday, July 01, 2007 04:38
  Subject: [AccessD] Never Take a job for a friend (Three level designquestion)


  It is simple. Ya Right 

  I am righting a poor mans HR program. There are four user levels.
  Dispatchers can not do notes, can not see notes. 
  Field supervisor can write notes. Can not see manager or executive notes.  
  Managers can write notes, can read Field supervisor notes, not edit them or see executive notes.
  Executives can write theirs, see but not edit all other notes. 

  Notes are many notes to one employee.  

  How do I do notes so people see them in chronological order? If I do three sub tables how would I get all notes to same point. One employee can have multiple incidents good and bad in their record. How would I get all three levels of notes to same incident? 

  Ya all know where I am spending my sat night.

  Joe Hecht
  jmhecht at earthlink.net