Datasheet question 1

[mach27]

Hi

I have a table of questions and i want to show all its contents in a datasheet view on form.

Here how it looks:
First Table (Question)
QuestNum: Autonumber
Category: Text
Question: Text

Second Table (Questionnaire)
RACF: Text
timestamp: Date
category: Text
Question: Text
Answer: Text
Score: Number
Comments: Memo

All the questions (under table mentioned above) should appear to each one of the user(record)on the second table.

How can I do it? Hope you can help me.

Thanks in advance,
Mach

 

[PHV]

First, have a look here:

http://r937.com/relational.html

After reading you'll see that in the Questionnaire table the Category and Question fields should be replaced with a single field (QuestNum) referencing the Question table.

More wondering:
What is actually the Primary Key of Questionnaire ?
Don't you have a user table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

I have done a lot of survey databases and they can get a little tricky. A lot of people have different approaches. This is a simple one. Normally I have a user's table with demographic information about the user.

tblUsers
autoUserID
strSSN
strLastName
strFirstName
strGender
etc.

Next you have a question table

tblQuestion
autoQuestionID
intQuestionNumber (maybe to sort it)
strCategory
txtQuestion
etc.

Next you have a join table linking people, questions and responses

joinTblUser_Questions_Responses
intFKeyUser
intFKeyQuestion
strReponse
memoComment (I assume this is a user comment about a question)
dtmTimeStamp (default now())
intScore (if this is a score for the question)

Make a compound index out of the user and question foriengn key so that a user can only answer the quesiton once
Now you can set up a Answer form with the user on the main form, and a subform that links "intFkeyUer" to the autoUserID. The subform will consist of this join table linked to the question table.
Now the trick is to populate the join table with keys to all of the questions for each user. This is kind of your original question. When a user opens the form all the quesions appear. One way to do this is to fire an append query when the user opens the form. And append the users id, and all of the question id's into the join table. If the question is already assigned to the user it will not append because of the unique index. There are fancier ways to do this, but this should work.
Not sure what the score is. If that is a value for each question answered correctly then it goes where I said. If this is a sum, then it gets calculated in a query. Now to do your stats on the students, and questions you will use both aggrgate queries and xtab queries.
But as PHV said, you have some normalization problems.

 

[dcurtis]

Check out Duane Hookom's "At Your Survey." It may be what you need, and if not, should get you well on your way.

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