Multiple IIFs - There has got to be a better way.

[Dazed And Confused]

Lately I have been finding instances where I have several data items
that I need to name something else. For example, ages. If the
customer is between 18 and 21 put them in the group "18 To 21 Group"
etc.
I have been using nested IIFs in my queries to accomplish this. For
example:
AgeGroup:=IIF([Age]Between 18 and 21, "18 To 21 Group",IIF([Age]
Between 22 and 30,"22 To 30 Group","All Other")) And so on and so on.
*
I've also used them to group other things like Race for example.
RaceName:=IIF([Race]="A","Asian",IIF([Race]="WH","White",IIF([Race]="BL","Black","All
Other"))) and so on.
*
This works, but it sure gets to be cumbersome and time consuming. I
can't help but feel that there has got to be a better/simpler way of
doing this. Anyone have any ideas they would care to share. I sure
would appreciate it.
Thanks!
SJ

 

[Al Campagna]

Dazed,
Check out the Select Case statement in Access Help.
It may not save a lot of VB "finger-boning", but it certainly will be
more readable.

A couple of suggestions...
The best would be... create a function to determine the proper AgeDesc
for each possible Age value, and place it in an external module.
That function could be called whenever an Age (or Race) decision was
needed.
or
A lookup table of age values, and their corresponding textual
descriptions. That table could even be related to your data in queries to
generate the text desc.
or
This might be a case where adding a field to your base table... called
AgeText (text type) would make sense.
On the AfterUpdate event of Age, examine that value and determine the
text description, and update that field with the text. Then you never have
to IIF again.
(saving a calculated value is not generally recommended, but... if you have
lots of queries and reports and other forms based on your table, the AgeText
field could be justified).

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[BruceM]

Another option is the Switch function. Help (Access 2003) shows the use of
Switch in a user-defined function. Using RaceName it may go something like:
Switch([Race] = "A",Asian,[Race] = "WH","White")
A couple of points about the Age expression. "Between" works for criteria
in a query, but not in a function. Using Switch, you would need to do
something like:
Switch([Age] >= 18 And [Age] <= 21,"18 to 21 Group",[Age] <= 30,"22 to 30
Group")
Note that there is no need to specify a range after the first test, as
Switch stops at the first test that evaluates to True. Maybe something like
this would work:
Switch([Age] < 18 or Age > 30,"All Other",[Age] <=22, "18 to 22 Group")
However, if Age is a table field rather than a calculated field based on a
function that determines age from the date of birth, keeping the table
up-to-date will be quite difficult and error-prone. Here is a simple
function that can calculate age on the fly:
http://www.mvps.org/access/datetime/date0001.htm
This is not to argue against Select Case (a VBA option) or a lookup table,
which were among the options Al suggested, but rather to point out another
option. Only you have the full picture that will let you determine what
works best.
Remember, though, that if you use a function you don't need to specify a
range at each option. The principle I demonstrated for the Switch function
applies to Select Case and IIf, among others. As soon as the test is True,
the function stops checking.

 

[John Spencer]

Bruce,
One addition to your alternative solution would be to add the last option,
that is if no criteria are met earlier in the Switch then return a (defalut)
value.

Switch([Race] = "A",Asian,[Race] = "WH","White", TRUE, "All Other")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Another option is the Switch function. Help (Access 2003) shows the use
of Switch in a user-defined function. Using RaceName it may go something
like:
Switch([Race] = "A",Asian,[Race] = "WH","White")
SNIP

Lately I have been finding instances where I have several data items
that I need to name something else. For example, ages. If the
customer is between 18 and 21 put them in the group "18 To 21 Group"
etc.
I have been using nested IIFs in my queries to accomplish this. For
example:
AgeGroup:=IIF([Age]Between 18 and 21, "18 To 21 Group",IIF([Age]
Between 22 and 30,"22 To 30 Group","All Other")) And so on and so on.
*
I've also used them to group other things like Race for example.
RaceName:=IIF([Race]="A","Asian",IIF([Race]="WH","White",IIF([Race]="BL","Black","All
Other"))) and so on.
*
This works, but it sure gets to be cumbersome and time consuming. I
can't help but feel that there has got to be a better/simpler way of
doing this. Anyone have any ideas they would care to share. I sure
would appreciate it.
Thanks!
SJ

 

[BruceM]

Thanks. I was wondering how to handle "all others" with the Switch
function.

Bruce,
One addition to your alternative solution would be to add the last option,
that is if no criteria are met earlier in the Switch then return a
(defalut) value.

Switch([Race] = "A",Asian,[Race] = "WH","White", TRUE, "All Other")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Another option is the Switch function. Help (Access 2003) shows the use
of Switch in a user-defined function. Using RaceName it may go something
like:
Switch([Race] = "A",Asian,[Race] = "WH","White")
SNIP

Lately I have been finding instances where I have several data items
that I need to name something else. For example, ages. If the
customer is between 18 and 21 put them in the group "18 To 21 Group"
etc.
I have been using nested IIFs in my queries to accomplish this. For
example:
AgeGroup:=IIF([Age]Between 18 and 21, "18 To 21 Group",IIF([Age]
Between 22 and 30,"22 To 30 Group","All Other")) And so on and so on.
*
I've also used them to group other things like Race for example.
RaceName:=IIF([Race]="A","Asian",IIF([Race]="WH","White",IIF([Race]="BL","Black","All
Other"))) and so on.
*
This works, but it sure gets to be cumbersome and time consuming. I
can't help but feel that there has got to be a better/simpler way of
doing this. Anyone have any ideas they would care to share. I sure
would appreciate it.
Thanks!
SJ

 

[Guest]

I'd strongly endorse one of Al's suggestions, that each age group be stored
as a row in a separate AgeGroups table, which can be joined to your Customers
table. The reason I suggest this approach is that the AgeGroups are data,
and it’s a fundamental principle of the relational database model that data
be stored as values at column positions in rows in tables and in no other
way; its known as the 'Information Principle'. This principle is of more
than theoretical interest, however, but has a major practical benefit in that
data in tables can be edited via the interface, whereas if its hard coded in
functions any amendment requires changes to the code. So if you wanted to
change the parameters for the age groups all that's necessary is to edit the
AgeGroups table.

The AgeGroups table would have columns LowerAger, UpperAge and AgeGroup. So
you might have rows such as:

0 17 "Juvenile"
18 21 "18 to 21 group"
22 30 "22 to 30 group"
31 40 "31 to 40 group"
< and so on to for example >
80 150 "Other"

Assuming you are unlikely to have any customers older than 150 years of
course! You can then simply join the tables in a query, e.g:

SELECT FirstName, LastName, AgeGroup
FROM Customers INNER JOIN AgeGroups
ON (Customers.Age BETWEEN AgeGroups.LowerAge
AND AgeGroups.UpperAge);

NB: the parentheses in the ON clause are important when using a BETWEEN…AND
operation. You could of course GROUP BY AgeGroup if you want to aggregate
data per age group.

The same would apply for race, with a Races table containing columns Race
and RaceDescription with rows such as:

A "Asian"
WH "White"
<and so on>

In this case joining the tables would be much simpler, merely a case of
joining them on the Race columns in the usual way.

Ken Sheridan
Stafford, England