Organizing Table

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

--
Disclaimer: 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.

"Steve D" <SteveD.DeleteThis@discussions.microsoft.com> wrote in message
news:CA28C08F-53A5-4DEE-9948-2048BDCDFC3B@microsoft.com...
> 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?
>
>
> --
> Thank You,
> Steve
>
>
> "Jeff Boyce" wrote:
>
>> 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
>>
>> --
>> Disclaimer: 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.
>>
>> "Steve D" <SteveD.DeleteThis@discussions.microsoft.com> wrote in message
>> news:90EDF4E8-A28D-4962-A6D5-B0B5CC55FEED@microsoft.com...
>> >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?
>> > --
>> > Thank You,
>> > Steve
>> >
>> >
>> > "John W. Vinson" wrote:
>> >
>> >> On Wed, 28 Oct 2009 11:07:01 -0700, Steve D
>> >> <SteveD.DeleteThis@discussions.microsoft.com>
>> >> wrote:
>> >>
>> >> >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!
>> >> --
>> >>
>> >> John W. Vinson [MVP]
>> >> .
>> >>
>>
>>
>> .
>>

In message <nsnee5h1hn0vu5iiub3v88vfosnvd6k8bc.TakeThisOut@4ax.com>, John W. Vinson
<jvinson.TakeThisOut@STOP_SPAM.WysardOfInfo.com> writes
>On Tue, 27 Oct 2009 12:20:01 -0700, Steve D <SteveD.TakeThisOut@discussions.microsoft.com>
>wrote:
>
>>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?
>
>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."




--
Bernard Peek

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

--
Thank You,
Steve


"Jeff Boyce" wrote:

> 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
>
> --
> Disclaimer: 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.
>
> "Steve D" <SteveD.RemoveThis@discussions.microsoft.com> wrote in message
> news:CA28C08F-53A5-4DEE-9948-2048BDCDFC3B@microsoft.com...
> > 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?
> >
> >
> > --
> > Thank You,
> > Steve
> >
> >
> > "Jeff Boyce" wrote:
> >
> >> 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
> >>
> >> --
> >> Disclaimer: 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.
> >>
> >> "Steve D" <SteveD.RemoveThis@discussions.microsoft.com> wrote in message
> >> news:90EDF4E8-A28D-4962-A6D5-B0B5CC55FEED@microsoft.com...
> >> >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?
> >> > --
> >> > Thank You,
> >> > Steve
> >> >
> >> >
> >> > "John W. Vinson" wrote:
> >> >
> >> >> On Wed, 28 Oct 2009 11:07:01 -0700, Steve D
> >> >> <SteveD.RemoveThis@discussions.microsoft.com>
> >> >> wrote:
> >> >>
> >> >> >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!
> >> >> --
> >> >>
> >> >> John W. Vinson [MVP]
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>

On Fri, 30 Oct 2009 08:51:01 -0700, Steve D <SteveD.DeleteThis@discussions.microsoft.com>
wrote:

>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.

--

John 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.
--
Thank You,
Steve


"John W. Vinson" wrote:

> On Fri, 30 Oct 2009 08:51:01 -0700, Steve D <SteveD.RemoveThis@discussions.microsoft.com>
> wrote:
>
> >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.
>
> --
>
> John W. Vinson [MVP]
> .
>