Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-27-2017, 06:35 PM   #1
rashokku
Newly Registered User
 
Join Date: Nov 2017
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
rashokku is on a distinguished road
Updating values in a table based on the combination of values in the columns in the s

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
Attached Images
File Type: png Diease_Hierarchy.png (95.4 KB, 95 views)

rashokku is offline   Reply With Quote
Old 11-27-2017, 09:42 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,271
Thanks: 62
Thanked 1,144 Times in 1,045 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Updating values in a table based on the combination of values in the columns in t

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-27-2017, 10:56 PM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,695
Thanks: 55
Thanked 2,125 Times in 2,036 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Updating values in a table based on the combination of values in the columns in t

check if this is close to what you need. chk query1
Attached Files
File Type: zip CMS-HCC.zip (31.0 KB, 19 views)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Solo712 (11-28-2017)
Old 11-28-2017, 02:39 PM   #4
Solo712
Newly Registered User
 
Solo712's Avatar
 
Join Date: Oct 2012
Posts: 653
Thanks: 13
Thanked 102 Times in 99 Posts
Solo712 will become famous soon enough
Re: Updating values in a table based on the combination of values in the columns in t

Quote:
Originally Posted by arnelgp View Post
check if this is close to what you need. chk query1
Good work, arnel!

__________________
Dyslexics of the world untie !
Solo712 is offline   Reply With Quote
Reply

Tags
compare cells , split , sql , update , update data

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
query to update columns based on values on other specific columns and values Dieneces Queries 1 05-17-2012 01:53 PM
Updating table based on Max Values DFowler Queries 2 01-28-2010 02:53 AM
Update values in table based on values in another AlexD Queries 13 11-11-2003 01:50 AM
auto updating form values based on another table potts Modules & VBA 1 08-06-2002 10:48 AM
Creating new values in a table based on existing values Dave Edwards Modules & VBA 1 11-27-2000 12:05 PM




All times are GMT -8. The time now is 12:20 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World