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.
First of all, it is not normally a good idea to store "changeable" data like this in the table.
The reason being that you have to double check the table each time the database is opened to see if the data needs updating again.
However to answer your question there are 2 methods to do this in the table that already has the data in it and another where you can make the judgement as the data is entered into a Data Entry Form.
The 1st method for updatig the table would be by an "Update Query" using multiple IIF() statements
the 2nd way is to use a VBA Recordset to update the data uisng multiple If/Then/Else statements.
THe method for the form is also Multiple VBA statements as well in the After Update events of various fields or the Form itself.
Reply With Quote
