Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    manojcys is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    11

    Group By Reason and Sum Downtime for every Reason.

    I've created a query with Start Date and End Date parameter so that when someone enters the start date and end date it will extract data in between those dates. Now the extracted data has 2 columns one for reason and the second column for downtime. I would like to add downtime for same reasons instead of having many rows. I tried using Group By SUM in the query but is showing an error as a complicated expression; simplify it. Can someone help me with this?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Post the attempted SQL statement and example data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    manojcys is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    11
    Quote Originally Posted by June7 View Post
    Post the attempted SQL statement and example data.
    I am new to access and SQL.


    SELECT tblPounderDowntime.TimeUp, tblPounderDowntime.Reason, Format([TimeUp]-[TimeDown],"hh:nn:ss") AS Downtime
    FROM tblPounderDowntime
    WHERE (((tblPounderDowntime.TimeUp)>=[Start Date] And (tblPounderDowntime.TimeUp)<=[End Date]))
    ORDER BY Format([TimeUp]-[TimeDown],"hh:nn:ss") DESC;


    When I run Query I am getting a table in the following way:

    REASON
    DOWNTIME
    A 12min
    A 10min
    A 11min
    B 5min
    B 10min
    C 10min


    I need a table which groups reason and sums them.


    REASON
    DOWNTIME
    A 33 min
    B 15min
    C 10min


    Can you help me with this?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    I would like example raw data. TimeUp and TimeDown are date/time fields with date and time parts? [Start Date] is an input parameter?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    manojcys is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    11
    Time Format:8/19/2017 9:58:48 AM

    Yeah StartDate and EndDate are input parameters

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    I am asking you to provide a set of the same data you are working with so I can test for the same output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    manojcys is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    11
    Uploaded Sample data

    Please find it in the attachment below.
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    You did not provide the workbook so there is no data. Provide workbook or import into table or type into post like you did the example output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    manojcys is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    11
    Raw Data:

    TimeDown TimeUp Reason
    8/11/2017 9:58:48 AM 8/11/2017 1:23:56 PM Blue Conveyor
    8/11/2017 9:46:31 AM 8/10/2017 12:18:17 PM Blue Conveyor
    8/10/2017 12:21:12 PM 8/10/2017 12:39:12 PM Palletizer
    8/11/2017 10:14:57 AM 8/11/2017 10:15:41 AM Palletizer
    8/11/2017 10:35:18 AM 8/11/2017 10:36:21 AM Palletizer
    8/11/2017 12:15:19 PM 8/11/2017 12:15:20 PM QC
    8/11/2017 12:15:25 PM 8/11/2017 12:15:25 PM Metal Detector
    8/11/2017 12:15:30 PM 8/11/2017 12:15:30 PM Metal Detector
    8/11/2017 12:15:36 PM 8/11/2017 12:15:36 PM North Paddle Conveyor
    8/11/2017 12:15:40 PM 8/11/2017 12:15:40 PM North Paddle Conveyor
    8/11/2017 12:15:45 PM 8/11/2017 12:15:45 PM Dumper
    8/14/2017 11:28:17 AM 8/14/2017 11:28:18 AM
    8/14/2017 11:28:20 AM 8/14/2017 11:28:20 AM
    8/17/2017 9:00:52 AM 8/17/2017 9:00:52 AM
    8/17/2017 9:11:09 AM 8/17/2017 9:11:10 AM




    SQL Code Used:


    SELECT tblPounderDowntime.TimeUp, tblPounderDowntime.Reason, Format([TimeUp]-[TimeDown],"hh:nn:ss") AS Downtime
    FROM tblPounderDowntime
    WHERE (((tblPounderDowntime.TimeUp)>=[Start Date] And (tblPounderDowntime.TimeUp)<=[End Date]))
    ORDER BY Format([TimeUp]-[TimeDown],"hh:nn:ss") DESC;


    Query Result:

    TimeUp Reason Downtime
    8/10/2017 12:18:17 PM Blue Conveyor 21:28:14
    8/11/2017 1:23:56 PM Blue Conveyor 03:25:08
    8/10/2017 12:39:12 PM Palletizer 00:18:00
    8/11/2017 10:36:21 AM Palletizer 00:01:03
    8/11/2017 10:15:41 AM Palletizer 00:00:44
    8/11/2017 12:15:20 PM QC 00:00:01
    8/11/2017 12:15:45 PM Dumper 00:00:00
    8/11/2017 12:15:40 PM North Paddle Conveyor 00:00:00
    8/11/2017 12:15:36 PM North Paddle Conveyor 00:00:00
    8/11/2017 12:15:30 PM Metal Detector 00:00:00
    8/11/2017 12:15:25 PM Metal Detector 00:00:00


    I've tried to group and Sum them by using Sigma button on top in query section but when running the query it is showing error as This Expression is typed incorrectly or it is too complex to evaluate

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Can't Sum the formatted results of the calculation because that is a string, not a number. Sorry, I should have noticed that back in post 3.

    Remove the Format function and see what you get. Don't think you will like it. Now try Round function instead.

    Instead of aggregating in query, consider building a report and use its Sorting & Grouping features with aggregate calcs. A report allows display of detail data as well as summary calcs. Apply formatting in textboxes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    manojcys is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    11
    Can you please explain me in detail how to group and sum them in the report? I am new to access

  12. #12
    manojcys is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    11
    Can you please explain to me how to round them?
    TimeUp Reason Downtime
    8/11/2017 1:23:56 PM Blue Conveyor 0.14
    8/10/2017 12:39:12 PM Palletizer 0.01
    8/11/2017 10:36:21 AM Palletizer 0.00
    8/11/2017 10:15:41 AM Palletizer 0.00
    8/11/2017 12:15:20 PM QC 0.00
    8/11/2017 12:15:45 PM Dumper 0.00
    8/11/2017 12:15:40 PM North Paddle Conveyor 0.00
    8/11/2017 12:15:36 PM North Paddle Conveyor 0.00
    8/11/2017 12:15:30 PM Metal Detector 0.00
    8/11/2017 12:15:25 PM Metal Detector 0.00
    8/10/2017 12:18:17 PM Blue Conveyor -0.89

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    If you must aggregate and format in query, consider:

    SELECT tblPounderDowntime.Reason, Format(Sum([TimeUp]-[TimeDown]),"hh:nn:ss") AS Downtime
    FROM tblPounderDowntime
    WHERE (((tblPounderDowntime.TimeUp) Between [Start Date] And [End Date]))
    GROUP BY tblPounderDowntime.Reason;

    Explaining reports is too involved. Just start building and explore. Check out on-line tutorials - here is one https://support.office.com/en-us/art...2-4A23AC0FDBF3. Lots of reference books out there.

    Round function https://support.office.com/en-us/art...7-28E685B59935

    Round([TimeUp]-[TimeDown], 2)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    manojcys is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    11
    SQL code you provided worked!!

    Thanks allot for your help

  15. #15
    manojcys is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    11
    I am trying to create a pivot chart with Reasons on X axis and Downtime on the Y axis. And Access is not allowing me to do it. Can you please explain me why?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 16
    Last Post: 07-25-2016, 07:42 AM
  2. Replies: 5
    Last Post: 07-15-2016, 07:21 AM
  3. ACCESS and SQL SERVER the reason
    By elico in forum SQL Server
    Replies: 10
    Last Post: 02-02-2016, 02:51 AM
  4. Replies: 2
    Last Post: 10-10-2015, 10:34 AM
  5. Replies: 3
    Last Post: 06-17-2015, 05:18 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