Avatar of PDSWSS
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
Microsoft Access

Avatar of undefined
Last Comment
PDSWSS

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
telyni19

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PDSWSS

ASKER
Thank you very much. Your answer makes complete sense and should solve the problem.
PDSWSS

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

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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
telyni19

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PDSWSS

ASKER
Appreciate the clarification. Will apply it the database.
PDSWSS

ASKER
Excellent solution. Thank you very much for your help.