MicroSoft Access Monthly Sums

[stanlkathy]

I have created an extensive database of over 100K records extracting production volumes from TOW as daily. Each daily volume has an associated Equip_SK and Equip_Name for the timeframe 1/2008 to current.



Equip_SK EquipName Month Day Year Volume
180047 Well #1 1 1 2008 345
180047 Well #1 1 2 2008 678

How do I tell Access to calculate dailys to monthly and 12 month total by Equip_SK and EquipName? Thank you kindly,

Kathy Stanley

 

[xenou]

I think this query would group by Month, using the month end date:

SELECT
	Table1.Equip_SK, Table1.EquipName, 
	DateSerial(Table1.[Year], Table1.[Month] + 1, 0) As Period_Ending, 
	SUM(Table1.Volume) As SumOfVolume

FROM Table1

GROUP BY 
	Table1.Equip_SK, 
	Table1.EquipName, 
	DateSerial(Table1.[Year], Table1.[Month] + 1, 0)

And this would group by Year:

SELECT
	Table1.Equip_SK, 
	Table1.EquipName, 
	Table1.[Year], 
	SUM(Table1.Volume) As SumOfVolume

FROM Table1

GROUP BY 
	Table1.Equip_SK, 
	Table1.EquipName, 
	Table1.[Year]

Note: Next time you build a table, prefer not to use column names Year, Month, Day - these are the names of functions in Access. Putting them in brackets will help Access to catch on better.

 

[xenou]

Moving discussion this to this thread (which appears to be related):
http://www.mrexcel.com/forum/showthread.php?t=435293