Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 34
  1. #16
    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 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")));
    can we make that hire month and day or is that making it too much?

  2. #17
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You can make it anything you want. It is all in the "format" function. I can tell you what to do or you can google it and see for yourself how powerful it is.

  3. #18
    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 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")));
    can we tweak this and say to only add if the month and day are older than today? instead of just month. because a lot of employees will take time off the week before their anniversary date as to use up any unused PTO they have. and when i say start over i mean every year on the anniversary date the PTO resets back up to 56 or 96 or i think some people even get more depending on what their job title is. I just didn't want it to do everyone all at the same time.

  4. #19
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    sorry i repeated myself. i'm trying to multitask.

  5. #20
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You won't do everyone all at the same time. The first query did that because the table was empty. But now that there are records it will only do the ones that are missing.

    There are also some table changes required to EmployeeData:
    - remove PTOAccrualRate per year, this is now on the new table
    - BeginningPTOBalance - move to EmployeePTO_ByYear table - this applies to the year, not to the employee, and should be put there when the new record is created
    - PTO Remaining - remove this totally, this is a calculated field and should not be stored on a table. Create a standard query that you run each time you need this information
    - PTOAccrued - not sure what this is at it is empty on your table, but it sounds like another calculated field - remove from tables if that is the case

    i think some people even get more depending on what their job title is
    In this case, add a field to EmployeeData table for Annual PTO Hours. If an employee has a value in here then use that in the query when adding a record to the new table - it will be an override to the calculation that is done in your query (part of the same IIf statement)

  6. #21
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by aytee111 View Post
    You won't do everyone all at the same time. The first query did that because the table was empty. But now that there are records it will only do the ones that are missing.

    There are also some table changes required to EmployeeData:
    - remove PTOAccrualRate per year, this is now on the new table
    - BeginningPTOBalance - move to EmployeePTO_ByYear table - this applies to the year, not to the employee, and should be put there when the new record is created
    - PTO Remaining - remove this totally, this is a calculated field and should not be stored on a table. Create a standard query that you run each time you need this information
    - PTOAccrued - not sure what this is at it is empty on your table, but it sounds like another calculated field - remove from tables if that is the case


    In this case, add a field to EmployeeData table for Annual PTO Hours. If an employee has a value in here then use that in the query when adding a record to the new table - it will be an override to the calculation that is done in your query (part of the same IIf statement)
    My brain has exploded at this point. It's been over a year since I've created any databases in access. I'm so rusty on it. How do I do an override with an Annual PTO field? I'm a bit lost. I know I can get the query to only add records to the precise date if I use an AND statement but I can't seem to put anything together today. I'm so glad it's Friday. I'm sorry to be working you so hard.

  7. #22
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Every time I try and do a WHERE statement with both the month and day, it removes the previous months days if they were after the same "dd" of that month.

  8. #23
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by breakingme10 View Post
    Every time I try and do a WHERE statement with both the month and day, it removes the previous months days if they were after the same "dd" of that month.
    Ugh found my mistake on that one. I got it adding new records by the day now.

  9. #24
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It's all very well multi-tasking when you don't have to think!

    Add the new field to EmployeeData as well as qryPTOPerYearData.
    Change the IIF statement in PTOAppendQry to this:
    IIf(IsNull(PTO_Hours_OR),IIf(DateDiff("yyyy",[HireDate],[PTO_StartDate])>2,96,56),PTO_Hours_OR) AS PTO_Hours

  10. #25
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by aytee111 View Post
    It's all very well multi-tasking when you don't have to think!

    Add the new field to EmployeeData as well as qryPTOPerYearData.
    Change the IIF statement in PTOAppendQry to this:
    IIf(IsNull(PTO_Hours_OR),IIf(DateDiff("yyyy",[HireDate],[PTO_StartDate])>2,96,56),PTO_Hours_OR) AS PTO_Hours
    Ok I think I got it. Now just to redesign all the forms. THANK YOU!

  11. #26
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What a surprise! Well done.

  12. #27
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    New copy of db
    Attached Files Attached Files

  13. #28
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by aytee111 View Post
    New copy of db
    YOU ARE A HUGE HELP!
    The PTORem field on the form isn't updating when you set the date.

  14. #29
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    This is the original spreadsheet i'm working off of.
    Attached Files Attached Files

  15. #30
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    The HoursRemaining table won't show the most recent year if they haven't used any PTO. I need to see everyone's CURRENT pto amount. Even if it's 0 or they haven't used any of it. Can you help?
    Attached Files Attached Files

Page 2 of 3 FirstFirst 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