hi everyone

im 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

tblRespondent
RespondentID (PK and auto)
LastName
FirstName

tblQuestions
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

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.

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

hth,
Jack

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.

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

Jack

Hello Jack

Im 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

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

hi jack

I 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

The attached demo uses EmployeeID throughout the database...

hth,
Jack

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

Hopefully there is a questionnaire and a survey in the attached...

hth,
Jack

Hello Jack.I tried to open it this morining but it states unrecognised format.I am doing something wrong.
Many thanks
David

DRAT! Try the attached... If this does not work send me your email address and I will send the files to you directly....

Jack

Terrific works well.Once again thankyou
David

Phew! I am glad that I finally got it right! Good luck with the demos and continued success with your project....

Jack