The list of months is necessary otherwise the query lists the months in alphabetical order. (Thanks, Hans )TRANSFORM Sum(tblOutgoing.Cost) AS SumOfCost
SELECT Year([PurchaseDate]) AS [Year]
FROM tblCategory INNER JOIN tblOutgoing ON tblCategory.CategoryID = tblOutgoing.Category
GROUP BY Year([PurchaseDate])
PIVOT MonthName(Month([PurchaseDate]),True) In (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec);
We would now like to bring this in line with the UK tax year. This starts on the Wednesday of the first week in March. So, today is the last day of the UK tax year 2021-22. This means that the cost summary for this April will have to omit this year's few days of April 2022 (1st, 2nd, 3rd and 4th) which are in the tax year 2021-22 but the end of the tax year 2022-23 will have a few days of April to cope with. We could either list costs for a 4 day April 2023 or add these days into the March 2023 total. (but we don't know how to do any of this )
To add complexity, the start date of the UK tax year varies each year.
Can this be done, please?