I have a query that is showing an average of hours worked over 6 weeks. I
need to query by both the range of weeks and by the employee's department.
The issue is that some employees changed department during the 6 weeks. Is
there a way for the query to get the last 6 weeks based on the most recent
weeks department even though not all 6 will have the same department?

For Example:
I want to enter weeks 1 through 6 for the sales dept, how do i enter Sales
as the dept criteria and still get all 6 weeks instead of just the 5 they
were on sales?

Dept     Hours     week
Sales    30          1
Sales    35          2
Sales    36          3
Sales    39          4
Colle     30          5

thanks