Multiple Ranges in a Query

[sdimaggio]

Is this Possible?

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

Is it possible to make a report/query with multiple ranges like that? Any suggestions?

Thanks,
Steve

 

[smedvid]

Do u have more information on what the database table(s) look like? The example, is that the desired result? Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[sdimaggio]

The Data Table info is as follows:

tbl_employees
Field1: Name
Field2: HireDate

tbl_WorkDays
Field1: Name
Field2: Date
Field3: Type of Day (ie. Vacation, Sick, etc)

on frm_Workdays I calculate:
[StartDate]
[TodaysDate]
Record Source: tbl_Workdays

I will form a query with an expression to calculate the StartDate

my query would look like this
Field1: Name
Field2: StartDate
Field3: TodaysDate
Field4: Type of Day

Hope this helps

 

[smedvid]

I created 2 tables, Employee and WorkInfo, and Join on EmpID. Here is what I threw together and it seems to work.

SELECT employee.empID, employee.Name, employee.HireDate, workInfo.type, Count(workInfo.empID) AS CountOfempID
FROM employee LEFT JOIN workInfo ON employee.empID = workInfo.empID
WHERE (((workInfo.workDate) Between [employee].[HireDate] And Date()))
GROUP BY employee.empID, employee.Name, employee.HireDate, workInfo.type;

htwh Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[sdimaggio]

Thanks

I'll Try it.