Access - if then in query

I have a table in which I need to create categories from values in a column. For example, a column/field A has the following possible entries:

broccoli, spinach, beans, peas, cauliflower

orange, apple, pineapple, mangoes, cherries

fish, chicken, beef

I would like to create an if/in/then statement in a query to indicate that the first group above is "vegetable" the second group above is "fruit" and the third group is "meat."

Please advise on an efficient formatting for this. "Vegetable" "Fruit" "Meat will all be in a newly created column B called "Food Type" based on the values of Column A.
exp vgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
use the IIF() function

place this in a column of your query

Food Type: IIf([ColumnA] In ("Fish","Chicken","beef"),"meat",IIf([ColumnA] In ("orange","apple","pineapple", "mangoes", "cherries"),"fruit","vegetable"))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exp vgAuthor Commented:
Thank you.
Dale FyeOwner, Developing Solutions LLCCommented:
Rey's methods will work, but if you really want to normalize your database, you should:

Create 2 new tables:

1.  tbl_Categories, with fields CategoryID and CategoryName
2.  tbl_Item_Categories with fields:

ItemID, ItemName, CategoryID

Then, instead of storing the name of the item in your main table, store the ItemID.  

To identify the category, you would link tbl_Item_Categories to your main table based on the ItemID.  Then, if you need the category name, link that to tbl_Categories on the CategoryID field.
exp vgAuthor Commented:
Does the placement of , " spaces [ or ( matter in this statement - I keep getting wrong syntax error.
Rey Obrero (Capricorn1)Commented:
post the code you are using.

yes, the placing of the [] and () matters.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.