Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-27-2015, 11:59 PM   #1
DaRTHY
Newly Registered User
 
Join Date: Mar 2015
Posts: 90
Thanks: 1
Thanked 0 Times in 0 Posts
DaRTHY is on a distinguished road
Unhappy Query > Build > no more place

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.

DaRTHY is offline   Reply With Quote
Old 07-28-2015, 12:07 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query > Build > no more place

Use user Define function.
arnelgp is offline   Reply With Quote
Old 07-28-2015, 12:20 AM   #3
DaRTHY
Newly Registered User
 
Join Date: Mar 2015
Posts: 90
Thanks: 1
Thanked 0 Times in 0 Posts
DaRTHY is on a distinguished road
Re: Query > Build > no more place

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

DaRTHY is offline   Reply With Quote
Old 07-28-2015, 12:22 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query > Build > no more place

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.

Last edited by arnelgp; 07-28-2015 at 12:30 AM.
arnelgp is offline   Reply With Quote
Old 07-28-2015, 12:37 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Query > Build > no more place

Quote:
but how can i use them in main table and query ? did not find a solution at this time.
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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to build query kashif.special2005 Queries 0 03-01-2012 07:17 AM
Required Build Qty vs. Actual Build Qty - multiple tables new_2_prog Reports 0 06-10-2011 06:13 AM
Problem Query Wizard Build a select query by using tables with a many-to-many relatio fboehlandt Queries 15 09-08-2008 06:57 AM
Needs help to build query Jonny Queries 6 09-13-2007 02:48 AM
how to build the query amos Queries 5 08-13-2002 09:37 AM




All times are GMT -8. The time now is 11:58 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World