Step Ladder 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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[matt4003]

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

[Bill Martin]

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