Table design help!

[Hannah]

I'm a bit of an access novice (training on basics, searching for help on the
rest) building a database for a research project. I've tried having a wee
look for this question but I can't spot it anywhere (probably because it's
obvious to everyone else).

I'm designing my tables and what to put in them and I've hit on something
I'm not very sure about. For some questions there's a yes or no question
(e.g. "do you eat cheese?) and a follow up if they say yes, theres an "if
yes, which of these types..." follow up question. I'm not sure whether I need
to structure these into two different tables or whether I can use one table
for both? I was planning to use tick boxes for these but if there's a better
way, please let me know! Thanks very much for help in advance - I don't know
where I'd be without these groups (well I do, somewhere not very good!)

 

[Hannah]

P.S. what I totally forgot to say was whether it's okay to have a table with
just one field (e.g. just "do you eat cheese?" tick box)? Want to make sure
I'm doing this right from the beginning!

 

[Fred]

Hello Hanna,

I noticed that nobody answered.

To be honest, your post indicates confustion on even the fundamentals, and a
responding would be even tougher than starting at zero because they would
have to start by undoing you wanting to talk about the cart before the horse,
as your post seeks to do. And you've given zero informaiton about the horse
leaving nowhere to start.

Well, if you survived that paragraph, you need to start by defining for us
(if not for yourself) the nature of the real-world process that you want to
databse, and decide and determine what the "entities" aree that you want to
database.

I'm guessing that one of these entities will be "questions" beyond that
you gave us nothin' to go on.

- Maybe your entities include people (who are taking the survey)

- Depending on what the nature of potential answers are (just yes/no?
Multiple choice from a set list? Free-form writing in the blank? anoth
entity might be potential (allowable) answers on multiple choice questions

- Maybe your entities include surveys (which are groups of questions) if
there are multiple surveys

- Maybe your entities include instancs of answers to questions

You'll have to tell us.


The answer to your "if-then" question will be dependent on your structure,
which you are going to build upon your "entities" answer. Also on you
defining whether or not a particular answer leads to just one "next
question" vs. a group of them.


I know that that makes your "next step" pretty complicated right now, but I
think that such is inevitable, and hopefully this helped a bit.

 

[Jeff Boyce]

In addition to Fred's observations, consider whether you need to reinvent
the wheel.

If this is a survey/questionnaire, perhaps work already done by Duane H.
could get you started:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

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

 

[Hannah]

Thanks for your in put and apologies for my lousy explanation! You are right
Fred in that I don't feel particularly clear on the fundamentals of what I'm
doing really at all. And thanks Jeff for suggesting Duane's database, I've
downloaded a copy and tried to look closely at what he's done. My main
problem is that I've come to this project halfway through and my Access
training has been limited.

During training, we set up the tables for the first questionnaire (and
unfortunately, this doesn't resemble Duane's database much) but I'm now
trying to do the second one by myself and I just felt a wee bit confused
about what exactly goes where.

My entities include multiple surveys, each with different questions and
multiple answers, and details about the organisation filling in the survey.
Answers can be in a variety of forms (yes/no, write in the blank, multiple
choice and multiple choice where they can choose 'other' and specify).

My structure at the moment is (and I apologise now for the dreadful
explanation): a table with the name of each questionnaire and notes about it.
For each questionnaire, this links to a table with the contact details and
country of respondent. This links to a table for each of the overarching
questions and then I've grouped the smaller questions around these by theme.
I know this is nothing like Duane's but it's what we were advised to do in
training!

What I was wondering about in my earlier post is if it's okay to have a one
field table. I've got a smaller question (yes/no) and I wasn't sure whether
the question that leads from it ("if yes,...) should be in a separate table.
I thought instinctively yes but my training lady put the fear into me about
one field tables and I tried reading up on it on the internet but I just seem
to tie myself more in knots.

I appreciate I'm probably beyond help at the moment (and doubly bad at
explaining my problems!) but anything you can offer would be really
appreciated. Thanks again for your help already, it's really given me more to
think about.

 

[Fred]

Hello Hannah,

First, answering your specific question, one-field tables are seldom useful
and seldom "the answer" for what you are trying to do. The one notable
exception is to to create a "pick list" for dropdowns etc.

One thing that you didn't tell us, but I'm presuming is the case.....will
this application be where they actually participate in the survey? I.E. the
person sits at the computer, and looks at the questions and answers them
through the Access application that you are going to write?


- - - -

That said, your mission as you describe it is very complex. For you to do
it yourself as described would probably require sever years of teaching
rather than a post in this group.

I think that you are going to have to either simplify it, or else have
someone more expert work on it for you. (like pay a developer or something.)
But don't use anyone who solicits for business in these forums.


Just being honest, which is hopefully helpful.

 

[Hannah]

Hello Fred,
Your honesty is very much appreciated - every bit of advice is really useful
and I dread to think where I'd be without the amazingly helpful people in
this group.

In response to your question, this application won't be where the person
participates in the survey - they've already filled it in on paper. It will
be mainly me that inputs the data but I'm hoping that I can make the
application easy enough to use so that other people could use it too
(ambitious as this seems given my access skills!) I think this is part of the
problem in that the people who designed the questionnaire didn't make it very
clear or give much thought to how they were going to analyze the data at all!

The other key bit of information I should have written in my description was
that after I've put all the data in, it won't be changed or updated in anyway
(it's part of a one-off research project). I want people to be able to use
the application and see how it's set up but there will be no need to
add/change data in future.

I definitely agree that this needs simplified but I'm not really sure how to
go about it? unfortunately there's no budget for getting someone else to
develop it. At the moment, I've got a rudimentary application but I'm far
from sure that I'm going aboout it in the best way.

Anyway, thanks again for all your help (and patience!) - I genuinely can't
tell you how much I appreciate it.

 

[Fred]

Hannah,

What you just said DOES simplify it:

A. The database doesn't have to direct the process of which questions cause
which followup questions. Since it is past tense, they are all just
questions that may or many not have been answered.

B. Your users of the DB will be at least partially skilled or familiarized.



That makes things easier. So here are a few more questions after which
I or someone could take a guess at a structure to get you started:

1. Do you want the database to "know"/record what the possible / allowable
answers are for each question?

2. Do want the database to "know" record the "if then " linkages between
questions?

3. Does the DB have to guide the data-enterer along those paths/linkages?
(your answer should be NO on this if you want to simplify)

4. Are the same people taking multiple surveys? Are there several "fields"
with information on the person (address, city, state, phone etc.)

 

[Fred]

Also:

5. Do you already have a real-world numbering system for people (Like
PersonIDNumber)

6. Do your already have a real world naming or numbering system for the
questionaires?

7. Do you have a real world numbering system fo the questions. Is it
unique across ALL questions, oor just across the questions within on
questionaire?

 

[Hannah]

Fred - first off thank you for being so patient with me! I really do
appreciate all your help. In answer to your questions:

1. It's a bit difficult because I have so many different types of question
(yes/nos, multiple choice, multiple choice where they can choose more than
one answer or add their own answer, and open questions where they can write
whatever they want). The questionnaire is not designed brilliantly (don't
even get me started on that) so it's pretty difficult for any question to
make a list of allowable answers as people have written a whole bunch of
things. So I suppose what I'm saying is no, it's probably very rare that I
could anticipate and tell the database what the allowable answers are for
each question.

2. I think I do want it to know the "if then" linkages if that's possible?

3. No, it doesn't have to guide the person answering them. It'll be me
inputting the data and I quite honestly have dreams/nightmares about it so
I'm pretty sure I know inside out what comes next!

4. No, the participants will only take one survey. There are several fields
with information on the person.

5. No, I don't have a real world numbering system for the people. I'm not
worried about tracking the people (beyond being able to link them to their
questionnaire) so I hadn't thought about a numbering system.

6. There isn't a naming/numbering system for the questionnaires as such.
There are eleven different types of questionnaire (so I suppose I have names
for the category of questionnaire) but I hadn't made a filing system for them
yet. Our plan had been to number and file them according to the numbers used
in Access.

7. The questions are numbered in real life but badly (some follow up
questions don't have a number). It isn't unique across all questions or even
the questionnaire itself (some questionnaires have 3 parts each with
questions 1,2,3).

I hope that's helped answer your questions and apologies if I've given long
winded/unclear answers to anything. Thanks again for all your help.

 

[Fred]

Hannah,

Great answers.

Answering your one question on #2, anythign is possible. Now, is it
feasible for you......answer is also yes if you are just recording, printing
and displaying.....I.E. not having Access do anything fancy based on that.

I'm going to assume an answer to a question which I forgot to ask: Is a
particular question usually used just for one survey? I will assume "yes".
If you want to use it for more than one survey you will just re-enter it as
"another" questions. This avoids the complexity of databasing a many-to-many
relationship

Again, the response would be a bood rather than a post, bust here's my guess
at a framework.

Shorten my overly long explanatory table and field names. Add fields as
desired with info on the entity (with one-to-one correspondence to that
entity) which is databased in the table.

Make a table "Questionaires" with fields:

-QuestionaireNumber (Autonumber, Primary Key (PK)
-QuestionaireNameOrDescription (text)



Make a table" InstancesOfSomeoneTakingOfASurvey with fields:

-SurveyInstanceNumber (Autonumber, Primary Key (PK)
-QuestionaireNumber Integer, not autonumber, this will be a Foreign Key
(FK) Note: FK is a use, not a setting. The subject number will be put
into this field.
-Fields for name and all desired info on the person who took it

Make a table "Questions" with the following fields:

QuestionNumber (Autonumber, PK) (use these numbers in your paper forms....if
not possible, make another field to store the question number from the paper
form)
QuestionText
QuestionAireNumber (integer) This is a FK which will take record which
questionaire the question is for

Maka a table "PossibleAnswers" wiht these fields
QuestionNumber (insert the number of the question that the answer is
applicable to
AnswerNumber (Autonumber, PK)
Answer (this holds any desired answers like "a", "b", "Yes" "No" and maybe
the entire text of answers if you wish)
Answer Description Description of the answers if not contained in the
text. For example, if an answer is "a", this says that taht means "I prefer
green"
FollowUpQuestionNumber When this answer lead to a perticular next
question, record that questions number here


Make a table "InstancesOfAQuestionBeingAnswered" with these fields:

"AnswerInstanceNumber" Autonumber, PK.
"QuestionNumber"
"Answer" For now, just manually enter the answer. Later, get someone whoc
is smarter than me to tell you how to make a dropdown that shows th epossible
answers specific to that question)
"SurveyInstanceNumber"

Well, that's just shooting form the hip (but hopefull a good structure as a
foundation)

 

[Hannah]

Thanks Fred - you've given me a lot to think about and I'm feeling much more
happy about approaching it. Thanks again, can't tell you how much I
appreciate it.

 

[Fred]

Hello Hannah,

Happy to help.

I wrote and posted it too fast for it's size, but hopefully the numerous
typos are obvious / not obscuring anything,

Good luck on your Access adventure.

Fred