Step Ladder Query Help

[matt4003]

Hi All,

I have been struggling with what should be a very simple task, at least I would think so. Here is what I have. My customer provides me a rolling 8 week forecast. It looks like this.

Forecast Date|Part|Forecast Week|Quantity
1/1/2006|Part A|Week 1|100
1/1/2006|Part A|Week 2|200
1/1/2006|Part A|Week 3|150
1/1/2006|Part A|Week 4|300
1/1/2006|Part A|Week 5|50
1/1/2006|Part A|Week 6|120
1/1/2006|Part A|Week 7|100
1/1/2006|Part A|Week 8|400
1/8/2006|Part A|Week 2|300
1/8/2006|Part A|Week 3|150
1/8/2006|Part A|Week 4|700
1/8/2006|Part A|Week 5|550
1/8/2006|Part A|Week 6|420
1/8/2006|Part A|Week 7|200
1/8/2006|Part A|Week 8|800
1/8/2006|Part A|Week 9|100

So you can see that the first week is always the closest to the current date, and every week a new forecast comes out. The problem is, there are over 900 rows for each 8 week rolling forecast (lots of parts and ship to locations).

I think the best way to handle this is to use Access Database and query the data. But I can't seem to get the query right.

I would like to be able to compare the closest week's forecast with the actual quantity bought. Which means I need to line up the calendar week of the forecast given date with the calendar week of the forecast week. I have attached an example.

So you know, I have been using pivot tables for a year now, but the excel file is 50mb and I am out of rows...so access would be nice. Plus, once I can get this figured out, I will be able to link it to my consumption table and generate on the fly reports for management.

Thanks for your help!!!

Matt

 

[SMatthews]

You could setup a cross reference table that contains the forecast week (1-52) and the corresponding calendar week. Then you can join the two tables on forecast week and query for results where the calendar weeks are equal.