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
+-------------------------------------------------------------------+
|Filename: example.jpg |
|Download:
http://www.excelforum.com/attachment.php?postid=4255 |
+-------------------------------------------------------------------+
--
matt4003
------------------------------------------------------------------------
matt4003's Profile:
http://www.excelforum.com/member.php...fo&userid=9635
View this thread:
http://www.excelforum.com/showthread...hreadid=503756