Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72

    Question how to update the content of a field depending on the content of another field ?

    Hello,

    I have several access tables containing 2 fields : "ID" and "Name"
    I would like to update the content of the "Name" field.

    In each line, I'd like to insert a string of characters before the current characters string contained in the "Name" field.
    This string of characters I'd like to insert is made of the first 9 characters contained in the "ID" value.

    So for example, if the ID contains "12345678_9101112131415" and the corresponding Name is "Jack_L", I need the updated Name to be "12345678_Jack_L" (because the first 9 characters of the corresponding ID are "12345678_"). The ID should not be modified.

    How can I do this? (I am a newbie regarding this kind of thing)


    I guess it's an update query, but I don't know how to build it, what the formula should be, and where to write it.

    Thank you for helping me :-)
    Last edited by nicoboss; 09-25-2019 at 10:56 AM.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is the purpose of this? The usual approach to database is 1 fact, 1 field.

  3. #3
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    The new name could be inserted in a third field. I'll have to delete it later.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I am also a bit confused as to what you are trying to do and why. There is a very good chance that it may not be necessary at all.
    Most of the time, if something value can be calculated, there is no reason to store it (and storing it can actually violate Rules of Normalization and undermine data integrity).

    As Orange alluded too, I think we need more details regarding what exactly you are trying to accomplish and why. There may be a better way to do it.

  5. #5
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    This is a table of correspondance.
    These data are stored in access and I need to edit the content of the "Name" field as mentioned in the 1st post.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ?? Doesn't clarify for me.
    Tell us about
    I have several access tables containing 2 fields : "ID" and "Name"
    . Why you have several such tables is unclear, and may be relevant to any solutions. You know details of your issue and we can only guess , until you describe it to us.

  7. #7
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Sorry, I don't understand which additional information you need.

    At the moment, I have 2 fields both full of text.
    For each entry, I need to insert the 1st 9 characters of the "ID" field at the beginning of the text contained in the "Name" filed.

    So for example, if the ID contains "12345678_9101112131415" and the corresponding Name is "Jack_L", I need the updated Name to be "12345678_Jack_L" (because the first 9 characters of the corresponding ID are "12345678_"). The ID should not be modified.

    At the end of the process, I need to have a new table of correspondance with the "ID" field (unchanged) and the edited "Name" field.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    @nico

    what you are trying to do can be done, but we cannot understand why you need to do it since it can be easily calculated 'on the fly' when required.

    The concern is if you were to do it then any searches you need to do on the name part of the field will become a) more complex to achieve and b) will be much slower than if the name was kept as a separate field.

    So we are asking why you want to do this. To me it seems like the excel way of doing things and with access, many things are done in a completely different way - including this sort of exercise.

    So, once you have your changed name field - what will you do with it? will it be used for sorting? filtering? criteria in a query? printed on an envelope? used as a letter reference?

  9. #9
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    the table of correspondance I'll get after running this update will be used by a python script which requires it

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The general rule of thumb in Access is that most anything that can easily be calculated "on-the-fly" shown be done as a Calculated Field in a Query, and not stored as a hard-coded value in the Table (which violates the Rules of Data Normaliziation that says that you should not have fields within a table that are dependent upon each other).
    Pretty much anything that you can use a Table in Access for (Forms, Reports, exports, links to other Tables/Queries, etc), you can also use a Query for. So it is usually totally unnecessary to write those type of values back to the Table level.

    Is there any good reason why this cannot be done simply as a Calculated Field in a Query without writing it back to Table fields?
    Can't your python script use the Query instead of a Table?

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about a query on your existing table(s)
    Code:
    select  left(id,9) & [Name] from yourtable
    Should produce 12345678_Jack_L based on your earlier post.

  12. #12
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by orange View Post
    How about a query on your existing table(s)
    Code:
    select  left(id,9) & [Name] from yourtable
    Should produce 12345678_Jack_L based on your earlier post.

    Hi Orange, I have tried a "table creation" query as you can see in the screenshot, but it seems that I did it wrong (maybe I didn't insert your code in the right place).

    Click image for larger version. 

Name:	Access table creation query.png 
Views:	11 
Size:	3.0 KB 
ID:	39816

    By the way, I need the output field to be hardcoded, because I'll delete the old "Name" field after getting the new "Name2" field.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Copy and post the SQL of your make table query.

  14. #14
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    my make query does not work at all with your formula.

    This is probably due to the fact that I don't know how to use it properly.

    So I am sorry, but I can't get any SQL code to post it here.

    Please, could you let me know precisely how I should proceed?
    1. which kind of query should I create?
    2. what should I insert and where ?

    thank you

    Sorry for being so dumb regarding access...

  15. #15
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    this is the default SQL code for making a table which would be exactly the same as the input table I have :

    SELECT CORR1.ID, CORR1.Name INTO CORR2
    FROM CORR1;

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 11-03-2017, 08:20 AM
  2. Replies: 1
    Last Post: 12-04-2016, 05:43 PM
  3. Replies: 2
    Last Post: 01-03-2015, 07:18 PM
  4. Replies: 5
    Last Post: 04-23-2013, 01:42 PM
  5. update field with specific content
    By luxeon in forum Queries
    Replies: 2
    Last Post: 01-24-2011, 03:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums