24 rolling periods

[mccloud]

We have an ERP system that stores monthly qty's sold information by item. So
Nov 2009 is period 23. Jan 2010 will be period 01. Query will work from a
form where user will enter number of past periods to review and the item.
(.e.g. 6) entering 6 would need to add periods 22 thru 17. I thought about
building a table with (if period = 23 then add these periods together but it
would need to be huge to consider all possible combinations. Any one have a
better way?

 

[Duane Hookom]

What are you actually storing in your table, the period number or a date or
something else? Typically you can use DateAdd() and/or DateSerial() functions
to describe a rolling range of dates. Your criteria might look like:

Between DateSerial(Year(Date()), Month(Date())- 24,1) And Date()

The above would filter to the previous 24 months from the 1st day of the
starting month.

 

[mccloud]

It doesn't store any date information only sales data. So if I look up item
1234 it will have periods 1 thru 24 containing qty sold in each period. So I
need to query the system for the current period (Nov 2009 is 23) and decipher
which periods to include (add together).

 

[Duane Hookom]

It sounds like your table is horribly un-normalized where period information
is stored in field names. This would create 24 fields where a normalized
table structure would result in 24 records. If this is your situation, you
can normalize your data with a union query. You haven't provided any field or
table names so I can't provide the SQL of the union query.