Combine Multiple Weeks of Data

[Rustin788]

Hey,

I am trying to create some sort of automated way that I can view the sales history for a product so I can determine how much should be ordered. The lead time between when I place an order and the order arrives is at most 8 weeks so I need to know how much we are selling in that time period. What I want to do is have the sum for each 8 week period. For instance when looking at the results the first result would be the sum of Week 1-8, the second result would be the sum of week 2-9, and so forth. Currently the data is broken up by week using: Format(DatePart('ww',[Date]),"00").



Eventually I would like to be able to take those 8 week totals and have them automatically graphed in a form. I'm currently doing this all in excel and it takes a good day or two to getting everything graphed to be analyzed.

Thanks for any help.


***I will try out what ranman256 suggested and bump this thread with results.

[ranman256]

I use a report form...on it is the date range in 2 boxs
txtStartDate
txtEndDate

the query uses this as criteria. I can set it for 1 month, 1 day , etc...
in the query: where [Date] between forms!frmRpts!txtStartDate and forms!frmRpts!txtEndDate

If needed a 3rd txt box , txtRng, that translates the date range into a sort string.
like "Rng April2014"
or "Rng 140101 to 140105
(you could use weeks)

This way the data is not GROUPED BY the date, but summed by the txtRng box.