Full Version: table structure for a survey
UtterAccess Forums > Microsoft Access > Access Forms
FotoArt
hi everyone
Am trying to integrate a small survey into my existing database. Which means im sharing the STAFFNAMES table for the survey.
I have 7 questions on a form, each question givin 3 options. like so
Q1---option1----option2-----option3
Q2---option1----option2-----option3
Do i need a table for each Q?
Which would be the best way to do this?
thanx
ahmed
Jack Cowley
tblRespondent
RespondentID (PK and auto)
LastName
FirstName
blQuestions
QuestionID (PK and auto)
Question
tlbAnswers
AnswerID (PK and auto)
Answer
tblResponses
ResponsesID (PK and auto)
RespondentID (FK)
QuestionID (FK)
AnswerID (FK)
Something like the above should get you started...
hth,
Jack
FotoArt
hello jack
I have the questions in one table and answers in another table as you have shown. BUT I just dont know how i could lay out the questions on a form to fill up the junction table.
I thought I wouldnt need the questions table because if im using Option Groups on the forms.
any help would be apprecited.
thanx for the help.
ahmed.
Jack Cowley
I have to leave right now and I will be gone the rest of the day, but I have attached a demo that should answer your questions..
th,
Jack
FotoArt
hi Jack
The demo was ok. Im just getting used to VB.
But I just cant use the login part. because I already have a table with all the <employee> data. Is there another method, a simpler way how I can input the data into the junction table. I have tried various combinations. It just keeps giving me referential integrity problems.
Any suggestions would be appreciated.
Thank you for the demo.
Ahmed.
Jack Cowley
I don't understand the problem. At some point you are going to have to select a user to take the survey. The current demo is set up so that the same person cannot take the survery twice. To use the 'Login' form you only need to base the combo box on your existing table of employees so they can select their name and do the survey.... All the surveys that I have seen have used a form of some type to sign in the user and then let them take the survey...
ack
FotoArt
Hello Jack
Am still struggling with the demo u posted.
I have a bit confusion about the UserId and EmployeeID fields.
In my existing EMPLOYEE table(where I have all usernames) I already have an <employeeID> field. Do I need another field labeled UserID and if I do where should it be? and whats its purpose?
hope you will help me out.
any suggestions will be appreciated.
thanx again for all the assistance
ahmed
Jack Cowley
In the demo I sent you there is a table called tblEmployes. In that table is a field called UserID. For your purposes wherever you see UserID substitute your EmployeeID for UserID. This is the only place you have to change the name as EmployeeID is used everywhere else in the program. I did not realize this until just this moment so I will change my demo, but all the other tables should work for you if you use your EmployeeID and disregard the UserID.
I'm sorry about the confusion with the demo, but you should be ok now...
Jack
FotoArt
hi jack
just couldn't get past the login part.
I think im stuck with the append query in your demo.
Will it be possible for you to revise the demo and post it AGAIN so that there is no swapping of UserID and EmployeeID.
I already have my <employeeID> field. There is no need forthe UserID for me I think. I just need to select the employeeID and append the value to the junction table. Thats what i Think.
I just couldnt revise the code myself.
any help wuld be appreciated.
thank you for all the replies.
ahmed
Jack Cowley
The attached demo uses EmployeeID throughout the database...
th,
Jack
Dabolt
Hi Jack do you think I could have a copy of the survey for Access 97 .I am doing a similar survey at the moment .
many thanks
David
Jack Cowley
Hopefully there is a questionnaire and a survey in the attached...
th,
Jack
Dabolt
Hello Jack.I tried to open it this morining but it states unrecognised format.I am doing something wrong.
Many thanks
David
Jack Cowley
DRAT! Try the attached... If this does not work send me your email address and I will send the files to you directly....
ack
Dabolt
Terrific works well.Once again thankyou
David
Jack Cowley
Phew! I am glad that I finally got it right! Good luck with the demos and continued success with your project....
ack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.