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

[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

 

[The_Doc_Man]

First, before we get too deep into this, I have to say that your structure as you have shown it lacks normalization and resembles something that you could have (and probably did) extract from an Excel spreadsheet. If this is a quick-and-dirty project that is for limited use, what you have might work. If this is part of something much bigger, step away from the keyboard and do some reading on database normalization.

OK, obligatory lesson has been issued. Now to your question:

It is difficult to see what you intend to do because in your post you talk about the columns having some encoded number, but the .PNG shows a text content in a given column. This is difficult to visualize. Further, you have numbers going as high as over 160 in the presentation attachment. Does that imply you actually have that many columns to manage? If not, your use of the columns numbered as you have them and talking about the values in the columns has totally obscured what you are trying to do.

You have given us a technically oriented example of what you want but I know that I can't "see" it in my head. Can you give us an English description of what is going on here? Because all you did was give us substitution rules and without understanding what is being substituted, I know of no particular method except for brute force. Which is the worst and most tedious of methods. With better understanding, I might be able to point you in a better direction.

 

[arnelgp]

check if this is close to what you need. chk query1

 

[Solo712]

check if this is close to what you need. chk query1

Good work, arnel!