Q: Designing table with net revenue..12 fields for months or 1?

[John]

I'm building a table to store revenue and units sold data for
products. What's the best design for storing the dates? Having 13
fields (year, Jan, Feb...Dec) or having 2 fields (year, month)?

It would seem that 13 fields is ok since that's a constant, but I'm
not sure.

-John

[Larry Linson]

Separate records identified by product, year, and month is consistent with
good relational database design practices, and far more flexible for work
you will want to do later.

Larry Linson
Microsoft Access MVP

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I'm building a table to store revenue and units sold data for
products. What's the best design for storing the dates? Having 13
fields (year, Jan, Feb...Dec) or having 2 fields (year, month)?

It would seem that 13 fields is ok since that's a constant, but I'm
not sure.

-John

[Bob Quintal]

so*********@hotmail.com (John) wrote in
news:90**************************@posting.google.c om:

I'm building a table to store revenue and units sold data for
products. What's the best design for storing the dates? Having
13 fields (year, Jan, Feb...Dec) or having 2 fields (year,
month)?

It would seem that 13 fields is ok since that's a constant,
but I'm not sure.

-John

The second method will allow you to easily build queries that do
things like compare the last 6 months of last year to the first 6
months of this year. If you later decide you want to see 9 and 3,
it's a simple task to change the one parameter. With the 13 fields,
it's a whole new, complicated set of calculations.

Go with the 2 fields design.

Bob

[John]

Thanks for your advice Bob and Larry. Ironically, I had originally
designed the table with the fewer fields option...but alas, delerium
set in.

Bob Quintal <bq******@generation.net> wrote in message news:<b9******************************@news.terane ws.com>...

so*********@hotmail.com (John) wrote in
news:90**************************@posting.google.c om:

I'm building a table to store revenue and units sold data for
products. What's the best design for storing the dates? Having
13 fields (year, Jan, Feb...Dec) or having 2 fields (year,
month)?

It would seem that 13 fields is ok since that's a constant,
but I'm not sure.

-John

The second method will allow you to easily build queries that do
things like compare the last 6 months of last year to the first 6
months of this year. If you later decide you want to see 9 and 3,
it's a simple task to change the one parameter. With the 13 fields,
it's a whole new, complicated set of calculations.

Go with the 2 fields design.

Bob

[John]

One last question;

I have two fields, year and month. Should these be integer data types
or date/time? If date/time, how would I create an input mask to make
relevant limits?

Thanks...

Bob Quintal <bq******@generation.net> wrote in message news:<b9******************************@news.terane ws.com>...

so*********@hotmail.com (John) wrote in
news:90**************************@posting.google.c om:

I'm building a table to store revenue and units sold data for
products. What's the best design for storing the dates? Having
13 fields (year, Jan, Feb...Dec) or having 2 fields (year,
month)?

It would seem that 13 fields is ok since that's a constant,
but I'm not sure.

-John

The second method will allow you to easily build queries that do
things like compare the last 6 months of last year to the first 6
months of this year. If you later decide you want to see 9 and 3,
it's a simple task to change the one parameter. With the 13 fields,
it's a whole new, complicated set of calculations.

Go with the 2 fields design.

Bob

[Mrs Howl]

so*********@hotmail.com (John) wrote in message news:<90**************************@posting.google. com>...

One last question;

I have two fields, year and month. Should these be integer data types
or date/time? If date/time, how would I create an input mask to make
relevant limits?

Thanks...

I would make year an integer and month a byte. For month, you could
have a validation rule of "between 1 and 12".