Query by Employee Evaluation Date Range

[Phantek]

I have one table with all my employee information, including the start and end dates of their evaluation years (based on an employee's hire date).

I have a second table that has all the accounts the employee has resolved, including the date of resolution. This table lists the employee ID of the employee that resolved the account.

What I would like to do is to come up with a query that lists all the accounts that each of my employees has resolved during their evaluation year. So for example, employee 1 has an evaluation year of April 1, 2010 - March 31, 2011. Employee 2 has an evaluation year of September 21, 2010 - September 20, 2011.

So if employee 1 resolved an account on August 3, 2011, I am not interested for this query. But if employee 2 resolved an account on August 3, I would be interested as it is within their evaluation year.

Is there any way I can generate a query tha would be able to do this? I am using Access 2003.

 

[plog]

Yes, you link your tables and then create a calculated field that tests to see if an account resolution falls within the employees evaluation dates then apply criteria to that field. It would look like this:

ValidResolvedAccount: IIF([ResolveDate]>=[EvaluationBegin] AND [ResolveDate]<=EvaluationEnd, 1,0)

Then below, for the criteria, you enter "1" which will make the query show just the resolved accounts that fall between the employees evaluation dates.

 

[Phantek]

Fatastic - thanks for your help plog!