Query > Build > no more place (1 Viewer)

Hello Friends;

I created a build in Query. The problem is, i need to define many things. For example :

Name: IIF([Table_name]![Field_name]="Dog";"Animal"; IIF([Table_name]![Field_name]="Cat";"Animal";IIF([Table_name]![Field_name]="Apple";"Fruit";IIF([Table_name]![Field_name]="Orange";"Fruit";IIF([Table_name]![Field_name]="Spinach";"Vegetable"; .......


it continues. At the end, there is no more place to define them. And min. i need to define 20 animals etc.

Is there a different way to do it ? Or simple way. I thought may be i can create new table and define there. but how can i use them in main table and query ? did not find a solution at this time.
I dont want to write any VBA code. Because i dont know anything about that.
Ty for your helps.

Use user Define function.

as i said no VBA
because i dont know how can i write there and use it in query etc.

sorry for that, another approach would be to add another field and write dog or animal, etc and include this field in your query.

another solution is to create another table with two fields (Description, Category) and put all your criteria there and Join the table in your query.

but how can i use them in main table and query ? did not find a solution at this time.

Click to expand...

That is just being lazy - what you want to do is what databases are all about and are provided as solutions in just about every post about query design.

This is a simplistic solution

create a new table called 'ItemTypes' with the following two columns

Item text
ItemType text

then populate with

Item.......ItemType
Dog........Animal
Cat.........Animal
Apple......Fruit
etc

Then in your query bring this table onto the design area and create a join between Field_Name and Item.

Finally drag ItemType down onto the query grid in replacement of 'Name'

Note 'name' is a reserved word so should not be used

Good luck with your project