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  

"groups" in table and text field size



 
 
Thread Tools Display Modes
  #1  
Old November 21st, 2004, 10:47 AM
Mimi
external usenet poster
 
Posts: n/a
Default "groups" in table and text field size

I created several tables differentiated by YEAR. They're pretty much the
exact same fields (survey questions), just different years (there's no
relationship). Within these YEARS, there are questions for different DAYS.

So with each ID, I added fields for all the questions for all 5 days. Now it
turns out that the ID's are grouped by DAY, not YEAR...meaning that a person
who answered the survey on DAY 4 did not also answer the survey on DAY
1,2,3,or5 (since the surveys were given different days and are anonymous).

So is it wrong to keep it the way it is on my YEAR table? The way I
separated it on my form is by creating a form for every year and using tabs
to separate the days. SO this means I would have to keep all the days on the
same table, right?

Any suggestions would be great on how to 'group' the days (on the table).
Thanks.

PS. I'm also having issues with the text field size. I originally used the
default 50 to created a whole bunch of text fields. But now, I need them to
be about 150 characters. I changed my default on my options, but this did not
go back and change my existing fields...kept them at 50. Is there any way to
change them all w/out changing the size one by one?
  #2  
Old November 21st, 2004, 06:43 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"=?Utf-8?B?TWltaQ==?=" wrote in
:

So is it wrong to keep it the way it is on my YEAR table?


yes: almost certainly what would have been better would be one long table
structured something like

YearNum DayNum Answer
1999 009 Something
1999 010 Something else
2000 009 A new millenium

and so on. That way you could filter on year and days and group and sort as
well as you like. And Access will much _much_ faster and more efficiently
with a "long, narrow" table than a short fat one.


PS. I'm also having issues with the text field size. I originally used


... but this did not go back and change my existing fields


No: you will have to redesign every field in every table that you want
changed.

Hope that helps


Tim F

  #3  
Old November 21st, 2004, 08:05 PM
Mimi
external usenet poster
 
Posts: n/a
Default

Also, under Answer/Question, would I have separate fields under Year and Day
for Q1, Q2, Q3a, etc?

"Tim Ferguson" wrote:

"=?Utf-8?B?TWltaQ==?=" wrote in
:

So is it wrong to keep it the way it is on my YEAR table?


yes: almost certainly what would have been better would be one long table
structured something like

YearNum DayNum Answer
1999 009 Something
1999 010 Something else
2000 009 A new millenium

and so on. That way you could filter on year and days and group and sort as
well as you like. And Access will much _much_ faster and more efficiently
with a "long, narrow" table than a short fat one.


PS. I'm also having issues with the text field size. I originally used


... but this did not go back and change my existing fields


No: you will have to redesign every field in every table that you want
changed.

Hope that helps


Tim F


  #4  
Old November 21st, 2004, 08:05 PM
Mimi
external usenet poster
 
Posts: n/a
Default

I understand YearNum and DayNum, but how would I have some questions about
Answer. Some of my Answers are text fields and others are an Option Box so
need 1-6. Can I define it to be text and Number, or I assume numbers are
allowed in Text boxes. But is this the best way?

Thanks again.

"Tim Ferguson" wrote:

"=?Utf-8?B?TWltaQ==?=" wrote in
:

So is it wrong to keep it the way it is on my YEAR table?


yes: almost certainly what would have been better would be one long table
structured something like

YearNum DayNum Answer
1999 009 Something
1999 010 Something else
2000 009 A new millenium

and so on. That way you could filter on year and days and group and sort as
well as you like. And Access will much _much_ faster and more efficiently
with a "long, narrow" table than a short fat one.


PS. I'm also having issues with the text field size. I originally used


... but this did not go back and change my existing fields


No: you will have to redesign every field in every table that you want
changed.

Hope that helps


Tim F


  #5  
Old November 21st, 2004, 08:06 PM
Mimi
external usenet poster
 
Posts: n/a
Default

Thanks Tim. I definitely needed some help. Now I need to figure out where to
go from here w/out completely starting over. I'm new at this, so really
appreciate your advice. I should've planned better! Thanks again.

"Tim Ferguson" wrote:

"=?Utf-8?B?TWltaQ==?=" wrote in
:

So is it wrong to keep it the way it is on my YEAR table?


yes: almost certainly what would have been better would be one long table
structured something like

YearNum DayNum Answer
1999 009 Something
1999 010 Something else
2000 009 A new millenium

and so on. That way you could filter on year and days and group and sort as
well as you like. And Access will much _much_ faster and more efficiently
with a "long, narrow" table than a short fat one.


PS. I'm also having issues with the text field size. I originally used


... but this did not go back and change my existing fields


No: you will have to redesign every field in every table that you want
changed.

Hope that helps


Tim F


  #6  
Old November 22nd, 2004, 06:53 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"=?Utf-8?B?TWltaQ==?=" wrote in
:

Now I need to figure out where to
go from here w/out completely starting over.


I am not sure that this is not the best place to start... :-)

Taking things from the top, I see that you have entities called

Years
Days
IDs
Questions
Answers

but I am not clear exactly how they all interact. Some things are
(probably) easy -- each Day belongs to exactly one Year; there is at most
one Answer for a given Question on a given Day by a particular ID; and so
on. My guess is that you are looking at tables something like this

Days(*YearNum, *DayNum, etc) to track days when surveys were taken

Questions(*QuestNum, TextOfQuestion, CorrectAnswer, AnswerType) which is
fairly obvious, except that if you make CorrectAnswer a text field,
then you can use it to store a numeric answer and set AnswerType to
a code that specifies "number", if you see what I mean. I would also
have urged you to keep all questions to a consistent answer type, but
you can't have everything!

Subjects(*SubjectID, FName, LName, Agegroup, Gender, etc)

Responses(*YearNum, *DayNum, *QuestNum, *SubjectID, GivenAnswer) This is
the table that holds one record for each question from each subject on
each day. You can group by year, or by day; do totals for each Subject,
and by suitable joins you can analyse by gender or agegroup etc etc.

One short cut would be to look at the ready-made At Your Survey database
from our very own Duane Hookom: check out
http://www.rogersaccesslibrary.com/OtherLibraries.asp

Even if it does not fit your needs exactly, you can see how to build a
similar application that does.

Hope that helps


Tim F


  #7  
Old November 24th, 2004, 06:17 AM
Mimi
external usenet poster
 
Posts: n/a
Default

I thought I had it figured out. but i definitely don't. Yes, all answers are
unique to the certain day and certain year. But the way it's set up, all
Q1's, Q2's are the same for all days and all years! Tha'ts not good. So maybe
I"m not understanding your syntax of how to keep them unique in the same
table.

Days(*YearNum, *DayNum, etc)


Will the * keep them unique? I need help!

THanks again.
"Tim Ferguson" wrote:

"=?Utf-8?B?TWltaQ==?=" wrote in
:

Now I need to figure out where to
go from here w/out completely starting over.


I am not sure that this is not the best place to start... :-)

Taking things from the top, I see that you have entities called

Years
Days
IDs
Questions
Answers

but I am not clear exactly how they all interact. Some things are
(probably) easy -- each Day belongs to exactly one Year; there is at most
one Answer for a given Question on a given Day by a particular ID; and so
on. My guess is that you are looking at tables something like this

Days(*YearNum, *DayNum, etc) to track days when surveys were taken

Questions(*QuestNum, TextOfQuestion, CorrectAnswer, AnswerType) which is
fairly obvious, except that if you make CorrectAnswer a text field,
then you can use it to store a numeric answer and set AnswerType to
a code that specifies "number", if you see what I mean. I would also
have urged you to keep all questions to a consistent answer type, but
you can't have everything!

Subjects(*SubjectID, FName, LName, Agegroup, Gender, etc)

Responses(*YearNum, *DayNum, *QuestNum, *SubjectID, GivenAnswer) This is
the table that holds one record for each question from each subject on
each day. You can group by year, or by day; do totals for each Subject,
and by suitable joins you can analyse by gender or agegroup etc etc.

One short cut would be to look at the ready-made At Your Survey database
from our very own Duane Hookom: check out
http://www.rogersaccesslibrary.com/OtherLibraries.asp

Even if it does not fit your needs exactly, you can see how to build a
similar application that does.

Hope that helps


Tim F



  #8  
Old November 24th, 2004, 06:29 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"=?Utf-8?B?TWltaQ==?=" wrote in
:

But the way it's set up, all
Q1's, Q2's are the same for all days and all years! Tha'ts not good.
So maybe I"m not understanding your syntax of how to keep them unique
in the same table.

Days(*YearNum, *DayNum, etc)


Will the * keep them unique? I need help!


The asterisk is the Primary Key -- in other words you can have only one Day
record for 2004/298, similarly you can have only one Responses record for
Year=2004
Day=298
QuestNum = 37
SubjectID = 10994

and so there is only one AnswerGiven by that subject to that question on
that day. There is, of course, another answer for SubjectID=10995 on the
same day, and another one again for QuestNum=38 by the same subject on the
same day. And so on: one record for each answer by each subject on each
day. That is what I mean by long thin tables rather than wide flat ones!

If you look at the At Your Survey database, it will show you how it works.

The good news: once you get your head around this, you know (practically)
all there is to know about Relational Design Theory!

Best wishes


Tim F





 




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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Duplicate data Rob Green Database Design 3 November 7th, 2004 04:08 AM
Here's a shocker Mike Labosh General Discussion 2 October 26th, 2004 05:04 PM
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. HDW Database Design 3 October 16th, 2004 03:42 AM
Must # of fields in the 2 tables in an Append Query be equal? CreativeImages Running & Setting Up Queries 3 October 1st, 2004 05:16 PM
Text (not headings) that needs to appear in TOC Linda Formatting Long Documents 2 June 4th, 2004 08:03 PM


All times are GMT +1. The time now is 07:15 PM.


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