Updating values in a table based on the combination of values in the columns

[rashokku]

First of all the thanks for your help. Attachment is the spec which I am explaining below.

Columns in table:
--------------------
MemberID (Primary Key):
COL1
COL2
COL3
COL4
COL5
COL6
COL7
COL8
COL9
COL10
COL11
COL12

I have 13 columns in a table in MS Access database as above. I would like to update the columns based on the combination as in the attached specification.
For example, if the combination is "9, 10, 11, 12" (if atleast four or more of the 12 columns has all the four values in any order - let us say
COL1 = 10, COL2 = 9, COL3 = 12, COL4 = 11, COL5 = 12 then update the columns with 8.

Attachment has combination and the values to be updated. Another example, from attachment/spec. If the combination is "111, 112" (atleast two of the columns having each of the values)
then update the columns with 110.

Anybody can provide insights on the approach.

Thanks,
Ram

 

[LaurieHamlin]

I get combining fields to another field. I don't understand why if it = 111, 112 you would update it to 110.
Regardless try an update query with a switch
UpDateTo: Switch(combination = "9, 10, 11, 12", "8", combination = "111, 112", "110", combination = X, updateto this, etc.)

I hope that helps.
Laurie

 

[dhookom]

Do you want to update all of the columns with "8" or whatever value? That doesn't make much sense to me. Also, do you have a table as represented in the image file?

What would the result be if various columns contained 18,19,83,84 since it would have all of the values for 10 and 82?

I think I answered a very similar question in another forum. Was that you also?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016