 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / October 2009Organizing Table
Good Afternoon, I have a very basic question regarding the organization of my table. I will have monthly data that will be entered and it will be for various years. Is it better to use one field called month and do a crosstab query or should I have 12 fields (one for each month)? My thought is that it is cleaner if each month has its own record but then I will have 12 times the number of records. Thoughts? Best Practices?  SignatureThank You, Steve Steve If you have one field for each month, you will have ... a spreadsheet! If you are unfamiliar with the terms "normalization" and "relational database design", plan to brush up on these BEFORE you design your table structure. In fact, plan to brush up anyway ... you will need to evaluate the responses you get here in light of their suitability to your specific situation, considering relational database design. Good luck! Regards Jeff Boyce Microsoft Access MVP SignatureDisclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. > Good Afternoon, > I have a very basic question regarding the organization of my table. I [quoted text clipped - 5 lines] > each month has its own record but then I will have 12 times the number of > records. Thoughts? Best Practices? >Good Afternoon, > I have a very basic question regarding the organization of my table. I [quoted text clipped - 3 lines] >each month has its own record but then I will have 12 times the number of >records. Thoughts? Best Practices? Absolutely and unquestionably, one record per month. "Fields are expensive, records are cheap"! In fact, I would NOT use a "month" field, either monthname or month number; instead use a Date/Time field. You must enter a complete date, but October 2009 data can be entered with a date field of #10/1/2009#, and so on; Access' date handling is pretty good and this will let you slice and dice the data chronologically with a lot of flexibility. SignatureJohn W. Vinson [MVP] I am ultimately going to want to use a crosstab query that will show multiple years in the rows. Do you still suggest using date/time? SignatureThank You, Steve > >Good Afternoon, > > I have a very basic question regarding the organization of my table. I [quoted text clipped - 12 lines] > date handling is pretty good and this will let you slice and dice the data > chronologically with a lot of flexibility. Steve In a query, Year([YourDateTimeField]) gives you a year. Now make a crosstab query using that. Regards Jeff Boyce Microsoft Access MVP SignatureDisclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. >I am ultimately going to want to use a crosstab query that will show >multiple [quoted text clipped - 30 lines] >> data >> chronologically with a lot of flexibility. >I am ultimately going to want to use a crosstab query that will show multiple >years in the rows. Do you still suggest using date/time? Even more strongly, yes. You can crosstab by year using Year([datefield]) as the Column Head - or by year and month using Format([datefield], "yyyy-mm"), or by week using DatePart([datefield], "ww"). Like I said, great flexibility! SignatureJohn W. Vinson [MVP] I get what you are saying but I would like to understand better how the database works. Why is it better to use the full date and have many records than to use one table with 12 months and one table with the years? I felt like I needed the Year table. I am using the database to manage the budget and forecast cycles, so I have a table called tblCycle and I wanted to be able to relate that with a year so I would be able to pull the 2007 budget, etc.., but now I think I could just query using the month field or Year([dtmMonth)]. Am I on the right path? SignatureThank You, Steve > >I am ultimately going to want to use a crosstab query that will show multiple > >years in the rows. Do you still suggest using date/time? [quoted text clipped - 4 lines] > year and month using Format([datefield], "yyyy-mm"), or by week using > DatePart([datefield], "ww"). Like I said, great flexibility! >I get what you are saying but I would like to understand better how the >database works. Why is it better to use the full date and have many records [quoted text clipped - 6 lines] > >Am I on the right path? Yes... but you need to keep going. You seem to have a bias that you must have a separate table for everything. You don't! With proper indexing and proper query construction, you can *VERY EASILY* and efficiently construct a query to extract any calendar year, or fiscal year, or month, or any other time interval from your table, using just a Date/Time field. You certainly do NOT need a table of months or a table of years to do this. SignatureJohn W. Vinson [MVP] Steve I'm with John on this... Adding another field to handle another month means you'll always be maintaining the application, updating the table structure, the queries, the forms, the reports, etc. Moreover, since Access is optimized for well-normalized data, if you try to feed it 'sheet data, both you and Access will have to work overtime to come up with (unnecessary) work-arounds. I suspect that folks with experience using Excel before considering Access have a BIGGER task coming up to speed, as they have to unlearn some of what they've been doing if they want to make good/efficient/effective use of the tool. Best of luck! Regards Jeff Boyce Microsoft Access MVP SignatureDisclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. >I get what you are saying but I would like to understand better how the > database works. Why is it better to use the full date and have many [quoted text clipped - 23 lines] >> year and month using Format([datefield], "yyyy-mm"), or by week using >> DatePart([datefield], "ww"). Like I said, great flexibility! First of all...Thank you for all of your help. Now more questions: I am using the database to maintain the budget and forecast processes, each of which has many versions. I created 3 tables, tblYear (2010, 2009, 2008...), tblCycle(Budget, 1qForecast, 2qForecast, Final...) and tblVersion (10/1/2009 12:00 PM, 10/15/2009 12:30 PM). If I understand you correctly I should have this as one table? SignatureThank You, Steve > Steve > [quoted text clipped - 49 lines] > > . Steve I'm having trouble envisioning how 2010, 2009 and 2008 is a table? Can you provide an example of what data you are storing in that table? Regards Jeff Boyce Microsoft Access MVP SignatureDisclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. > First of all...Thank you for all of your help. > [quoted text clipped - 70 lines] >> >> . Jeff, I have been using Hyperion Enterprise at work and from a user perspective it looks to me that it has several individual tables that you choose from to retrieve data (I'm sure the programming is much more sophisticated but I am going with what I see) So each data value has a corresponding Period, Entity, Frequency, Category, etc... So I am trying to re-create something that works the same way for my call center volume planning model. Each of my values will have a Year (2008, 2009...), Month (Jan, Feb, Mar...), Cycle (Budget, Q1 Fcst, Q2 Fcst...), Version (10/1/09, 10/15/09...) and Call Type (Billing, General Info, Quality of Service...) at the end of the day I have one table with multiple lookups that looks like: Year, Month, Cycle, Version, Call Type, # of Calls 2009, Jan, Q3 Fcst, 10/1/09, Billing, 25000 2009, Feb, Q3 Fcst, 10/1/09, Billing, 24000 2009, Mar, Q3 Fcst, 10/1/09, Billing, 23000 2009, Jan, Q3 Fcst, 10/15/09, Billing, 26000 2009, Feb, Q3 Fcst, 10/15/09, Billing, 25000 2009, Mar, Q3 Fcst, 10/15/09, Billing, 24000 2009, Jan, Budget, 10/15/09, Billing, 26500 2009, Feb, Budget, 10/15/09, Billing, 25500 2009, Mar, Budget, 10/15/09, Billing, 24500 SignatureThank You, Steve > Steve > [quoted text clipped - 82 lines] > > . >I have been using Hyperion Enterprise at work and from a user perspective it >looks to me that it has several individual tables that you choose from to >retrieve data Access is NOT a flawed implementation of Hyperion Enterprise. Hyperion Enterprise is likewise NOT a flawed implementation of Access. Trying to apply the logic and design conventions of one software package to another will be an exercise in frustration! If you're going to use Access, I'd really recommend that you use it on its own terms, as it's designed to be used. This would NOT include creating a new table every year, or every quarter. The table you post looks like one piece of a reasonable structure, but if you're still thinking of "a 2008 table" or a "February table" you're on the wrong track. SignatureJohn W. Vinson [MVP] My intention was never to build a seperate table for each year, just a table that contained all of the years (tblYear), and one that contained all of the months (tblMonth - 12 records - no more, no less). The table I showed as an example would reference all of those individual tables so if I needed to change a version name or add a year I would do it in the respective tables, tblVersion or tblYear, and the changes would be made globally. Sorry if that wasn't clear. SignatureThank You, Steve > >I have been using Hyperion Enterprise at work and from a user perspective it > >looks to me that it has several individual tables that you choose from to [quoted text clipped - 12 lines] > you're still thinking of "a 2008 table" or a "February table" you're on the > wrong track. >>Good Afternoon, >> I have a very basic question regarding the organization of my table. I [quoted text clipped - 6 lines] >Absolutely and unquestionably, one record per month. "Fields are expensive, >records are cheap"! In this particular case there's an argument for considering using columns. The usual reason for preferring rows is that someone might invent a new <foo> which would require a new column in a table. I believe that it's unlikely that anyone is going to add a new month to the calendar any time soon. Having said that it's not unknown for company accounts to be split into thirteen or sometimes more "months." An auditor of my acquaintance wasn't surprised to find an extra month in a set of accounts. He was surprised to find that we had given it a name, "Augustus." SignatureBernard Peek |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.