Dates Validation for Overlapping & Gaps (Leo asked)

Discussion in 'Access Table Design' started by Leo, Dec 19, 2009.

  1. Leo

    Leo Guest

    Hi there,
    I need to have a table to keep employees salaries, in which each employee
    may have many different salaries on different ranges of dates, with fields of
    EmployeeID , StartDate, EndDate, Salary Amount. For example employee A can
    have a 2000 $ monthly salary from Jan 1st to Apr 30th , but 2500$ from May
    1st to Dec 31st.
    Data entry could be though a form or directly from table, but there must be
    a control or constrain over validity of date ranges (the span between
    StartDate and EndDate) in a way that no two records overlaps each other for
    the same employee and also no gap between entries. For example if Employee A
    receive a salary from Jan 1st to Mar 3oth , then the same employee can not
    have a record of salary for Feb 1st to Apr 30th (overlapping) or Jun 1st to
    Dec 31st (leave a gap).
    How can I impose such a control for date validation??
    I appreciate your help.
    Leo, Dec 19, 2009
    1. Advertisements

  2. hi Leo,

    If the constraint 'no gap' is a necessary, significant business rule
    then the solution is quite simple:

    Use no EndDate. Store only the employee's ID, the salary and the start
    date. The end date is determined by either the next records start date,
    or by an external date reflecting the employee's state - fired at.

    --> stefan <--
    Stefan Hoffmann, Dec 19, 2009
    1. Advertisements

  3. SQL Superfast, Mar 10, 2011
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.