Easiest way to update tables (97 Sr2)

Topic

New Reply

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

Replies

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

Reply | Quote

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

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