By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/a
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

Nov 12 '05 #5

P: n/a
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".
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.