Thread: Access: Grouped Sums
2014-03-26, 05:36 #1
- Join Date
- Mar 2014
- Thanked 0 Times in 0 Posts
Access: Grouped Sums
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.
Subscribe to our Windows Secrets Newsletter - It's Free!
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!
+ 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!
2014-03-27, 02:47 #2
Run a first query to Determine the totals:
SELECT Table1.Hora, Sum(Table1.Volume) AS SumOfVolume, Table1.Unidad
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
GROUP BY Query1.Unidad;