How to sum up various choices with unique values? Expressions, iff?

[rivasj]

I'm not sure if this is possible...but I'll give it a go by asking the question in this topic forum.



I would like to sum up choices made by a user. I'm not sure whether I should create a new table and a couple of fields (not including primary key) with Fruit & Fruit Value. Where: Apple (Y or N)=4 or 0, Oranges (Y/N)=3/0, Bananas (Y/N)=2/0, etc. As the user chooses the fruit, the value rises. For example, if the user selected Yes for APPLE and ORANGES the sum is 7-- or if the user selected ORANGES and BANANAS the sum is 5, etc.

Is it possible?

Many thanks,
Joe

[ranman256]

if user picks APPLE on the question , the result will be TRUE or FALSE.
tTest
[Q], [A]
apple, true

You will need a Points table that has the point values for each fruit.
tFruit
[Fruit], [val]
apple, 4
banana, 2

then in a query, join the 2 tables on [fruit], and pull only TRUE values to sum.