Results 1 to 5 of 5
  1. #1
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82

    Multiple users, multiple sites, difficult rule for where an employee works and their status??

    Hi everyone,

    I'm developing a database for multiple users at multiple sites to use. One of the things the database tracks is if an employee is currently active or inactive (tblEmployees.Active is Boolean). This is important when generating most of our reports as inactive employees do not show up on most reports.



    When I initially rolled this database out, I had a simple function called CheckInactive that ran at login and a couple of times throughout the use of the forms. Very rarely. And this was only run at one site. It starts by making ALL employees active, then it evaluates the following business rules:

    1. If an employee is away on vacation, parental leave, or other absence, make them inactive;
    2. If an employee is working temporarily at another site, make them inactive;
    3. Any employees that were on a casual contract and that contract is now expired, make them inactive; and
    4. If an employee is now retired, make them inactive.


    Now that we are rolling out the database to other sites, #2 is causing some issues. New rules I have to follow to assess whether an employee is active or not at another site include:

    An employee is normally at site #1, but will be working at site #2 for the next three months:

    1. The DB user is at site #1 and will be generating reports. The employee should be inactive;
    2. The DB user is at site #2 and will be generating reports. The employee should be active if none of the other business rules apply; and
    3. The DB user is at site #3 and will be generating reports. The employee should be inactive.


    The only solution that I've come up with is to compare the employee's current work site to the DB user's work site and evaluate the rules above. I can run CheckInactive before any report is generated or before combo boxes are filled. This solution seems very heavy to me though. Currently, the DB does not have many users or employees, but it may start to grow quickly. I'm expecting ~100 users and ~5000 employees at full capacity.

    Any ideas or suggestions on how better to implement this?

    Thanks!

    Scott

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would always lean towards a query based solution rather than a code based solution, much as I like coding. How do you determine each of those criteria? Presuming the data is in tables, I'd think there would be a way to query it out. Perhaps that would end up with active employees with a site, so each site could query against that with a criteria for site.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    You could also create a table for “current” assigned (?) site. Depending on movement you cou use “until replaced” style (one date, no update problems) or a To From style (two dates, avoid the holes)

  4. #4
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    Quote Originally Posted by pbaldy View Post
    I would always lean towards a query based solution rather than a code based solution, much as I like coding. How do you determine each of those criteria? Presuming the data is in tables, I'd think there would be a way to query it out. Perhaps that would end up with active employees with a site, so each site could query against that with a criteria for site.
    I do have a table where it stores the employee ID number, temporary work location, start and end dates.

    The module CheckInactive is basically running the following query for me and creating a record set:

    Code:
    SELECT tblEmployeeAssignments.EmpID, tblEmployeeAssignments.StartDate, tblEmployeeAssignments.EndDate 
    FROM ((tblEmployeeAssignments INNER JOIN tblOffices ON tblEmployeeAssignments.OfficeID = tblOffices.ID) INNER JOIN tblEmployee ON tblEmployeeAssignments.EmpID = tblEmployee.EmpID) INNER JOIN tblOffices AS tblOffices_1 ON tblEmployee.Office = tblOffices_1.ID 
    WHERE (((tblEmployeeAssignments.StartDate)<=Format(Now(),"Short Date")) AND ((tblEmployeeAssignments.EndDate)>=Format(Now(),"Short Date")) AND ((IIf([tblOffices_1].[SiteID]<>[tblOffices].[SiteID],-1,0))=-1) AND ((IIf(FindSiteID()<>[tblOffices].[SiteID],-1,0))=-1))
    Followed by:

    Code:
    CurrentDb.Execute "UPDATE tblEmployee SET Active=0 WHERE EmpID=" & rs!EmpID & ";"
    Not eloquent at all, but it seems to be working. I am not great with SQL, so using VBA with the record set is a bit of a work around for me!

    But that's why I'm asking!!! Like I said, it seems like doing it this way is very resource intensive.

  5. #5
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    I know it tough to throw anything useful out, but ... Sometimes a clean start is better, think outside the box.

    Write down all the ways you”know” where an employee is, and where this data is stored (and how).

    SQL is quick and the result can stored globally, Tables are slower but are not dynamic, you have to update them, here and not here are logical opposite, that can confuse people.
    Think about what you are trying to say (do), how often does it need to be updated, how often the data is updated, and what other people will understand. Keep it simple (logically). A > B > C. Small steps is good way of thinking this though.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Status Of Employee!
    By cap.zadi in forum Queries
    Replies: 9
    Last Post: 12-02-2018, 11:52 PM
  2. Replies: 1
    Last Post: 06-17-2018, 02:12 AM
  3. Employee job status(Free or not freee)
    By updatesvc in forum Access
    Replies: 8
    Last Post: 08-25-2015, 02:27 AM
  4. Multiple Validation Rule
    By gatsby in forum Access
    Replies: 20
    Last Post: 01-15-2013, 05:24 AM
  5. Replies: 2
    Last Post: 08-31-2012, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums