• Easiest way to update tables (97 Sr2)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Easiest way to update tables (97 Sr2)

    Author
    Topic
    WSMelanieB
    AskWoody Lounger
    January 16, 2003 at 6:47 pm #381938

    One of my coworkers has a database with about 4K records in it. One of the fields is a drop down list that she selects from. This drop down is pulled from a table that holds the unique values for the drop down choices. If, for example, one of the list items it “Rpt for Tour” and I want to change it to “Report on January Tour”, how can I then get all the records that have that Rpt for Tour selection in their list boxes to update with the new one? I don’t really want to go through 4K records and find every record that has the field containing “Rpt for Tour” and manually change it to “Report on January Tour”. I am not vb literate, either, so if this takes code, please be specific. I was hoping I could update the drop down table and any record that used that field would change. That’s not working.

    Reply | Quote
    Viewing 2 reply threads
    Author
    Replies
    • WSCecilia
      AskWoody Lounger
      January 16, 2003 at 7:07 pm #645786

      The easiest way I can think of to do this is to run an update query on the table and update every record that has Rpt for Tour to Report for January Tour. Check out the help for update queries in Access. Remember to change your drop down choices afterward.

      If you’re just running a query on the table, you could do this:
      NewFieldName: IIF([FieldName]=”Rpt for Tour”,”Report for January Tour”,[FieldName])

      HTH,

      Cecilia smile

      Reply | Quote
    • WSDrew
      AskWoody Lounger
      January 16, 2003 at 7:24 pm #645793

      An update query is what you are looking for.

      However, I would suggest a different approach for your co-worker. Instead of having the actual text from the dropdown list go into your table, have it record a key.

      For example, here’s the ‘list’ table:

      1-Dog
      2-Cat
      3-Mouse

      Then, here’s your data table:

      Rover 1
      Cuddles 2
      Squeaky 3

      If later, you change the text for Dog, to Canine, you don’t have to change the data in other table, because the reference, through the key of ‘1’ remains the same.

      Drew

      Reply | Quote
    • WSdcardno
      AskWoody Lounger
      January 16, 2003 at 7:47 pm #645799

      I assume that in the table with 4K records you want to ensure that only “allowed” options are selected, and that they will be listed in the table that supplies the records to the drop down control. Go into the large table, and change the entry for “Rpt on January Tour” back to “Rpt for Tour” in any instance. Delete the entry for “Rpt on January Tour” in the small table that supplies the drop down control.
      In the relationship window, ensure that there is a relationship between the two tables with referential integrity enforced, and that it is set to “Cascade Update related Fields” (there is a check box beside it).
      Back in the small table, change the entry for “Rpt for Tour” to “Rpt on January Tour” – all instances in the larger table will be updated, as will any future changes in the descriptions.

      Reply | Quote
    Viewing 2 reply threads
    Reply To: Easiest way to update tables (97 Sr2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.