24 rolling periods how to calculate.

[mccloud]

Our ERP system stores sales history in 24 rolling periods. I have a form
that prompts the user for number of periods to review and an item. I'm
trying to build a query that looks up current period (23) Nov 2009, and
figure out which periods to review. As an example user may enter 6 for the
number of periods. So I would need to get data from periods 22 thru 17.
Because its a rolling period model and periods vary based on user input I'm
not sure how to handle. I thought about build a table that references which
periods to review but thought there might be an user way.

Thanks in advance for your replies. Also I'm sorry if this is a repeat from
an earlier post but I got an error.

 

[KARL DEWEY]

You did not say but I assume that each period is a calendar month. You did
not say but it seems you want period group to start from previous month
backwards through the number entered.

[YourPeriod] Between (DateDiff("m",Date(),#11/1/2009#)+23) And
(DateDiff("m",Date(),#11/1/2009#)+23)-[Enter number of periods]

 

[KARL DEWEY]

Error - use this ---
You did not say but I assume that each period is a calendar month. You did
not say but it seems you want period group to start from previous month
backwards through the number entered.

[YourPeriod] Between (DateDiff("m",Date(),#11/1/2009#)+22) And
(DateDiff("m",Date(),#11/1/2009#)+22)-[Enter number of periods]

 

[KARL DEWEY]

So Nov 2009 is period 23. Jan 2010 will be period 01.
I just read your other post and it does not match this one.

How can Jan 2010 be period 01 if Nov 09 is period 23?

 

[mccloud]

Jan 2010 is the start of the new cycle. So Dec 2009 is 24 and Dec 2010 will
be 12. So my code needs to look up current period and figure out which
periods to include.

 

[KARL DEWEY]

What I posted (with 22) wil work until you start over.

When you start over you will have two series with the same numbers so it
won't work unless maybe you are going to renumber the old series.

 

[mccloud]

Not sure how to do this but I would like to use the current period and
current month as a starting point. So Dec 2009 and current period = 24.
Create a union query that would use this info and assign a month and year to
each period.
period 23 = 112009, period 22 = 102009 ect...

Any help would be great!