PDSWSS
asked on
How would I change the number from the combo box drop down choice that is saved in the MS Access Table?
I have created an MS Access 2007 database that contains many questionnaires (forms).
A small sample of this DB is attached. Please see YMRS table and form.
On several questionnaires I need to have the total of the answers saved in the Total Field in questionnaire (forms) and the corresponding tables. A number is associated with each answer and this answer for each question is chosen from a combo box. Please see attached example from the database.
The position of the chosen answer in the combo box is saved in the table and not the # that goes with the answer. For example, if the answers in combo box are 0,2,4,6, a choice of 4 is saved as 3 not 4. I would like to save the answer to the question not the # of the position in the drop down. This is important because I need to calculate the total of all the answers.
For example, if answers to Q1 = 4 and Q2 = 6 then total = 10. Based on position in dropdown Total now = 3 + 4 = 7. You can see the problem.
How would I save the numerical answer and not the position in the combo box in the table corresponding to each form? Thanks,
EEtest.accdb
A small sample of this DB is attached. Please see YMRS table and form.
On several questionnaires I need to have the total of the answers saved in the Total Field in questionnaire (forms) and the corresponding tables. A number is associated with each answer and this answer for each question is chosen from a combo box. Please see attached example from the database.
The position of the chosen answer in the combo box is saved in the table and not the # that goes with the answer. For example, if the answers in combo box are 0,2,4,6, a choice of 4 is saved as 3 not 4. I would like to save the answer to the question not the # of the position in the drop down. This is important because I need to calculate the total of all the answers.
For example, if answers to Q1 = 4 and Q2 = 6 then total = 10. Based on position in dropdown Total now = 3 + 4 = 7. You can see the problem.
How would I save the numerical answer and not the position in the combo box in the table corresponding to each form? Thanks,
EEtest.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please clarify how I would do the following?
Then for each question's drop-down, you would do a select on the table and filter by the question number, and use the AddValue as the bound column.
Then for each question's drop-down, you would do a select on the table and filter by the question number, and use the AddValue as the bound column.
ASKER
Your solution would indicate a separate lookup table for every questionnaire - correct?
Some of these forms have over 100 questions - is there a simpler way?
Some of these forms have over 100 questions - is there a simpler way?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appreciate the clarification. Will apply it the database.
ASKER
Excellent solution. Thank you very much for your help.
ASKER