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.
    --
    Thans & Best regards
    Leo, InfoSeeker
     
    Leo, Dec 19, 2009
    #1
    1. Advertisements

  2. hi Leo,

    On 19.12.2009 07:58, Leo wrote:
    > 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.

    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.

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

  3. Re: hi Leo,On 19.12.

    you can find sql query for the same at www.sqlsuperfast.com


    > On Saturday, December 19, 2009 1:58 AM Leo wrote:


    > 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.
    > --
    > Thans & Best regards
    > Leo, InfoSeeker



    >> On Saturday, December 19, 2009 5:00 AM Stefan Hoffmann wrote:


    >> hi Leo,
    >>
    >> On 19.12.2009 07:58, Leo wrote:
    >> 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.
    >>
    >> mfG
    >> --> stefan <--



    >> Submitted via EggHeadCafe
    >> New Features Of Microsoft Outlook 2010
    >> http://www.eggheadcafe.com/tutorial...8/new-features-of-microsoft-outlook-2010.aspx
     
    SQL Superfast, Mar 10, 2011
    #3
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Micky

    table level validation

    Micky, Jul 22, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    119
    Brian Camire
    Jul 22, 2003
  2. Mercy
    Replies:
    2
    Views:
    87
    Mercy
    Aug 13, 2003
  3. Serena

    Validation of one field based on another

    Serena, Aug 17, 2003, in forum: Access Table Design
    Replies:
    2
    Views:
    84
    Serena
    Aug 18, 2003
  4. J. Thomas Coons

    validation rule violations when there are no validation rules!

    J. Thomas Coons, Sep 25, 2003, in forum: Access Table Design
    Replies:
    0
    Views:
    211
    J. Thomas Coons
    Sep 25, 2003
  5. RicoDorsey
    Replies:
    1
    Views:
    108
    Duane Hookom
    Aug 16, 2005
  6. Randy

    Input Mask to prevent Gaps?

    Randy, Oct 11, 2005, in forum: Access Table Design
    Replies:
    5
    Views:
    74
  7. John W. Vinson
    Replies:
    1
    Views:
    105
  8. Peter Kinsman

    Autonumber records inserted in gaps

    Peter Kinsman, Jan 25, 2013, in forum: Access Table Design
    Replies:
    0
    Views:
    389
    Peter Kinsman
    Jan 25, 2013
Loading...