software

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: www.excelforum.com/attachment.php?postid=4255 |
-------------------------------------------------------------------

--
matt4003
------------------------------------------------------------------------
matt4003's Profile: www.excelforum.com/member.php...foamp;userid=9635
View this thread: www.excelforum.com/showthread...hreadid=503756If you want to use Access, why post the question in an Excel group?

Go to Access and import the excel sheet into Access.

--
Regards,
Tom Ogilvy

quot;matt4003quot; gt; wrote in
message ...
gt;
gt; Hi All,
gt;
gt; I have been struggling with what should be a very simple task, at least
gt; I would think so. Here is what I have. My customer provides me a
gt; rolling 8 week forecast. It looks like this.
gt;
gt; Forecast Date|Part|Forecast Week|Quantity
gt; 1/1/2006|Part A|Week 1|100
gt; 1/1/2006|Part A|Week 2|200
gt; 1/1/2006|Part A|Week 3|150
gt; 1/1/2006|Part A|Week 4|300
gt; 1/1/2006|Part A|Week 5|50
gt; 1/1/2006|Part A|Week 6|120
gt; 1/1/2006|Part A|Week 7|100
gt; 1/1/2006|Part A|Week 8|400
gt; 1/8/2006|Part A|Week 2|300
gt; 1/8/2006|Part A|Week 3|150
gt; 1/8/2006|Part A|Week 4|700
gt; 1/8/2006|Part A|Week 5|550
gt; 1/8/2006|Part A|Week 6|420
gt; 1/8/2006|Part A|Week 7|200
gt; 1/8/2006|Part A|Week 8|800
gt; 1/8/2006|Part A|Week 9|100
gt;
gt; So you can see that the first week is always the closest to the current
gt; date, and every week a new forecast comes out. The problem is, there
gt; are over 900 rows for each 8 week rolling forecast (lots of parts and
gt; ship to locations).
gt;
gt; I think the best way to handle this is to use Access Database and query
gt; the data. But I can't seem to get the query right.
gt;
gt; I would like to be able to compare the closest week's forecast with the
gt; actual quantity bought. Which means I need to line up the calendar week
gt; of the forecast given date with the calendar week of the forecast week.
gt; I have attached an example.
gt;
gt; So you know, I have been using pivot tables for a year now, but the
gt; excel file is 50mb and I am out of rows...so access would be nice.
gt; Plus, once I can get this figured out, I will be able to link it to my
gt; consumption table and generate on the fly reports for management.
gt;
gt; Thanks for your help!!!
gt;
gt; Matt
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: example.jpg |
gt; |Download: www.excelforum.com/attachment.php?postid=4255 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; matt4003
gt; ------------------------------------------------------------------------
gt; matt4003's Profile:
www.excelforum.com/member.php...foamp;userid=9635
gt; View this thread: www.excelforum.com/showthread...hreadid=503756
gt;

Hi Tom,

Thanks for the reply.

I have already imported the data into Access. But the query and
analysis takes place in Excel. Access is just the storage, so it
really isn't an Access Question, it is an Excel based Mircosoft Query
question. Perhaps you're right, an Access Forum maybe better suited
for query questions. I will give it a try.

Regards,
Matt--
matt4003
------------------------------------------------------------------------
matt4003's Profile: www.excelforum.com/member.php...foamp;userid=9635
View this thread: www.excelforum.com/showthread...hreadid=503756Perhaps you're right that Access is the quot;rightquot; 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:
gt; Hi All,
gt;
gt; I have been struggling with what should be a very simple task, at least
gt; I would think so. Here is what I have. My customer provides me a
gt; rolling 8 week forecast. It looks like this.
gt;
gt; Forecast Date|Part|Forecast Week|Quantity
gt; 1/1/2006|Part A|Week 1|100
gt; 1/1/2006|Part A|Week 2|200
gt; 1/1/2006|Part A|Week 3|150
gt; 1/1/2006|Part A|Week 4|300
gt; 1/1/2006|Part A|Week 5|50
gt; 1/1/2006|Part A|Week 6|120
gt; 1/1/2006|Part A|Week 7|100
gt; 1/1/2006|Part A|Week 8|400
gt; 1/8/2006|Part A|Week 2|300
gt; 1/8/2006|Part A|Week 3|150
gt; 1/8/2006|Part A|Week 4|700
gt; 1/8/2006|Part A|Week 5|550
gt; 1/8/2006|Part A|Week 6|420
gt; 1/8/2006|Part A|Week 7|200
gt; 1/8/2006|Part A|Week 8|800
gt; 1/8/2006|Part A|Week 9|100
gt;
gt; So you can see that the first week is always the closest to the current
gt; date, and every week a new forecast comes out. The problem is, there
gt; are over 900 rows for each 8 week rolling forecast (lots of parts and
gt; ship to locations).
gt;
gt; I think the best way to handle this is to use Access Database and query
gt; the data. But I can't seem to get the query right.
gt;
gt; I would like to be able to compare the closest week's forecast with the
gt; actual quantity bought. Which means I need to line up the calendar week
gt; of the forecast given date with the calendar week of the forecast week.
gt; I have attached an example.
gt;
gt; So you know, I have been using pivot tables for a year now, but the
gt; excel file is 50mb and I am out of rows...so access would be nice.
gt; Plus, once I can get this figured out, I will be able to link it to my
gt; consumption table and generate on the fly reports for management.
gt;
gt; Thanks for your help!!!
gt;
gt; Matt
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: example.jpg |
gt; |Download: www.excelforum.com/attachment.php?postid=4255 |
gt; -------------------------------------------------------------------
gt;