Update field data from one field to another field, within same table (1 Viewer)

mcrudo

New member
Local time
Today, 04:02
Joined
Jul 26, 2020
Messages
6
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!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:02
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

As you can see, you're having problems already. Part of the issue is your use of a "multi-select" field (actually called multi-value field or MVF). So, if you're just starting out, I would recommend you don't use a MVF in your table and use a child table instead (the "normal" way to handle child/related data).
 

isladogs

MVP / VIP
Local time
Today, 11:02
Joined
Jan 14, 2017
Messages
18,186
Hello and welcome to AWF

If I understand you correctly, when you mention multiselect data, you are referring to multivalued fields.
These cause major problems and are best avoided. See my article

Next you want to duplicate that MVF data into another MVF field. Whatever the reasons, that is very likely to be wrong.

If my understanding is correct, you need a major rethink of whatever you are trying to do.
 

mcrudo

New member
Local time
Today, 04:02
Joined
Jul 26, 2020
Messages
6
Thanks for the quick feedback. So I'm clear (perhaps using the wrong terminology), the data in the tblPeople.Custom1 and Custom2 fields is pretty standard and straightforward, from a setup standpoint. Custom1 might be "mobile technology devices owned," let's say. So, Custom1's table would be Code, Description (1,item1; 2,item2; etc.). Custom2 might be "household technology devices owned," let's say. So, Custom2's table would be the samesetup, Code, Description (1,item1; 2,item2; etc.).

Then in tblPeople, the "People" would have data stored as tblPeople.Custom1 like "1,2,3,4,5,6,7,8,9,10,11,12" (if they owned twelve devices), and in tblPeople.Custom2 like "10,11,12" (which at this point is empty).

I simply want the "10,11,12" from Custom1 copied over into Custom2 (because that's the only duplicate data between Custom1 and Custom2) , but it is copying "1,2,3,4,5,6,7,8,9,10,11,12" instead.

I hope that clears things up. Multi-Select data is common, so my apologies if my explanation lead to a different thought.

Any thoughts?
 

isladogs

MVP / VIP
Local time
Today, 11:02
Joined
Jan 14, 2017
Messages
18,186
My thoughts are exactly what I wrote before. Avoid the use of MVFs. Read my article to understand why.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:02
Joined
Jul 9, 2003
Messages
16,244
How can I run the Update query to exclude non-existent data in Custom2 from any combination of codes from Custom1?

Have a look at Nifty Access website on multi value Fields:- https://www.niftyaccess.com/multi-value-fields-links/ There is an excellent video on Multi Value Fields from >>> Takeshi K

At the bottom of the page there are some links, there's a link to Colin's website where he provides some excellent information on multi value fields (already posted) and there's also one from Microsoft on how to manipulate multi value Fields with DAO
 

June7

AWF VIP
Local time
Today, 03:02
Joined
Mar 9, 2014
Messages
5,423
A normal UPDATE cannot even update a multi-value field so if you are getting data in the second field from first field, neither can be MVF.

Why 2 fields and not just 1 field for all devices?

If you are actually saving a comma-separated values string (CSV) in each field, that may be even worse than MVF.
 
Last edited:

mcrudo

New member
Local time
Today, 04:02
Joined
Jul 26, 2020
Messages
6
All...again, I sincerely appreciate the feedback.

I am looking to take data from one field (Custom1) and replicate it to another field (Custom2), with the end-result being to remove the data from Custom1 so it is not duplicated data.

I'm am trying to keep it as general as possible in my explanation.

Tom has 10 devices.
His devices are in the DB as tblPeople.Custom1 = 1,2,3,4,5,6,7,8,9,10,11,12

I need to tie Tom's devices to a new table (Custom2) on tblPeople.Custom2 = 10,11,12

Then I need to remove 10,11,12 from Custom1 once ALL people have had their data moved to Custom2.

I do not want Custom2 to have 1,2,3,4,5,6,7,8,9 in it as a result of the UPDATE query I have posted, but it is added these 9 codes to the field.

How can I run an UPDATE query to only copy data where data exists (Custom2 has 10,11,12, but it does not have 1,2,3,4,5,6,7,8,9)?

If there's not a way to run an UPDATE query to do so, please let me know, as I've searched far and wide prior to posting here.

I often find very useful information/solutions in forums, and I also find a lot of room for misunderstanding (because not all thoughts can be relayed appropriately).

Thanks to all, and I look forward to any additional feedback. It's an older DB and not able to re-create some things at this point given the structure in place.
 

June7

AWF VIP
Local time
Today, 03:02
Joined
Mar 9, 2014
Messages
5,423
Did you read my last post? I may have edited after you read it. Looks like a CSV string to me and breaking this string to save only the segment that has values 10 and greater would require string manipulation functions and maybe even a VBA custom function.
 

mcrudo

New member
Local time
Today, 04:02
Joined
Jul 26, 2020
Messages
6
Yes, the fields take CSV values as imports.
I simply want to move data from Custom1 to Custom2, without all codes from Custom1, and only those where Custom1 and Custom2 are equal.

If I'm completely off-base here, and this is not possible, please let me know. I do understand the advice of creating something different (and more friendly in the end), it's just not possible given the DB as it is.
 

Micron

AWF VIP
Local time
Today, 07:02
Joined
Oct 20, 2018
Messages
3,476
I think you're at the point where you either have to post a sample db (zipped) or actual source data and expected results. I agree with everyone else's comments; problem may be that you're asking some people to figure out for you what they would never do. That makes it so much harder to get a solution.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:02
Joined
Jan 23, 2006
Messages
15,361
Show readers your table design so that we're all talking about the same thing. As others have noted and cautioned --avoid MVF fields.

If you are just starting this project and want some advice/assistance re Access, then get the redesign sorted and vetted before attempting to create work around code to correct some symptom of what seems to be a design issue. Again, we don't know exactly what you have, nor what you really need, so these comments may not apply.
 

mcrudo

New member
Local time
Today, 04:02
Joined
Jul 26, 2020
Messages
6
Thank you all, again. I have an image to hopefully show what it is I am trying to convey.

In the image is a not about the age and, thus, limitations of the DB in its current state.

Please let me know if there is anything else I can provide. I realize it is not "optimal" in design, but it is what it is for us for now, until a complete overhaul is done at some point when cash flow resumes in this economy.

Many thanks again.
Access_DB_update_query_question_image1.png
 

June7

AWF VIP
Local time
Today, 03:02
Joined
Mar 9, 2014
Messages
5,423
As I indicated, yes it can be done. Something like:
Code:
Function BreakString(strS As String) As String
Dim aryS As Variant, x As Integer
aryS = Split(strS, ",")
For x = 0 To UBound(aryS)
    If aryS(x) > 9 Then BreakString = BreakString & aryS(x) & ","
Next
If BreakString <> "" Then BreakString = Left(BreakString, Len(BreakString)-1)
End Function
UPDATE tblPeople SET [Column2] = BreakString([Column1])

And if you want to also change the string in Column1, code could be modified to handle that.
 
Last edited:

Micron

AWF VIP
Local time
Today, 07:02
Joined
Oct 20, 2018
Messages
3,476
You're asking how to do something you apparently shouldn't be doing. IMO it is very rare that you would ever require a field with csv values, but not withstanding that, nothing you're showing helps anyone to figure out what needs to be done. For that, you need to start with a thorough explanation of what process/business that the db is supposed to support because none of that makes sense. There is just so much wrong with what I see that I really wouldn't know where to begin - except maybe to tell you to make sure you understand normalization with respect to databases or the basics on how to create one. Maybe look at these, but focus on normalization until you can explain it to someone else.

Normalization Parts I, II, III, IV, and V
and/or

Entity-Relationship Diagramming: Part I, II, III and IV

How do I Create an Application in Microsoft Access?

Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk/forums/showthread.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

Or just ignore all advice and implement a solution that was suggested, which will get you moved on to the next trap.
 

mcrudo

New member
Local time
Today, 04:02
Joined
Jul 26, 2020
Messages
6
Thanks all, bowing out. I appreciate the input. I find great solutions through forums, but there's also a ton of judgement.
I did not create the DB, but I must maintain it (as it is) to the best that I can.

Just wondered if there was a possible way to modify this "new" field with data fro man existing field, with the hope of deleting a lot of duplicate data while organizing certain sections of the DB which have grown out of whack.

June7 I will attempt your suggested "Function BreakString..."

Thanks again, all. You've been helpful.
 

Micron

AWF VIP
Local time
Today, 07:02
Joined
Oct 20, 2018
Messages
3,476
It's an older DB and not able to re-create some things at this point given the structure in place.
I missed that comment and apologize if I came across as judgemental. You weren't being judged but I suppose the db was. Might be a good idea for next time to state that up front. Such info can get lost in the noise of what someone wants to do when it just seems contrary to what should be.

If you're not familiar with the linked information, then perhaps it will help you avoid repeating what you have now in your next db, which ought to make your life easier.
 

Users who are viewing this thread

Top Bottom