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

    Unanswered: Best approach for Absence DBase

    Ive been given a brief to build a database (after sometime away from access) to record and monitor staff absences and in the whole its a straight forward task however Ive hit a wall in how to proceed with the actual absence tracking:

    The issue I have is:

    I have multiple offices eg- Office1, Office2, Office3 etc
    And
    Multiple absence types eg Sick1, Sick2, Sick3 etc.

    Now Office1 treats Sick1 differently in that after 4 days an action is need and after 8 days another.
    Office2 has an action needed after 8 days and after 12 days
    Office3 has and action after 10 day.

    What I need help with is some pointers in how to tackle the problem, whether its through Queries, reports etc not really sure where to start any help greatly appreciated.

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    It depends what you want to happen.

    All you've done is explain the situation, without specifying what you want to happen...

    After 4 days absence do you want a flag to appear on some form? An e-mail sent with their P45 attached? Flowers sent to their suspected widow?

    Setting up some VBA to check this won't be much work. But without knowing what you'd like to happen at these specified intervals, it's difficult to assist any more.
    Looking for the perfect beer...

  3. #3
    Join Date
    Nov 2011
    Posts
    12

    Good Point

    Yer sorry for that, had a moment I guess.

    Well I need it to flag the event so that it can be used for reports (if needed) but ultimately I'd like it to generate an email to the appropriate HR administrator with a form attached.

    This would be the same on the subsequent event points 8weeks, 10weeks etc only difference being a different form would be issued.

    Regards

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    It's difficult to help with this kind of thing, as there's going to be quite a lot of detail going in to getting that working for you.

    As a rough guide (as I finish work in a few minutes), you'll want to setup all your tables, etc (as you probably already have done).

    Then you'd want to have, probably in your main forms onload event, a piece of code that runs through each of the absent individuals.

    For each of the absent individuals, check which office they're in.

    Check for that office, what their policy is on absence.

    Then calculate how many relevant days (i.e. work days) the individual has missed (not just generic date differences).

    Once the number of days absent, and, the amount of time allowed before an action is necessary are both known, you can then compare them.

    If this comparison is true...
    i.e. Individual4 has been off of work for 4 days, and works in office1, so something needs to happen after 4 days, so there's a match.
    Then you can go about utilising this somehow. Perhaps populating a listbox with all the individuals that need some kind of action taken, and what actions are suggested, whenever the form is loaded.

    From that list, you could then setup a button, so the user (the administrator, you, whoever is going to be using this, basically), can select a user, hit the button, and an e-mail will fly off to their office's HR administrator.


    To get into the practicalities and code of how to achieve that will have to wait for another 16 hours I'm afraid. But depending on your abilities already, that should give you something to get started with.


    Hope this helps a bit, let me know how you get on.
    Looking for the perfect beer...

  5. #5
    Join Date
    Nov 2011
    Posts
    12

    The next Steps

    Thanks for that and I've started making good headway now, thanks for the pointers

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by JohnMc View Post
    Thanks for that and I've started making good headway now, thanks for the pointers
    No worries buddy, pleased your making progress.

    Just hit us back if you run into any troubles along the way.
    Looking for the perfect beer...

Posting Permissions

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