Results 1 to 4 of 4
  1. #1
    nekendrick is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    2

    How to selectively group in a query

    I have an access table that contains the following information:



    Click image for larger version. 

Name:	1.jpg 
Views:	15 
Size:	25.2 KB 
ID:	9065


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

    Click image for larger version. 

Name:	2.jpg 
Views:	12 
Size:	19.2 KB 
ID:	9066
    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.

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    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 :
    Code:
    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

  3. #3
    nekendrick is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    2
    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

  4. #4
    KelleyM is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    9
    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?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Selectively delete entries from two tables
    By murukessan in forum Access
    Replies: 3
    Last Post: 08-19-2012, 05:15 AM
  2. Replies: 2
    Last Post: 06-21-2012, 07:40 PM
  3. Hide Duplicates selectively
    By SpaceEd in forum Reports
    Replies: 14
    Last Post: 11-03-2011, 01:24 PM
  4. query group by over another group by?
    By gap in forum Queries
    Replies: 2
    Last Post: 07-04-2011, 12:59 AM
  5. Using data in the import to selectively insert
    By mrbaggins in forum Import/Export Data
    Replies: 4
    Last Post: 11-25-2010, 06:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums