How to selectively group in a query

[nekendrick]

I have an access table that contains the following information:




I want to summarize the elapsed time each time the operation changes, so my output looks like this:


Any thoughts on how to do this? I can't just group by operation as I need to show the different groupings of operation 200 and the time each started.

[recyan]

In the results, Wondering about,
Tony 6/10/2012 11:55:56 PM 200 6958
Tony 6/11/2012 02:11:28 AM 200 3

Have you tried, something like below :

SELECT 
    Table1.Employee, 
    DateValue([StartTime]) AS TheDate, 
    Table1.Operation, 
    Sum(Table1.ElapsedTime) AS SumOfElapsedTime, 
    Min(Table1.StartTime) AS MinOfStartTime
FROM 
    Table1
GROUP BY 
    Table1.Employee, 
    DateValue([StartTime]), 
    Table1.Operation;

Thanks

[nekendrick]

Thanks for the input and sorry my example or description weren't specific enough. I can't group on the date alone, as I need to group and sum the "operations" at each change of operation. So if operation 200 appears three times, then 444 appears 4 times, then 200 appears again twice, the desired result is:

employee 200 firstofstartdatetime sumofelapsed
employee 444 firstofstartdatetime sumofelapsed
employee 200 firstofstartdatetime sumofelapsed

[KelleyM]

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.

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.

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?