Grouping by consecutive months

[Leex]

Batch	Date	CuttingsTaken
128880	18/12/2013	17150
128880	19/12/2013	32500
128880	07/01/2014	1500
128880	15/12/2014	53700
128880	17/12/2014	975
128880	07/01/2015	400
128880	16/12/2015	9750
128880	17/12/2015	3850
128880	18/12/2015	31100
128880	08/07/2016	31925
128880	12/07/2016	22525
128880	13/07/2016	41200
128880	27/07/2017	119875
128880	31/07/2018	77150
128880	01/08/2018	57750

Hello. Based on the data above could you please help me to find a way to group the data by 2+ consecutive months and sum the number of "CuttingsTaken". The reason why i need to group them is because we have two seasons: Winter (Dec-Feb) and summer (Jun-Sep) and I would like to have the totals per batch per season. I was trying to find the answers myself but with no avail. Please help me out.
Thank you in advance.

[orange]

Provide a sample of what you want based on your data.

[Leex]

From that data I would like to have:
Batch____ Season____ CuttingsTaken

128880____ winter 2014 ______51150 (18th, 19th Dec 2013 + 7th Jan 2014)
128880____ winter 2015____ 55075
128880____ winter 2016____ 44700
128880____ summer 2016____ 95650
128880____ summer 2017____ 119875
128880____ summer 2018____ 134900

[June7]

You need to calculate fields that assign season to each record as well as extract year.

Season: IIf(Month([Date]) BETWEEN 6 AND 8, "summer", "winter")
Yr: Year([Date])

Now use those fields to group/sort/filter records.


Date is a reserved word and should not use reserved words as names for anything.

[pbaldy]

I might use a table that had a season field ("winter 2014") and start/end date fields. Then use a non-equi join to that table to get the season. My gut is that June7's method will separate "winter 2014" into 2 records since the data falls into two different years, but I could be wrong.

[June7]

Good point. Will need to decide which year to assign Dec/Jan/Feb. If you want to keep Dec with the following Jan/Feb year:

Yr: Year([Date]) + IIf(Month([Date]) = 12, 1, 0)

Or build a table.

[accesstos]

Or

Season: IIF(MONTH([Date]+31)<6;"Winter";"Summer") & " " & YEAR([Date]+31)

[June7]

I kept the 2 calcs separate to allow easier filter/sort by year.

[Leex]

Thank you for suggestions guys. I will try this on my data set and let you know the results.
June7 you are right, to the winter season the year assigned is the following one (e.g. period Dec 2017 - Feb 2018 is going to be winter 2018).

[Leex]

All working well. Many thanks for your help.