Full Version: forecasting database is beyond me
UtterAccess Forums > Microsoft® Access > Access Forms
Salton
One for the Guru's.
I'm messing a round with a few idea's for a forecasting database, only i think i've under estimated the job at hand,
My two main issues are, 1), best table structure for data, ie, the forecast for each sku/product is in a table called tblForecasts, Only when in the data is shown in a form, how can i show only the forecast for a period i.e 2006 forecasts, or 2007 forcasts etc..
2) when in form view, showing only the forecast for 6 months, which will roll on each month i.e current month is MAY 2006 so i want to display the forecast for May 2006 to October 2006, then say in NOVEMBER, i want to display November 2006 to April 2007.
This is completely beyond me, but i'm sure there is someone would find this a doddle.
ScottGem
You need to define how you do the forecasting. What formula is used?
Salton
The actual forecast will be entered into the database, no calculation required at this point.
The database will be used to store sales history and forecasts, Each month the total forecast for each product will be exported for upload into another system.
just need to get round showing only the forecast for 6 months, i.e current month is MAY 2006 so i want to display the forecast for May 2006 to October 2006, then say in NOVEMBER, i want to display November 2006 to April 2007.
I will need to do the same for the sales history too
ScottGem
Ahh, Ok, but that depends on your table structure. You should have something like this:
blForecasts
ForecastID (PK autonumber)
ProductID (FK)
EffectiveDate
Forecast
You can either the first or last day of the month to identify the month. First day is easier. From there is easy to present a rolling 6 months report by setting criteria in the date field to
Between DateSerial(Year(Date()),Month(Date()),1) and DateSerial(Year(Date()),Month(Date())+6,0)
Salton
Hi Scott,
Thanks for the advice, but i dont think this is suitable for us as we currently have an excess of 400 products with a minimum of 20 customers to each product, and 12 months, this would mean a very large table of data, 8000+ records per month.
I was hoping to do something like:
tblForecast
ForecastID
YearID
January
February
March.....etc
Oalso need totry and minimise the amount of data the user has to key, you solution would mean the user would also have to put in a date, Is there a VB alternative that would show only 6 months based on my table structure ?
If there isn't, then i'll have to have a re-think
ScottGem
This is NOT a large table. Even though the number of records may be large, the record size is small. But more importantly, what you are proposed is not a normalized structure. Mine is. And, again, it makes your rolling query very easy to do.
Is for limiting keying, that's not hard. Assuming forecasts are being entered a year at a time, the user selects the year and the product in unbound controls. Then the date and product are automatically filled as each new record is created. Or you can simply add 12 records for the year in VBA and fillin in the amounts.
Salton
Thanks Scott, I will give it ago.
ScottGem
glad to assist
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.