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

If 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




"matt4003" wrote in
message ...

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

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: http://www.excelforum.com/member.php...fo&userid=9635
View this thread: http://www.excelforum.com/showthread...hreadid=503756

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 |
+-------------------------------------------------------------------+