Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2014
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Access: Grouped Sums

    Hi,

    First post but here it goes.

    I have a data set as per the below which shows a unit (denoted by unidad) volumes and prices. This is broken down into hours however for one hour there can be more than one price and volume. I would like to be able to do the below

    First is to find out the maximum total hourly volume offered for each unit, ideally for each month. So for example for CILCOR this would be 163 and 64 for GUA7 in February

    Hora/ Fecha/ Pais/ Tipo Oferta/ Volume/ Price/ Ofertada (O)Casada (C)/ Unidad/ Type
    21 11-Feb-13 ES V 79 23 O CILCOR THERMAL
    21 11-Feb-13 ES V 79 40 C CILCOR THERMAL
    22 11-Feb-13 ES V 84 36 O CILCOR THERMAL
    22 11-Feb-13 ES V 79 23 C CILCOR THERMAL
    23 11-Feb-13 ES V 83 36 O CILCOR THERMAL
    23 11-Feb-13 ES V 79 29 C CILCOR THERMAL
    24 11-Feb-13 MI V 79 28 O CILCOR THERMAL
    24 11-Feb-13 MI V 79 39 C CILCOR THERMAL
    1 12-Feb-13 MI V 82 37 O CILCOR THERMAL
    21 11-Feb-13 ES V 14 24 O GUA7 THERMAL
    21 11-Feb-13 ES V 10 22 C GUA7 THERMAL
    22 11-Feb-13 ES V 12 34 O GUA7 THERMAL
    22 11-Feb-13 ES V 9 27 C GUA7 THERMAL
    23 11-Feb-13 ES V 23 22 O GUA7 THERMAL
    23 11-Feb-13 ES V 41 28 C GUA7 THERMAL
    24 11-Feb-13 MI V 8 25 O GUA7 THERMAL
    24 11-Feb-13 MI V 22 36 C GUA7 THERMAL
    1 12-Feb-13 MI V 24 39 O GUA7 THERMAL


    Hope its clear and thanks in advance
    Last edited by accessnovice; 2014-03-26 at 06:28.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    New Lounger Ensemble's Avatar
    Join Date
    Apr 2013
    Location
    Centurion
    Posts
    23
    Thanks
    9
    Thanked 1 Time in 1 Post
    Run a first query to Determine the totals:

    SELECT Table1.Hora, Sum(Table1.Volume) AS SumOfVolume, Table1.Unidad
    FROM Table1
    GROUP BY Table1.Hora, Table1.Unidad;

    Then run a second query on the first query to return the max:

    SELECT Max(Query1.SumOfVolume) AS MaxOfSumOfVolume, Query1.Unidad
    FROM Query1
    GROUP BY Query1.Unidad;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •