Results 1 to 10 of 10
  1. #1
    Rated047 is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    11

    Creating an Employee Daily Present/Absent Pie Chart From Leave Table

    I have a table named Employees and another table named EmpLeaves. Employees table holds all employee information while EmpLeaves is updated via a form and it has columns; EmpID, LeaveID and [Start Date].


    I want to create a pie chart is a form which shows the number of employees present and absent for the current date. If there is a record for the employee in the EmpLeave table for the current date then they should be considered absent, otherwise if a record on the current date does not exist then present.

    I need help with creating the table/query that will be the source for the pie chart. I can't seem to find a way to filter the employees who are present.

  2. #2
    madpiet is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    122
    Quote Originally Posted by Rated047 View Post
    I have a table named Employees and another table named EmpLeaves. Employees table holds all employee information while EmpLeaves is updated via a form and it has columns; EmpID, LeaveID and [Start Date].
    I want to create a pie chart is a form which shows the number of employees present and absent for the current date. If there is a record for the employee in the EmpLeave table for the current date then they should be considered absent, otherwise if a record on the current date does not exist then present.

    I need help with creating the table/query that will be the source for the pie chart. I can't seem to find a way to filter the employees who are present.
    I think you're missing some pieces. Wouldn't the EmplLeave table have (EmpID, StartDate, EndDate) ? Then If you have a Calendar table (basically one record per date for a range of dates), you can cross join Employee and Calendar and then subtract EmpLeave.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You did not give us enough info to be able to help you. A leave should have a start date and an end date; if the current day (=Date() or today) is between the Start and End dates of a leave then that employee is absent; if today's date is outside that range then they are present.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Then you have to consider employee hire and terminations, unless you completely delete employee. Count total employees employed on specified date and subtract absentees.

    See if this gets you on path:

    EmpLeave.zip

    PS: Since chart data is limited to a single date, its RowSource can be simplified to: SELECT [Cat], [Data] FROM [Chart] WHERE CalendarDate=tbxDate;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Rated047 is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    11
    Quote Originally Posted by madpiet View Post
    I think you're missing some pieces. Wouldn't the EmplLeave table have (EmpID, StartDate, EndDate) ? Then If you have a Calendar table (basically one record per date for a range of dates), you can cross join Employee and Calendar and then subtract EmpLeave.
    Yes sorry I do have EndDate as well but it made a vba code that adds the dates separately avoiding weekends (eg. if a leave is from 9th to 13th march 2023, then it would create separate records for March 9,12,13 as Friday and Saturday are weekends for us)

  6. #6
    Rated047 is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    11
    @June7 Thanks this helps a lot but may I ask is it a must to have the calendar table and should it have all the dates in the calendar year?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Calendar table required and must have at least a record for the date of interest. If only one record, textbox is not needed (except as a title for chart, bind form to Calendar table) and chart RowSource could be further simplified.

    SELECT [Cat],[Data] FROM [Chart];
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Rated047 is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    11
    Quote Originally Posted by June7 View Post
    Then you have to consider employee hire and terminations, unless you completely delete employee. Count total employees employed on specified date and subtract absentees.

    See if this gets you on path:

    EmpLeave.zip


    PS: Since chart data is limited to a single date, its RowSource can be simplified to: SELECT [Cat], [Data] FROM [Chart] WHERE CalendarDate=tbxDate;

    I am not familiar with the charts, but it seems to take a long time to load. Is there a way to make it load faster? Also the outlines are not very smooth.

  9. #9
    Rated047 is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    11
    Quote Originally Posted by June7 View Post
    Then you have to consider employee hire and terminations, unless you completely delete employee. Count total employees employed on specified date and subtract absentees.

    See if this gets you on path:

    EmpLeave.zip

    PS: Since chart data is limited to a single date, its RowSource can be simplified to: SELECT [Cat], [Data] FROM [Chart] WHERE CalendarDate=tbxDate;

    Is it possible to do this with the Insert Modern Chart option?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Probably slow because of Cartesian product nature of the two base queries which can cause slow performance in large datasets. The only way I could see to speed up would use VBA and temp table.

    I don't have Modern Chart available so can only guess would work but data manipulation would likely be same.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-13-2018, 12:16 PM
  2. need present and absent student.
    By shah1419 in forum Queries
    Replies: 3
    Last Post: 10-24-2017, 07:32 PM
  3. Creating a Daily update
    By willflemingthe1st in forum Access
    Replies: 1
    Last Post: 09-11-2015, 06:24 PM
  4. Replies: 2
    Last Post: 10-04-2014, 01:58 PM
  5. Replies: 1
    Last Post: 04-14-2014, 11:44 AM

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