I have an Orders table that has the following fields:


What I want to do is take all orders within a time range starting at Date A and ending with Date B and put the Order Requirements into Buckets by week showing the total orderQty Like so:

Item Location 10/20/00 10/27/00 11/3/00 X
------ ---------- ---------- ----------- --------- -----
10 A 50 25 5 10
10 B 75 30 0 5
12 A 123 90 50 75

and so forth....

What I need to do is show 8-12 weeks (to be determined at a later date) of buckets and then an X bucket to show orders out in the future beyond the 8-12 week span. Does anybody have any suggestions on how to do this?

I will be using Access 97 as the front end for the program needing this information. I already have skeleton passthrough queries working to "simulate" the data. So ideally I'd like to accomplish this task with one passthrough SQL statement, or if need be use VBA to aid in the process.

Thank you...

I will be showing 8-12 buckets (weeks) of inidivuals