Hi. I am a newbie to dBforums. I am also teaching myself ms access (ver 2002).

At work I am putting together a employee database which will include all aspects, including time request, time gained, etc. I have a query which calculates the time and feeds the results to a form for display purposes.

I am having a problem getting the query to display individual anniversary date years. For example, if employee A started on May 1, 2002 and was alloted 80 vacation time hours for the anniversary year (beginning on May 1, 2003) and took 80 vacation hours for the year period (may 1, 2003 - May 1, 2004) and the same for the anniversary year 2004 - 2005 ... I want the query to display each individual year and front load the hours for each year, instead of calculating the hours from date of hire to current date and subtracting total hours requested from date of hire to current date (as the sick leave requests work because this works off of calendar year).

I thought it would be a group by call in the query in the anniversary date field, however I can't figure out how to do this, the following is the sql I have now which produces the results by calendar year.

SELECT [tbl-employee].EmployeeName, [tbl-employee].[Employee Type], [tbl-employee].[Start Date], DateSerial(Year(Date()),Month([Start Date]),Day([Start Date])) AS AnniversaryDate, DateDiff("m",[Start Date],Now()) AS MthsEmp, [MthsEmp]/12 AS YrsEmp, DateSerial(Year([Start Date]),Month([Start Date])+1,Day("2")) AS BegOff, DateDiff("m",[BegOff],Now()) AS VacMths, IIf([VacMths]<=59,80,IIf([VacMths]<=119,120,IIf([VacMths]<=239,160,IIf([VacMths]<=999,200,0)))) AS VacTmeAcc, Sum([tbl-leaverequests].TotalHours) AS TimeReq
FROM [tbl-employee] INNER JOIN [tbl-leaverequests] ON [tbl-employee].EmployeeName = [tbl-leaverequests].EmployeeName
GROUP BY [tbl-employee].EmployeeName, [tbl-employee].[Employee Type], [tbl-employee].[Start Date], DateSerial(Year(Date()),Month([Start Date]),Day([Start Date])), [tbl-employee].Status, [tbl-leaverequests].LeaveType
HAVING ((([tbl-employee].[Employee Type])=2) AND (([tbl-employee].Status)="Active") AND (([tbl-leaverequests].LeaveType)="Vacation"))
ORDER BY [tbl-employee].EmployeeName;
Thanks in advance for the help.