Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Location
    East Coast, USA
    Posts
    3

    Question Unanswered: Date Functions/Anniversary Date

    I am trying to get a query to show results for leave type taken during an anniversary year. I don't want it to show each individual day the leave was taken, I only want a sum of hours for that particular year. I have been able to get it to show the sum for each year by calendar year, but how do I do it based on the start date?

    All help is appreciated.

    Following is the sql for the query as it stands now. With this query, it totals all the requested time for all the years the employee has been there.

    Code:
    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 MonthsEmployed, [MonthsEmployed]/12 AS YearsEmployed, IIf([MonthsEmployed]<=59,[MonthsEmployed]*6.67,IIf([MonthsEmployed]<=95,[MonthsEmployed]*10,IIf([MonthsEmployed]<=119,[MonthsEmployed]*11.33,IIf([MonthsEmployed]<=191,[MonthsEmployed]*13.33,IIf([MonthsEmployed]<=239,[MonthsEmployed]*14.67,IIf([MonthsEmployed]<=2000,[MonthsEmployed]*16.67,0)))))) AS VacTimeAcc, 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])=1) AND (([tbl-employee].Status)="Active") AND (([tbl-leaverequests].LeaveType)="Vacation"))
    ORDER BY [tbl-employee].EmployeeName;

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You may have to go with a subquery for this one.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2005
    Location
    East Coast, USA
    Posts
    3

    Post thanks...

    I will look into a subquery, I haven't done them as of yet, so not sure how to approach it. Back to the Books!

    Thank you for taking the time to respond.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •