Hi - first time posting here, so I hope I'm posting in the correct thread.
I'm using Access 2016 to try and update this data. I have a table (call it tblPeople), and it has a custom field (tblPeople.Custom1) which contains multi-select data (codes 1-20, let's say). In tblPeople, I have a need to create a new field (tblPeople.Custom2) which contains multi-select data that matches some data from Custom1 (but only codes 10-20, let's say). I am trying to take the data from Custom1 and update it to Custom2. What is actually happening is, Custom2 will update with data from Custom1 (in its entirety) even if a "code" in Custom2 doesn't exist.
For example, I run an Update Query as:
UPDATE tblPeople SET tblPeople.Custom2 = [Custom1];
When I run the Update, Custom2 becomes everything from Custom1 (Codes 1-20, in this example), although Custom2 only has codes 10-20 in its table.
(so, after the Update, the Custom2 field data looks like "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20" - when I only needed "10,11,12,13,14,15,16,17,18,19,20" to update)
How can I run the Update query to exclude non-existent data in Custom2 from any combination of codes from Custom1?
Thanks in advance for your feedback!
I'm using Access 2016 to try and update this data. I have a table (call it tblPeople), and it has a custom field (tblPeople.Custom1) which contains multi-select data (codes 1-20, let's say). In tblPeople, I have a need to create a new field (tblPeople.Custom2) which contains multi-select data that matches some data from Custom1 (but only codes 10-20, let's say). I am trying to take the data from Custom1 and update it to Custom2. What is actually happening is, Custom2 will update with data from Custom1 (in its entirety) even if a "code" in Custom2 doesn't exist.
For example, I run an Update Query as:
UPDATE tblPeople SET tblPeople.Custom2 = [Custom1];
When I run the Update, Custom2 becomes everything from Custom1 (Codes 1-20, in this example), although Custom2 only has codes 10-20 in its table.
(so, after the Update, the Custom2 field data looks like "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20" - when I only needed "10,11,12,13,14,15,16,17,18,19,20" to update)
How can I run the Update query to exclude non-existent data in Custom2 from any combination of codes from Custom1?
Thanks in advance for your feedback!