Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153

    Question Reset Field Value On Anniversary Date

    I am working on a Paid-Time Off Database for my company. I need the field that has the employees remaining PTO to be reset to the full total every year on their anniversary date. I have a HireDate field and PTORemaining field and a LastUpdated field. I just can't figure out how to calculate if that date has passed since it was last opened and how to reset that field based off of how long the employee has worked here. I've never had to do anything like this before. I attached the database so you can see exactly what I'm dealing with. I just need the field to reset to either 56 or 96 based on if they had been here one year (56) or over three years (96) on their anniversary date. That also needs to be a number field so that I can calculate how much PTO a given employee has at any given time. I realize I am probably out of my league on this one but I'd really like to see this through.
    Attached Files Attached Files

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I would recommend keeping the PTO hours on a separate table. That way you will be able to see history over the years and use it on reports if needed. Also it prevents later arguments! Store the EmployeeID, PTO start and end dates, and total hours given for the year.

    Create a query which runs when the database opens. Add the main form to the database options so that it opens automatically and in the OnLoad event you can run a query which can go thru the employees and add new records where applicable.

    Query 1 : SELECT EmployeeData.EmployeeID, EmployeeData.HireDate, DateSerial(Format(Date(),"yyyy"),Format([hiredate],"mm"),Format([hiredate],"dd")) AS StartDate FROM EmployeeData;
    Query 2 : INSERT INTO EmployeePTO_ByYear ( EmployeeID, PTO_StartDate, PTO_EndDate, PTO_Hours )
    SELECT Query1.EmployeeID, Query1.StartDate, [StartDate]+364 AS EndDate, IIf(DateDiff("yyyy",[HireDate],[StartDate])>2,96,50) AS Hours
    FROM Query1 LEFT JOIN EmployeePTO_ByYear ON (Query1.EmployeeID = EmployeePTO_ByYear.EmployeeID) AND (Query1.StartDate = EmployeePTO_ByYear.PTO_StartDate)
    WHERE (((EmployeePTO_ByYear.EmployeeID) Is Null) AND ((EmployeePTO_ByYear.PTO_StartDate) Is Null));


  3. #3
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by aytee111 View Post
    I would recommend keeping the PTO hours on a separate table. That way you will be able to see history over the years and use it on reports if needed. Also it prevents later arguments! Store the EmployeeID, PTO start and end dates, and total hours given for the year.

    Create a query which runs when the database opens. Add the main form to the database options so that it opens automatically and in the OnLoad event you can run a query which can go thru the employees and add new records where applicable.

    Query 1 : SELECT EmployeeData.EmployeeID, EmployeeData.HireDate, DateSerial(Format(Date(),"yyyy"),Format([hiredate],"mm"),Format([hiredate],"dd")) AS StartDate FROM EmployeeData;
    Query 2 : INSERT INTO EmployeePTO_ByYear ( EmployeeID, PTO_StartDate, PTO_EndDate, PTO_Hours )
    SELECT Query1.EmployeeID, Query1.StartDate, [StartDate]+364 AS EndDate, IIf(DateDiff("yyyy",[HireDate],[StartDate])>2,96,50) AS Hours
    FROM Query1 LEFT JOIN EmployeePTO_ByYear ON (Query1.EmployeeID = EmployeePTO_ByYear.EmployeeID) AND (Query1.StartDate = EmployeePTO_ByYear.PTO_StartDate)
    WHERE (((EmployeePTO_ByYear.EmployeeID) Is Null) AND ((EmployeePTO_ByYear.PTO_StartDate) Is Null));

    That helps immensely. I am playing around with this now. THANK YOU!!!!!!

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Rereading this, I see a problem! This won't add all the records you want, see if you can see the issue and fix it, otherwise come back here.

  5. #5
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    How do I get the OnLoad event to run two queries like that?

  6. #6
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by aytee111 View Post
    Rereading this, I see a problem! This won't add all the records you want, see if you can see the issue and fix it, otherwise come back here.
    What is it?

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Hah, I thought it was skipping records - but it isn't! It's been a long week, lol.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In form design, add an event procedure to the OnLoad event.
    In VBA, add :
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryname"
    DoCmd.SetWarnings True

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You run only the one query, btw.

  10. #10
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    I can't figure out how to run both the queries from the OnLoad event. It will let me type in one query on the event.

  11. #11
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Select Event Procedure from the dropdown

  12. #12
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by aytee111 View Post
    In form design, add an event procedure to the OnLoad event.
    In VBA, add :
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryname"
    DoCmd.SetWarnings True
    OK, So my SQL view of the query looks like this:
    INSERT INTO EmployeePTO_ByYear ( EmployeeID, PTO_StartDate, PTO_EndDate, PTO_Hours )
    SELECT qryPTOPerYearData.EmployeeID, qryPTOPerYearData.PTO_StartDate, [StartDate]+364 AS PTO_EndDate, IIf(DateDiff("yyyy",[HireDate],[StartDate])>2,96,56) AS PTO_Hours
    FROM qryPTOPerYearData LEFT JOIN EmployeePTO_ByYear ON (qryPTOPerYearData.PTO_StartDate = EmployeePTO_ByYear.PTO_StartDate) AND (qryPTOPerYearData.EmployeeID = EmployeePTO_ByYear.EmployeeID)
    WHERE (((EmployeePTO_ByYear.EmployeeID) Is Null) AND ((EmployeePTO_ByYear.PTO_StartDate) Is Null));

    i saved the query1 as qryPTOPerYearData in the database?

  13. #13
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by aytee111 View Post
    In form design, add an event procedure to the OnLoad event.
    In VBA, add :
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryname"
    DoCmd.SetWarnings True
    it added everyone to the table all at once. i don't want their PTO to start over if they haven't met that year mark yet. I attached the database again so you can see it.
    Attached Files Attached Files

  14. #14
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What do you mean by "start over"? This value that you are adding to this table is the total hours they are given for the year, it will never change. Then next year you will add another record for the employee, on their anniversary date.

  15. #15
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I see that this query is adding data for 2017/2018. It is designed to run and add when a new year happens.

    In order to create records for employee's 2016/2017, run this one-time query to change the dates back.
    UPDATE EmployeePTO_ByYear SET EmployeePTO_ByYear.PTO_StartDate = [PTO_StartDate]-365, EmployeePTO_ByYear.PTO_EndDate = [PTO_EndDate]-365;

    Then re-run the query. I also made a change to the qryPTOPerYearData, I added a field to say only add records for employees where the hire month is before today - so that if they were hired in June a record will not be added until June, instead of adding it now.
    SELECT EmployeeData.EmployeeID, EmployeeData.HireDate, DateSerial(Format(Date(),"yyyy"),Format([hiredate],"mm"),Format([hiredate],"dd")) AS StartDate, Format([HireDate],"mm") AS HireMonth
    FROM EmployeeData
    WHERE (((Format([HireDate],"mm"))<=Format(Date(),"mm")));

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-09-2015, 02:15 PM
  2. Field Reset & Increment
    By Bracon in forum Access
    Replies: 1
    Last Post: 08-31-2014, 01:17 PM
  3. Reset a field when a new day starts
    By ashraf_al_ani in forum Forms
    Replies: 6
    Last Post: 08-05-2014, 10:02 AM
  4. Replies: 3
    Last Post: 05-21-2014, 05:38 PM
  5. Sum After Anniversary Date
    By vinsavant in forum Access
    Replies: 3
    Last Post: 02-18-2013, 07:02 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