can we make that hire month and day or is that making it too much?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")));
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.
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.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")));
sorry i repeated myself. i'm trying to multitask.
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)i think some people even get more depending on what their job title is
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.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)
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.
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!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
What a surprise! Well done.
New copy of db
This is the original spreadsheet i'm working off of.
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?