Append Query Help

[MTSPEER]

Hello,



For example I have this field name which is FruitName, and I have 500 rows in this field with multiple different fruit names. For example apple CPN, orange SSD, pear ATS. And I created a new field called Category, the field is blank because I just made it. I want to write a query so whatever row has the text CPN, SSD, or ATS, it will display an "S" for SSD, "D" for CPN, and "D" for ATS in the Category field. I'm guessing an append query needs to be created to make this work. If someone can help me out and which is the best way to do it let me know. Thank you.

[aytee111]

This would be an Update query to update the new Category field for each row.

If this is a one-off deal, easiest would be to keep using the same query and do them one by one (create the query, run it, change the values, run it, etc), otherwise it would take a complicated "IIf" statement. Bring in the FruitName field and in the criteria line put - Like "*CNP*", then update the Category field to "S". Etc.

[MTSPEER]

There's about a hundred different Fruit Names. I can't go one by one changing each fruitname to "S" or "D".

[aytee111]

Are you saying there are only three types? If so, the fruit field can be displayed as Right(FruitName,3) and then in the criteria row when it is CPN or ATS update the category to "D" (run query), second time change it to "SSD" and "S" (run query). Your category should really belong in a different table, your fruitnames should also belong in their own table, so there are always one of each. I don't understand why you have 500 rows.

[MTSPEER]

theres a hundred different types of fruit..but hey all end with SSD, CPN, ATS.