Perhaps you're right that Access is the "right" tool to use, but for this small
a problem it's an unnecessary complication IMHO. I do a stock table lookup on a
table with 500-150,000 rows (multiple sheets) stored in a simple Excel XLS file
and it's plenty fast.

I started out doing a simple exhaustive search for the correct data and it would
find it within a few seconds. Since my data is also ordered however, I wrote a
bit of VBA to do a binary search of the data and it finds the correct record
virtually instantaneously now.

Access is overkill for as small a problem as you've described IMHO.

Bill
-----------------------
matt4003 wrote:
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 |
+-------------------------------------------------------------------+