Forecast Table

[Mr.LuckyMe]

I would like to receive your feedback about my table design for forecast. The
forecast is set at product customer level.

What do you think about the following table layout in Access 2003?

ProductID
CustomeID
January
February
....
....
....
November
December
Year

Thanks

 

[Sninkle]

Is it possible to get a little more information, is this your only table?
What is the Primary Key for this table? Also, you shouldn't list your months
like that. There should be a separate table for them that contains MonthID
and Month... in the Month field is where you would put the data January,
February, etc.

 

[Mr.LuckyMe]

Thank you for answer.

No, there are a lot more tables

The Primary Key for this table is ProdCustId

If I understood correct and as my forecast is in Kilograms, what you suggest
is:

ProdCustID
ProductID
CustomerID
MonthID
KG
Year

Thanks,

 

[Mr.LuckyMe]

Sorry, but the primary key for the previous mentioned table is:

FcstKgID NOT ProdCustID

Thanks

 

[Duane Hookom]

I would not create months as fields. Try:

ProdCustMthID
ProductID
CustomerID
ForecastMonthNumber
ForecastYear
ForecastKG

You could also combine the month and year into a single date field. This
would allow you to create forecasts by week if needed.

 

[Mr.LuckyMe]

Duane,

Could you please elaborate on how combining the month and year would be
usefull for creating weekly forecasts

Thanks

 

[Duane Hookom]

If you are storing the month and year in two fields in your table, you are
limiting your granularity to monthly forecasts. If your field is a date type,
you could enter values like:
10/5/2008
10/12/2008
10/19/2008
10/26/2008
This would allow you to create 4 forecast values in October 2008 and
generally 52 in a year.

You could still enter values like:
10/1/2008
11/1/2008
12/1/2008
if your forecasts are monthly.

 

[Mr.LuckyMe]

Duane,

Thank you for your answer.

In my business case the forecast is kept at monthly level. Do you think is
better to have month and year in two fields or just one?

Thanks,

 

[Duane Hookom]

I would use a single date field. If you want this represented as year and
month, you can always calculate these from the forecast date ie:
ForeCastMth: Month([ForecastDate])
ForeCastYr: Year([ForecastDate])

 

[Mr.LuckyMe]

Duane,

Thank you very much for your help

I would use a single date field. If you want this represented as year and
month, you can always calculate these from the forecast date ie:
ForeCastMth: Month([ForecastDate])
ForeCastYr: Year([ForecastDate])

--
Duane Hookom
Microsoft Access MVP

Duane,

Thank you for your answer.

In my business case the forecast is kept at monthly level. Do you think is
better to have month and year in two fields or just one?

Thanks,