survey

[slimjen]

Ok. I need to design a form in my database to have the user complete questions on a personnel exit interview. Most of these questions would be yes; no type. I know it’s now good database design to use yes/no fields in table. I have researched and found the “At your survey” by duane hokum. But the design uses combo boxes in subforms for the questions??. Is there another way to design this. For example. I have a list of employees in a table. I need the user to be able to select the employee on a form or subform and have a list of questions to answer yes; no or perhaps unknown. Should I have a list of questions in a table with possible answers? And how do I connect them to the employee? So far I have come up with the following table structure but can’t seem to get it the way I want to on the form with putting the yes/no test boxes in a table for each question. Table structure:

tblEmployee
EmplID (PK Auto)
SSN
FirstName
Lastname
 
tblQuestion
QuestionID (PK Auto)
Question
 
tblAnswer
AnswerID (PK Auto)
EmplID (FK)
QuestionID (FK)
Answer

Also; when create a qry for the report; it doesn’t come out right. The questions and answers doesn’t correspond with the answers and the empl. It almost seem like I should be doing a pivottable or matrix.
Ultimately; I would like to see a form like this:

EmplID                  SSN                       HireDate
FirstName           Position                TermDate
LastName            
                                                                                Yes   No   Don’t Know
Did employee leave voluntarily?              Radio button go here
Was the employee terminated?

And Report like this

EmplID  Q1          Q2          Q3          Q4          Q5          Q6
450         x                              x              x              x
451                         x              x                              x              x

As I mentioned before; I can only find examples with combo boxes for the questions. Does someone have something similar to what I’m looking for please? FYI; I posted in another forum but no ans.

[ItsMe]

If you do not like the combo box control another approach would be an option group. They should still be available through the wizard in newer versions of Access.

Option groups can use radio buttons or check boxes. nut, they are generally for when you have more than two options to select.

Another control may be a toggle.

I think there are advantages to the combo box. One it is simpler to compile the info from the user's input. Two, you can you an after update event to change focus and minimize incorrect answers.

There may be other reasons but there are my two pennys

[slimjen]

Thank you for replying. Can you possibly provide an illustration of how this would work. I am not opposed to seeing how the combo box would help. but Im really strugglying with the design. One exit interview to one set of questions and answers. thank you

[ItsMe]

How what would work? An option Group?

You turn your wizard on while in your form's design view. Place an option group onto your form using the "Option Group" tool. The wizard asks you some questions and you are off and running. The option group can return a value of 1, 2, 3, 4 and so on. You might be able to assign -1 to yes and 0 to no while inside the wizard.

Give the wizard a try.

[slimjen]

Thanks for replying. I apologize I wasn't clear. I was refering to the combo boxes. How would it work in my case if I have a 1:1 instead of 1:many. Each exit interview would only have 4 set questions and answers. On the form; I would like to select an exit interview emplid; tab to the list of questions and choose the correct ans by toggle or option and have the answers stored so there is a report pulled by the user. Thanks

[ItsMe]

I am familiar with exit interviews. Fortunately, the majority of times I am the interviewer. I can appreciate the need to store correct information and offer it in a report, etc. I am not too clear on how the final form interface will work and the functionality you envision.

Judging from the questions you are asking and the general tone of the thread. I believe you need to take a step back and look at the structure of your tables. Most questions will be solved/answered by keeping all of your data relative.

I have not looked at your DB but, one example would be to have a simple table. It will have two columns and two records. One column will contain fields for the PK as integer data type, the other will contain an answer field with data type text. You can use this table as your combo box's rowsource.

When a user interfaces with your new combo box, they see Yes or No as an option. What they choose will affect the ComboBox's value. The value will be the PK for the record selected. Yes may be represented by a PK value of 1 for instance. The record No may be represented by record PK value 2. It could just as easily be 10 and 20. Depends on how you enter the data into your table.

[slimjen]

Got it. I will work on it this weekend and post back next week. Thank you for taking the time to respond.

[ssanfu]

Also, it depends on the questions or how you as the questions. For example, you provided a question:

                                      Yes   No   Don’t Know 
Did employee leave voluntarily?      Radio button go here 
Was the employee terminated?

The only way I know of to leave a company is "Quit" (voluntary termination) or "Get Fired" (involuntary termination).

Couldn't the question be:

Termination type?

With a combo box that has selections:
1, "Voluntary"
2 "Involuntary"
3 "Don't Know"


I'm in agreement with ItsMe (post # 6). Structure of tables is important.

As to the report, what is the purpose? To see how many were fired... um, I mean had involuntary termination vs.quitting?

I would be interested in seeing some of your other exit questions... (I'm setting up a text dB)

[slimjen]

The users will get me a list soon. You said you are setting up a text db??