Query with most recent applicable criteria?

[twalsh]

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

 

[MichaelRay via AccessMonster.com]

That's not possible with your table structure. You can't use Sales as a
criteria because some of the records aren't in sales. I'm not sure exactly
what your trying to get at here. Is this a portion of a table that also
contains EmplyeeID, so for each employee you track which department he/she
worked in over the last 6 weeks?

If so, why not just query by Employee, then group the results by Department?
This would tell you how many hours (or the average) that they worked in each
department.