Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Easiest way to update tables (97 Sr2)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Easiest way to update tables (97 Sr2)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Easiest way to update tables (97 Sr2)

    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

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easiest way to update tables (97 Sr2)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •