425,635 Members | 911 Online
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
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" 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 wrote in message news:... 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 wrote in message news:... 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.