Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Central Florida, USA
    Thanked 0 Times in 0 Posts

    Northwind Employees (Access 97/2k)

    Theoretically speaking, what if the Northwind company was in an industry where employees were hired for a period of time, then layed off/terminated during slow times, and then rehired when things picked back up? What would be the best approach to tracking the multiple rehire and term dates AND how would you handle that on a form like the one presented in the Northwind database?

    Two examples come to mind as far as possible table structures addressing the rehire and term date:

    1 - A secondary related table that tracks hire and term dates as follows:
    Table name tblEmployeeDate
    tblEmployeeDate.numEmployeeID (as foreign key linking to the Employee table )
    tblEmployeeDate.dtmHireDate (hire date)
    tblEmployeeDate.dtmTermDate (term date)

    2 - A secondary related table that tracks a date (hire or term) and a field that differentiates between the type of record/date.
    Table Name tblEmployeeDate
    tblEmployeeDate.numEmployeeID (as foreign key linking to the Employee table )
    tblEmployeeDate.strType (for type of date such as "Hire" and "Term")
    tblEmployeeDate.dtmDate (event date)

    Would either of the methods be acceptable? On the form used for reading records, the MAX function could be used to display only the most current hire date (and/or term date) on their form I think. But I don't know how to control editing an employee's record, and adding either a rehire or term date.

    This is probably very simple, but I cannot see the forest for the trees.

    Thanks for your consideration.


  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Dallas, Texas, USA
    Thanked 0 Times in 0 Posts

    Re: Northwind Employees (Access 97/2k)

    Mr. Ken?

    Well, this sounds SO FAMILIAR....I wonder where I have been asked this before! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    I would recommend using the first option, where you store the Hire and Term date.

    Then, what I would do, as far as displaying this information, is to make the Employee form have a subform, that displays (READ ONLY) the Hire and Term dates of the past (where BOTH dates are filled only a complete Hire/Term date). I would then have two unbound controls on the form. The first unbound I would fill with the Hire date from the record with the 'missing' Term date. Then the second Unbound control would be the blank term date. Filling that in 'terminates' the employee, and adds the missing date to that record. If no records are found with a missing term date, I would leave the first unbound control blank (and disable/lock the Term unbound textbox), so that it is ready to hire them again...showing they are not current employees.

    Thus, to determine if someone was active or not, you don't need a max, just a search for a blank date field!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts