Organizing Table

On Tue, 27 Oct 2009 12:20:01 -0700, Steve D <SteveD RemoveThis @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 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.


--

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


"John W. Vinson" wrote:

> On Tue, 27 Oct 2009 12:20:01 -0700, Steve D <SteveD RemoveThis @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 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.
>
>
> --
>
> John W. Vinson [MVP]
> .
>

Steve

In a query, Year([YourDateTimeField]) gives you a year. Now make a crosstab
query using that.

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:2934661E-FE0C-48AE-AA95-0B0BD103DB3C@microsoft.com...
>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?
> --
> Thank You,
> Steve
>
>
> "John W. Vinson" wrote:
>
>> On Tue, 27 Oct 2009 12:20:01 -0700, Steve D
>> <SteveD DeleteThis @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 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.
>>
>>
>> --
>>
>> John W. Vinson [MVP]
>> .
>>

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]

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

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