Twelve Trailing Period Query Help

Hi guys,

So I'm looking to create a query that will generate a dynamic report that sums each customer sales over the most recent 12 month window. As a new month of sales is completed, I need this query to add the most recent completed month, while dropping the last month in the range.

I have a SerialMonth field added to my Date Table that allows me to use numeric values to signify which months I want in a given range. For example, November 2011 is SerialMonth 206, and December will be 207.

Obviously if I want just a snapshot look at a range of 12 months I can include the criteria "Between 194 and 206" in the SerialMonth field to give me what I want, however when December rolls around I will have to manually edit that criteria to say "Between 195 and 207".

Is there a way through criteria or SQL to give me the most recent 12 Months? Perhaps the TOP 12 included in my SQL statement?

Any help is greatly appreciated!!

I'd like to keep it within the Query if possible, as this Query will most likely also be utilized as a source for an Excel file at some point. The code you listed does give the right logic, however I need it to look for the highest SerialMonth values in order to give me the most recent 12 month window. I threw in the Max() function but it does not seem to do the trick.

Also, I know that throwing in the SerialMonth field into the query will give me a sum of sales for each month, when in the end I am looking for just a single sum of sales figure for each customer based on the criteria of the latest 12 months.

Thank you for the help and quick reply

Ahh, now here comes the tricky part. I used the term "Month" to dumb things down in looking for an answer to my question, however our sales data is logged in periods, as we run on 13 periods a year (4 weeks each). So, the NOW() function will not work because the periods can range between multiple months in some cases during the year.

For the current data tables I'm using, sales aren't broken down by individual transactions but rather summarized by period totals in order to minimize the number of records I am importing from our master sales database. So if I am understanding your question correctly, the source I am using does not have a field designating our weeks directly.

In the back end, our weeks would be broken out I assume as {1, 2, 3, 4} for Period 1, followed by {1, 2, 3, 4} for Period 2, etc rather than a sequential numbering of 1-52.

Let me know if I answered your question.