Query with Multiple Ranges - Repost

[sdimaggio]

I am trying to figure out how many vacations a person used given their start date to the current date. The problem is each employees range is different. Example:

Name StartDate TodaysDate VacationDaysUsed
John Doe 1/1/01 2/20/02 8
Mary Jane 6/1/01 2/20/02 9
Mike Smith 9/1/01 2/20/02 6

qry_Vactions
Field1 = EmployeeID
Field2 = StartDate
Field3 = TodaysDate

tbl_Attendance
Field1 = DayType (Vacation, Sick)
Field2 = Date

I formed a Query linking qry_Vactions and tbl_attendace.

How do I get Access to Count Vaction and Sick day totals by employees if it fall in between StartDate and TodaysDate?

Any Suggestions?

Thanks,
Steve

 

[fwatanabe]

Hi Steve,

You need to do a group by query. BTW, you do not say, but I hope you have an EmployeeID field in tbl_Attendance or this will not work! Also, you do not need to store today's date in the table, you can use the function Date() - unless you *want* to store today's date.

Select EmployeeID, StartDate, count(tbl_Attendance.Date) 
FROM tbl_Attendance, qry_Vacations
WHERE tblAttendance.Employee_ID = qry_Vacations.Employee_ID
and tbl_Attendance.Date between StartDate and Date()
Group by EmployeeID, StartDate

I hope my syntax is okay, I'm just typing off the top of my head. If you want to use the Query designer, go to the View menu and choose Totals - this will give you an extra row in the grid, where you can choose GROUP BY for the EmployeeID & StartDate fields and choose COUNT for the tbl_Attendance.Date field.

good luck - hope this helps.
Fuyo