I think in order to get the behavior you want you will need to introduce another field. Here is what I came up with.
Make two ID fields. ID1 numbers each line and ID2 contains the number of the line below the current line.
The table might look like this.
ID |
ID2 |
Emp_Name |
Start_Time |
Operation |
Elapsed_Time |
1 |
2 |
Tony |
6/10/2012 11:00:06 PM |
350 |
8 |
2 |
3 |
Tony |
6/10/2012 11:00:14 PM |
350 |
696 |
3 |
4 |
Tony |
6/10/2012 11:11:50 PM |
121 |
2646 |
4 |
5 |
Tony |
6/10/2012 11:55:56 PM |
200 |
258 |
5 |
6 |
Tony |
6/11/2012 12:00:14 AM |
200 |
6700 |
6 |
7 |
Tony |
6/11/2012 12:31:00 AM |
200 |
60 |
7 |
8 |
Tony |
6/11/2012 1:51:54 AM |
300 |
1162 |
8 |
9 |
Tony |
6/11/2012 2:11:28 AM |
200 |
3 |
Then write a query to update ID so that it only changes when the operation does.
Code:
UPDATE tblTest INNER JOIN tblTest AS tblTest_1 ON tblTest.ID = tblTest_1.ID2 SET tblTest.ID = [tblTest].[ID]-1
WHERE (((tblTest.Operation)=[tblTest_1].[Operation]) AND ((tblTest.ID)<>[tblTest_1].[ID]));
Then you could use a query like the one below to get your results.
Code:
SELECT tblTest.Emp_Name, First(tblTest.Start_Time) AS FirstOfStart_Time,
First(tblTest.Operation) AS FirstOfOperation, Sum(tblTest.Elapsed_Time) AS SumOfElapsed_Time
FROM tblTest
GROUP BY tblTest.ID, tblTest.Emp_Name;
The trouble with this solution is the update query would have to be run multiple times to handle an opperation appearing more than twice in a row.
This solution aside, is there anyway to port this part of the project to excel where it would be much less cumbersome to do this selective grouping?