SQL/VBA: How to group by a fiscal year starting fr

Question

I’m trying (using MS Access) to group some data by a fiscal year, which can be different to the calendar year.

If every fiscal year always started on the 1st of a given month, then the solution would be simple: one could simply use an if statement like this:

FY: IIf(Month([orderdate])<month([startFYofaCompany]);Year([orderdate])-1;Year([orderdate]))

in a select query like

SELECT statement here AS FY, Sum(something) AS SumOfSth
GROUP BY statement here;

However, the difficulty in my case is that a financial year of a given company can start from date other than 1st of a given month. I’ve looked for a solution, but everyone assumes otherwise.

I’d be grateful if someone had the same problem and could share a solution.

Edit: There is no pattern re. fiscal year – the database consists of multiple companies and should work whether some company has a FY starting on 2nd, 3rd, 15th, or any other day of any given month. The start date is stored in a separate field (in the pseudo-code above: startFYofaCompany).

Answer 1

If you have month + day to take into consideration, then obviously you will need the corresponding 2 fields in your table (NOT NULL DEFAULT 1).

Once this is added, the following will calculate the year: year(orderDate) - IIF(dateserial(year(orderDate), startFYofaCompany_month, startFYofaCompany_day) > orderDate, 1, 0)

Add this to the 2 places you have prepared in your query.

Answer 2

This is actually quite simple, and you don't need any additional table or fields.

The method is to offset the financial dates to match the calendar year. For example:

FinancialYearFirstMonth = 9
FinancialYearFirstDay = 2 

First, toalign to primo of the month:

DateMonth = DateAdd("d", 1 - FinancialYearFirstDay, SomeDate)

Next, expand to also align to primo of the year:

DateYearMonth = DateAdd("m", 1 - FinancialYearFirstMonth, DateAdd("d", 1 - FinancialYearFirstDay, SomeDate))

If the financial year is not lagging but leading the calendar year, add 12 months:

DateYearMonth = DateAdd("m", 1 - FinancialYearFirstMonth + 12, DateAdd("d", 1 - FinancialYearFirstDay, SomeDate))

Now you can have the financial year and month and (with some limitations for the ultimo date(s) of a month) day using the normal functions:

FinancialYear = Year(DateYearMonth)
FinancialMonth = Month(DateYearMonth)
FinancialDay = Day(DateYearMonth)

Of course, month names will not fit. If these are needed, use MonthName of the original date.