Map code to label in query

  • Thread starter Thread starter Attila Fust
  • Start date Start date
A

Attila Fust

I am running a query that groups an indicator code. I
would like to label the code. Here are the results of the
query:

Code Sum
1 3500.00
2 2500.00
3 1000.00

The code field is grouped "Group by" in the query.

I would like to map the code to an english name. Here is
what I would like to see:

Code Sum Name
1 3500.00 Member
2 2500.00 Spouse
3 1000.00 Dependent

I could create a separate table with that identifies the
name for each code and then join to my original table but
I would like to avoid that if possible.

Is there a way to do this in the query?

Thanks in advance.

Attila
 
If you only have 3 codes, you can just use something like
the following as the field source:

Name: iif(Code = 1,"Member",iif(Code = 2,"Spouse",iif
(Code = 3,"Dependent","")))

HTH, Ted Allen
 
Or you could use the Switch function.

Name: Switch(
Code: 
 = 1, "Member", [Code] = 2, "Spouse", [Code = 3],
"Dependent")

But I personnally prefer to have the separate table.  That way, I always
know how and where to make changes.  If you hard code this, you will have to
go looking for it when you need to add a 4th code.

Dale
 
You can implement the suggestion given by Ted Allen and it will work, but in my
opinion you are almost always better off adding the translation table. It
becomes very easy to handle any new code values, just add one record to one
table. Or if someone decides that "Spouse" should be "Significant Other" or
"Life Partner" or "Dependent" should be "Child" or "Household Minor", again you
edit one record.

If you use the hard-coding method, you have to locate every instance where you
have coded this conversion and make the change. Plus if you end up with more
than 3 or 4 values, the code gets very cumbersome to read.

Just my thoughts, take them for what they cost you.
 
Back
Top