Help to group year and month data in query (1 Viewer)

[gojets1721]

Here's a sample of my data:

202210
202208
202210
202201
202204
202109
202104
202106
202303
202207
202209
202211
202202
202108

Its year and month. I want to use a query to group this data and count it. I want to group it in 12 month intervals (ie. 202205 - 202304)

Any suggestions? Not sure if its even possible

 

[Pat Hartman]

You have to convert the year/month into a value that represents your fiscal year. Create a function that returns just a year value. Then group on that year.

 

[The_Doc_Man]

It's always possible but not with what you showed us. Since you are using non-standard groups (i.e. don't start at year beginning), the EASIEST method is to add a second field to guide the grouping. This second field can be an expression. The one I show will give you the day of the first of the month for 4 months before the actual date. The DateSerial function converts year, month, and day to a formal date, and DateAdd allows you to add a selected number of time units to a date. Then the Year function extracts the year.

Play with it a bit. When you get the correct addition amount in the DateAdd, add the Year function and just group on the year.

Year( DateAdd( "m", -4, DateSerial( Left(Code, 4), Right( Code, 2 ), 1 ) ) )

 

[arnelgp]

see FinalQ query.
also see the 2 supporting queries.

 

[Pat Hartman]

I don't understand the solution in the database and I don't believe it is correct. So I took Doc's solution and turned it into a query for you. It produces the results I expected. Months <= 4 = prev year. Other months = Current year. You then group by this fiscal year to get your desired results.

SELECT data.ID, data.Field1, Year(DateAdd("m",-4,DateSerial(Left([Field1],4),Right([Field1],2),1))) AS Expr1
FROM data
ORDER BY data.Field1;

 

[arnelgp]

I want to use a query to group this data and count it. I want to group it in 12 month intervals (ie. 202205 - 202304)

I don't understand the solution in the database and I don't believe it is correct.

neither do i understand your suggestion. it does not even Count.

 

[561414]

Assuming your field is called yearmonth and the table is sometable, start with this query:

SELECT Left([yearmonth],4) AS YearPart, Right([yearmonth],2) AS MonthPart
FROM sometable

Call it qryParseMonths and use it for this:

SELECT
    IIf([MonthPart]<5,[YearPart]-1,[YearPart]) AS StartRange,
    IIf([MonthPart]>=5,[YearPart]+1,[YearPart]) AS EndRange
FROM
    qryParseMonths

Call it qryRanges and use it for this:

SELECT [StartRange] & "-" & [EndRange] AS Range, Count(*) AS CountOfRanges
FROM qryRanges
GROUP BY [StartRange] & "-" & [EndRange];

This is with auxiliary queries, which would make it look much simpler. But it could also look like this in one go:

SELECT [StartRange] & "-" & [EndRange] AS Range, Count(*) AS CountOfRanges
FROM (
    SELECT IIf([MonthPart]<5,[YearPart]-1,[YearPart]) AS StartRange, IIf([MonthPart]>=5,[YearPart]+1,[YearPart]) AS EndRange
    FROM (
        SELECT Left([yearmonth],4) AS YearPart, Right([yearmonth],2) AS MonthPart
        FROM sometable
    ) AS qryParseMonths
) AS qryRanges
GROUP BY [StartRange] & "-" & [EndRange];

If I understood correctly, all you need to do is divide the task into subqueries. One to extract the year part and the month part. Another to figure out the year range to which the month belongs. And another to count the occurrences. Check the attached. It should be giving you all of the year ranges. Which produces the following result for your sample data:

Range        CountOfRanges
2020-2021    1
2021-2022    6
2022-2023    7