Group by rolling date ranges

[Alc]

I'm trying to create a query that will group results by date range.
I know how to do this by year, month, week or day. For example, for the table in question, this works:

 SELECT Format([CALENDAR_PROCESS_DATE],"yyyy") AS SalesPeriod, Count(tblARank.CALENDAR_PROCESS_DATE) AS NoOfSales
FROM tblARank
GROUP BY Format([CALENDAR_PROCESS_DATE],"yyyy");

However, I've been asked to show results in the following form (if run today):

October 20th 2015 - October 19th 2016 : Total Sales = 10
October 20th 2014 - October 19th 2015 : Total Sales = 25
October 20th 2013 - October 19th 2014 : Total Sales = 3
October 20th 2012 - October 19th 2015 : Total Sales = 13
and so on

(if run next month)

November 14th 2015 - November 193h 2016 - Total Sales = 11
November 14th 2014 - November 13th 2015 - Total Sales = 27
November 14th 2013 - November 13th 2014 - Total Sales = 4
November 14th 2012 - November 13th 2015 - Total Sales = 10
and so on

I can't work out how to group all dates in the last year from today's date together, all dates from the previous year from today's date as another, and so on? All the examples I've found have been for rolling totals but nothing for what I want.

 

[plog]

What this boils down to is constructing a string based on [CALENDAR_PROCESS_DATE]. Once you assign every date to the correct SalesPeriod the query groups them for you. So the key is determining the correct SalesPeriod for every [CALENDAR_PROCESS_DATE].

I would create a function in a module to do this. You would pass it [CALENDAR_PROCESS_DATE] and the function would return the appropriate string based on it and today's date.

Check out the functions on this page for reference:https://www.techonthenet.com/access/functions/date/dateadd.php

You will be using quite a few based on the methodology you employ.

 

[Alc]

I would create a function in a module to do this. You would pass it [CALENDAR_PROCESS_DATE] and the function would return the appropriate string based on it and today's date.

I completely about calling a function from within a query! Thanks so much.