468,539 Members | 1,521 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

Can you add a string inside a record without deleting its previous value

I want to add a new string inside a cell without deleting its previous value. The goal is one asset is part of multiple kits like a resistor. So i need a cell inside resistor asset with multiple value like (kit 1, kit 2, kit 3).

Question:
If I add a new kit with a resistor asset, i have to update that cell inside resistor asset. But when i do a update query it replaces the current value of that cell to the value of 'Update to' inside the query.
Oct 24 '15 #1

✓ answered by zmbd

1) It is not considered best practice to store multiple values within a single field. Instead, one should create a normalized database structure. >> Database Normalization and Table Structures.

2)However, there are legitimate instances where one may need to append values to the current value in a field.
Generic for a static value:
Expand|Select|Wrap|Line Numbers
  1. UPDATE [TableName]
  2. SET [FieldName]=[FieldName]+'appendedvalue'
  3. WHERE (criteria);
One can get fancier using SELECT and Sub queries to build the final string between tables and then update the third. Once again, if one is needing to do this then it generally indicates a flaw in the original table structures and relationships. ((I say generally because there are times where one needs to de-normalize for reports or other specialized situations. Even then I tend to use a VBA function such as this one Function fConcatChild to build the string on the fly from the normalized data. ))


On a side note:
To store data/information:
Excel/spreadsheets use "Cells"
Access/databases use Tables>Records>Fields. :)

2 878
zmbd
5,400 Expert Mod 4TB
1) It is not considered best practice to store multiple values within a single field. Instead, one should create a normalized database structure. >> Database Normalization and Table Structures.

2)However, there are legitimate instances where one may need to append values to the current value in a field.
Generic for a static value:
Expand|Select|Wrap|Line Numbers
  1. UPDATE [TableName]
  2. SET [FieldName]=[FieldName]+'appendedvalue'
  3. WHERE (criteria);
One can get fancier using SELECT and Sub queries to build the final string between tables and then update the third. Once again, if one is needing to do this then it generally indicates a flaw in the original table structures and relationships. ((I say generally because there are times where one needs to de-normalize for reports or other specialized situations. Even then I tend to use a VBA function such as this one Function fConcatChild to build the string on the fly from the normalized data. ))


On a side note:
To store data/information:
Excel/spreadsheets use "Cells"
Access/databases use Tables>Records>Fields. :)
Oct 25 '15 #2
hvsummer
215 128KB
we can update info into 1 records without delete old value by copy that value first, adding info to it then insert it back into table again, can be done via VBA.

I think zmbd did give the best solution via SQL, but you will need some tweak with JOIN, ORDER BY, WHERE to resolve your problem.
Oct 25 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Michael Yanowitz | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.