Originally Posted by
aytee111
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));