Newbie Help Please

Hi

I would like to create an Access 2002 database to capture the answers from a questionaire with 25 questions. I'm not very familiar with access and don't know any VB

I would be grateful for some help on the structure.

I've created 3 tables - Contacts - Questions - Answers

1. Contacts Table

ContactID
First Name
Last Name
etc etc

2. Questions Table

QuestionID
Question
?

With this table do I need to create a field for each of the 25 questions or do I create one question field and create 25 records with one question written in each of the 25 record cells?

3. Answer Table

AnswerID
QuestionID
ContactID
?
?

Do I need to create 25 answer fields here or one answer field?


Once I have the structure set up correctly I want to create a form to enter the answers. Do I need to establish relationships between these 3 tables? I can't get me head around where I need to make the connections - is it one to many or one to one?

Any help would be greatly appreciated.
Cheers

Hey Tinggg, these types questions get answered a lot on this forum - you are looking to "normalize" your tables, and you're real close!

1. This will have all the people who answered your questionaire. Self-explanatory.

tblContacts

ContactID - PK
First Name
Last Name
etc etc


2. This will have all the questions you're asking. In your case, it will have 25 RECORDS (not fields!), but this may grow/shrink as time goes on if your questionnaire undergoes revisions.

tblQuestions

QuestionID - PK
Question


3. The next table is your "answers table":

tblAnswers

QuestionID - FK
ContactID - FK
Response

And that's it. (You could put an "AnswerID" autonumber in tblAnswers if you want for future use, but you'll designate QuestionID and ContactID together in Design view of tblAnswers to be a composite key, so...)

Then you'll want to go into Relationships from the main database window, apply some one-to-many relationships between the matching fields, and you should be good to go. Make sure you check off the referential integrity options, too!

(I hope I got all that right.)

Hi, thanks MT, nearly there

I folowed you until this bit

tblAnswers

QuestionID - FK
ContactID - FK
Response

And that's it. (You could put an "AnswerID" autonumber in tblAnswers if you want for future use, but you'll designate QuestionID and ContactID together in Design view of tblAnswers to be a composite key, so...)

Click to expand...

Shouldn't I create a primary key on the Answers table? I'm not sure what FK means.

Hi

This is where if gotten to....

I'm creating a database which will capture the answers to a questionaire with 25 questions.

I have 3 tables - Contact - Questions - Answers

1. Contacts Table

ContactID
First Name
Last Name
etc etc

2. Questions Table

QuestionID
Question Number (ie 1.1, .1.2, 2.1)
Question

3. Answer Table
AnswerID
QuestionID
ContactID
Answer

I have created relationships from the:
Contact table to the Answer table
ContactID to ContactID

and
I have create a relationship from
Question Table to the Answer table
QuestionID to QuestionID

Problem:
I want to create a form that allows me to enter the answers to all 25 questions. I create a query and include the Question Number and Question field from the Question table BUT when I create a form from this query I only get the entry fields Question Number and Question I can't see the 25 questions I entered as records in the Question table.

Any idea how I can make these questions appear in the form so that I can enter the answers??

Thanks

Hey Tinggg,

"FK" means "foreign key".

W/r/t your question showing up on the form: It sounds like you just need to include the correct tables and fields on your query, and then put corresponding bound textboxes on your form. (Make sure your one-to-many relationships/integrity are set correctly to allow you add new records. You can test this by simply running the query, and seeing if it allows you to add new records there.) Am I understand you correctly, or is the problem deeper than that?

Hi MT

I think i've included the correct fields to create the query from which to create the form. My relationships are all one to many. I've attached screen dumps to show my structure.

Here are the table fields I have used for the query:
I have used my 3 tables Contact/Question/Answer:

1. Contacts table
Fields I have used in the query = ContactID, First Name, Last Name

2. Question Table
Fields I have used in the query = Question Number, Question

3. Answer Table
Fields I have used in the query= Answer

Can you see and problems here as to why all 25 questions are not appearing when I create a form from this query. I do not want to cycle through all questions on the form but rather to see all 25 at once.


It's like there's a setting that would allow all the records on the Question table appear???

Thanks for your help

Hey Tinggg,

This looks like a prime candidate (IMHO) for a Form-Subform solution.

What I would do is build two queries - one that just grabs User Info (something like "qryUserInfo" or whatever), and one that grabs the question/answer info (like you've done here, maybe called "qrySurvey" or what-have-you).

Then build a form ("frmMain" or something) based on the first query which shows the names of your survey takers. This form should probably be in "Single Form", which you can designate in the properties (the attribute is called "Default View"). Insert a Subform ("fsubSurvey" or something) based on the 2nd query (what I was calling "qrySurvey" above) and link it to the main form by the common UserID field that appears in both queries. In contrast to "Single Form" view of "frmMain", put this subform in "Datasheet" or "Continuous Form" view (you'll have to do some maneuvering around to make Continuous Form view look nice, but it usually pays off - Datasheet is quick, but looks kind of brutish).

Hope this works for you, Tinggg - if anyone else has a more elegant solution (or can explain it better than I did) please jump in!

I Created 2 Queries

1. Contact Query (using the Contact table only), with ContactID, First name, second name, etc

2. Question Query (using fields from the Contact table [ContactID], Question Table [Question Number, Question], Answer table [Answer]

Followed your instructions with the Contact Query and created it as a "Single Form"

I create the Question Query (to be subform to Contact SingleForm) in dataview - I set it to Continuous Form and the 25 questions still didn't appear. Should I now be seeing all the questions? i've attached a screen dump.

Any ideas....thanks very much....this is driving me bananas so any help would be great.

Hey Tinggg,

I was misunderstanding what you were asking, but I think it's clear to me now.

You want to see the spaces for every question, whether or not every user answers every one, right? You're looking to see even places that might be blank? I thought you were just asking to see all the answers and questions that a participant submitted. (I'll have to read more carefully next time!)

There are two ways to do this - a quick and dirty, non-normalized way, and a "proper", although more difficult, way.

1. The quick and dirty way is probably the way you first started out, and it's the way you'd do it in a spreadsheet - a field for every user for every question. The reason this isn't done is because it has the potential to leave tons of NULL fields, and you also need to jump through all sorts of hoops later on if/when, say, your survey changes and questions are added or deleted or what have you. If you are ABSOLUTELY positive that the questionaire will not change and this is not a dynamic project (i.e. the database will not be in use for long), then you can probably get away with having an un-normalized table which will have a bunch of NULLs for all the participants who either a: didn't answer all the questions or b: haven't input everything yet. (But you never heard this from me. The first rule of un-normalized tables is that you don't talk about un-normalized tables.) Be warned - this type of quick and dirty building gives you lots of easy results in the short run, but will quickly bite you in the rear if the project needs to expand or is ongoing...and, to be honest, I STILL get bitten in the rear by this.

2. The "proper" way to do it would be to run an APPEND QUERY every time you input a new participant. (This can be triggered via Visual Basic or Macros using the "After Insert" property/event on your main form.) In other words, every time you have a new record in the tblContacts, that person's ContactID is added throughout the AnswersID table as well, populating the table and leaving NULLs in the "Answers" field until you get around to filling those in.

This thread might help explain it better:

Append Queries for a Survey

If Pat Hartman says that's how she'd do it, then dagnabbit, this monkey is listening to her. Take special note of comments #4, #5 and #6. Pat explains quite simply what has taken me days to convey.

If you do a quick search on these forums for "survey" or "create survey" or things like that, you'll probably find lots of examples to pillage, too!

thanks for all your help

monkeytunes said:

2. The "proper" way to do it would be to run an APPEND QUERY every time you input a new participant. (This can be triggered via Visual Basic or Macros using the "After Insert" property/event on your main form.) In other words, every time you have a new record in the tblContacts, that person's ContactID is added throughout the AnswersID table as well, populating the table and leaving NULLs in the "Answers" field until you get around to filling those in.

Click to expand...

Trying to do step 2 so that my questions show up on the form but cant get it to work can anyone help me out with this?

Thank you,
Bill