Replacing Cell Value in a Table

[tobert]

OK, here is my situation. I have an access database which is updated automatically every night with new data. My companies solution to this is each morning, the entire database is downloaded to my computer to be used by another application.

In one of the tables, there is a field that doesn't work for my purposes, so each day I have to manually open the table, and change the contents of that field for every record. For example, lets say I have the following table:

Field1 Field2 Field3 Field4
Rec1 Wigets Faucet Model1A
Rec2 Wigets Faucet Model1B
Rec3 Wigets Sink Model1A
Rec4 Wigets Sink Model1B
Rec5 Wigets Toilet Model1A

Each day I manually change Field2 so it reads "Total" and whatever is in Field2:

Field1 Field2 Field3 Field4
Rec1 Total Faucets Faucet Model1A
Rec2 Total Faucets Faucet Model1B
Rec3 Total Sinks Sink Model1A
Rec4 Total Sinks Sink Model1B
Rec5 Total Toilets Toilet Model1A

Each morning when I receive the new database download, they all go back to widgets.

Is there a macro or module I can create to automatically look at Field3, and then assign a new value to Field2 based on Field3's value?

Any help would be appreciated!!!!

[Rabbie]

Run an Update Query to change Field2 to "Total " & Field3

That should do what you want. For more information look up "Update Query" in Access Help

[tobert]

Originally Posted by Rabbie

Run an Update Query to change Field2 to "Total " & Field3

That should do what you want. For more information look up "Update Query" in Access Help

That does work, however, then everytime my database is updated, the Update Query is deleted....is there a way to turn my "Update Query" into an external file or an executable file that I can run each morning after my update?

[KeithG]

you could create another access db and link the table to the new db and write your query in the new db. This will work as long as the db is saved to the same location with the same name each morning.