RE: Forecast Table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"Mr.LuckyMe" wrote:

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" wrote:

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.
--
Duane Hookom
Microsoft Access MVP


"Mr.LuckyMe" wrote:

Duane,

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

Thanks

"Duane Hookom" wrote:

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.
--
Duane Hookom
Microsoft Access MVP


"Mr.LuckyMe" wrote:

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

FcstKgID NOT ProdCustID

Thanks

"Mr.LuckyMe" wrote:

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,

"Sninkle" wrote:

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.

--
Carrie


"Mr.LuckyMe" wrote:

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
.

Relevant Pages

  • RE: Forecast Table
    ... "Duane Hookom" wrote: ... Microsoft Access MVP ... In my business case the forecast is kept at monthly level. ... The Primary Key for this table is ProdCustId ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Forecast Table
    ... Microsoft Access MVP ... The Primary Key for this table is ProdCustId ... If I understood correct and as my forecast is in Kilograms, ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Forecast Table
    ... In my business case the forecast is kept at monthly level. ... "Duane Hookom" wrote: ... Microsoft Access MVP ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Creating an DB for an Office Rota/Skills
    ... Microsoft Access MVP ... abpAbPID primary key autonumber ... "Duane Hookom" wrote: ... fields for skills and/or days then you are on your own. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Updateable Query
    ... Can you make the MPR field a unique or primary key? ... Microsoft Access MVP ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)