Can I ask Access to automatically enter data in a field if a certain word is found...

[Charles618]

Good morning!


Here's my problem: I have a table with 715K records. Each record is an inventory product, and the sixth field of each record is a short description. The tenth field is a single-letter category designation, and is currently blank. What I want to do is search through the description field of every line, and where the word "Paint" is contained, enter a letter "P" in the category field.



Is this even possible? Thanks in advance for help. I'm completely stumped here.

[Bob Fitz]

Yes it should be possible using an update query. Search the forum and Google for details. There's lots of info out there. Here's one link that may help: http://office.microsoft.com/en-gb/ac...010076527.aspx Post back if you're still stuck.

[ItsMe]

With that many records it may be worthwhile to store the results of your search. However, if storing calculated data like this is in the interest of speed, you will need to incorporate a procedure to update the table each time a new record is added, a procedure to see if you need to write "P" to another column.

An alternative to storing the results would be to create an alias in a query. This new column would be a virtual column and the criteria would search the entire table each time the query is used.

An alias would look something like this.
MyAlais: IIf(InStr(tblName.FieldName, "Paint"), "P", "")