A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Organizing Table



 
 
Thread Tools Display Modes
  #1  
Old October 27th, 2009, 08:20 PM posted to microsoft.public.access.tablesdbdesign
Steve D
external usenet poster
 
Posts: 48
Default Organizing 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?
--
Thank You,
Steve
  #2  
Old October 27th, 2009, 09:16 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Organizing Table

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

--
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" wrote in message
...
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?
--
Thank You,
Steve



  #3  
Old October 27th, 2009, 10:03 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Organizing Table

On Tue, 27 Oct 2009 12:20:01 -0700, Steve D
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]
  #4  
Old October 28th, 2009, 07:07 PM posted to microsoft.public.access.tablesdbdesign
Steve D
external usenet poster
 
Posts: 48
Default Organizing Table

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

  #5  
Old October 28th, 2009, 07:18 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Organizing Table

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" wrote in message
...
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

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



  #6  
Old October 28th, 2009, 10:07 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Organizing Table

On Wed, 28 Oct 2009 11:07:01 -0700, Steve D
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]
  #7  
Old October 29th, 2009, 03:58 PM posted to microsoft.public.access.tablesdbdesign
Steve D
external usenet poster
 
Posts: 48
Default Organizing Table

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

  #8  
Old October 29th, 2009, 05:39 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Organizing Table

On Thu, 29 Oct 2009 07:58:08 -0700, Steve D
wrote:

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?


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

John W. Vinson [MVP]
  #9  
Old October 29th, 2009, 06:08 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Organizing Table

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" wrote in message
...
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

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



  #10  
Old October 29th, 2009, 06:09 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default Organizing Table

In message , John W. Vinson
writes
On Tue, 27 Oct 2009 12:20:01 -0700, Steve D
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:24 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.