Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    12

    Unanswered: Calculating Absences

    I've been plugging away at a dbase for a couple of weeks now and so far thanks to a littel guidence I've done well, however I've become a little stuck with my latest !

    I'm looking to calculate the number of separate absence periods a member of staff has (if any) over a rolling 12 month period, eg if some one had 5 separate absences over a 12 month period it would set a flag.

    What I need is just a little direction into what function I need to do this !

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    Here is the SQL I used

    Code:
    SELECT Table.NameFld, Count(Table.Field) AS CountOfField
    FROM Table
    WHERE (((Table.DateFld) Between Now()-365 And Now()))
    GROUP BY Table.NameFld;
    I used the name and date fields, then picked another field from the table and ran a count.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Are you saying if they have 2days off in a row that is counted a one occurrence
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Nov 2011
    Posts
    12

    Indeed

    Yes no mater the length of the period in question (1 day, 3 days, 7 days etc) each one would be classed as a single absence period.

    Regards

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Off top of head

    how are you storing the absence hope it some like

    absence <= table
    absenceID <=PK
    EmpID <= EmpID form Emp Table
    DateOff <= First day Off
    DateBack <= First day back
    Reason

    then we can run a Query to find the number Days off between DateOff dateBack
    hint DaysOff = dateDiff("d",DateOff,DateBack)


    this query should output something look like

    EmpID
    DateOff
    DaysOff

    now if you count the number of times EmpID is between now() - 365 days should give you the number of absence in that period


    just need to think about how to handle emp who are off but not yet back
    Last edited by myle; 11-23-11 at 15:56. Reason: Spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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