Query Criteria Question

[ckirmser]

I am befumped and befuddled.

I have a table with bunches o' records of employee data. Employees move between various departments often and a tenure of how long they were in their latest department is needed.

Unfortunately, every move and change in a department is kept in this table. For example, Bob was in dept #1 for two years on first shift, dept #3 for three years on second shift, dept#1 for three more years on first shift, then dept #1 for 2 years on second shift.

For Bob, the query would need to sum the five total most recent years in dept #1.

A query to just pull up the most recent years in a department won't work because it would lose the immediately prior tenure in dept #1 on a different shift. So, it would have to find all records for an employee in a department, summing the years, where there was no break in department code, regardless of other activities, like shift changes.

Clear as mud?

 

[Moniker]

So the structure is like this:

Employee   DeptNum   ShiftID   Years
Bob        1         1         2 
Bob        3         2         3
Bob        1         1         3
Bob        1         2         2

Group on Employee, DeptNum, and ShiftID, and Sum on the Years field using an aggregate query. (Click on the Sigma in the query designer to turn these on.) The result of that query is:

Employee   DeptNum   ShiftID   Years
Bob        1         1         5 
Bob        1         2         2
Bob        3         2         3

I believe that's your answer.

 

[ckirmser]

Thanx for the response, moniker, but that's not quite it. Your initial table is a good representation of the data, but what I have to determine is what Bob's tenure is in his most recent department, regardless of shift, but not total tenure; only that since he was last assigned to it.

I didn't think to use the code field, so I'll try to illustrate better here;

Emp.	Dept #	Shift	Eff. Date
Bob	1	1	2/2/1996
Bob	3	2	4/15/1998
Bob	1	1	10/3/2001
Bob	1	2	9/9/2003

So, though Bob spent 7 years, 7 months total in department #1, the period from '96 to '98 doesn't count because there was a break. I guess I used a poor example, earlier.

What I need is Bob's tenure in his most recent department for contiguous time in that department; in this case dept #1. I get tenure by subtracting the effective date from today's date, but I need the immediately prior date, too, because, though his shift changed, his department didn't. The answer, using the above data, is 5 years, 5 months in Dept #1 (10/3/2001 to date).

I need to sort of go back through Bob's department assignments until I get to a record where the department changes, at which time, I'd stop and subtract the immediately prior record's effective date from today's date, giving Bob's tenure in his most recent department assignment.

But, I don't know how to get access to determine changes between records. I don't know how to get it to compare one record's data to another's. Or, if it can.

Of course, I may be approaching this from the wrong direction, making it more complicated than it needs to be. There might be a simple Access formula that does just what I want. But, I have no clue what it might be...

 

[ckirmser]

This problem has raised its ugly head again, so I'm a-giving this a bump...

Any help out there?