case formula to rename columns

[rrmcguire]

Is it possible to rename columns for instance if I have a field called reason_for_leaving with the possible selections being AGT,AL, ATT, BKS, DIS, DL,DTI,EOB,FOL,FR

can I have it so if reason_for_leaving = 'AGT' then 'Agent and Driver Left' else if reason_for_leaving = 'AL' then 'Agent Left', etc.

 

[dhookom]

It doesn't look like you are renaming columns. I would expect that renaming a column would simply be creating an alias for the column named "reason_for_leaving". If you simply want to substitute the displayed value in the column you should have a small table of reason codes and titles.

[pre]ReaCode ReaTitle
AGT Agent and Driver Left
AL Agent Left
ATT ...
BKS ...[/pre]

Then all you need to do is add the small table to your query and join the code fields to display the ReaTitle rather than the ReaCode.


Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

In your "reason_for_leaving" column I would not keep the abbreviations, I would keep the number (set up as FK) that would refer to a number ID (set up as PK) in another table:

[pre]
ID ABR DESCRIPTION
1 AGT Agent and Driver Left
2 AL Agent Left
3 ATT .....
4 BKS
5 DIS
6 DL
7 DTI
8 EOB
9 FOL
10 FR
[/pre]

No need for Case statement, you just join 2 tables and display whet you want.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[rrmcguire]

the reason_for_leaving is the abbreviation, but we're needing to have when one of the abbreviation codes is on a record have it display the appropriate description, i.e. AGT = 'Agent and Driver Left"

We're using this to display a bar chart where we have a count set for the number of records as our Y-axis, then the reason_for_leaving as our X-axis, but they're wanting to display the description and not the abbreviation

thanks for any help provided.

 

[dhookom]

rrmguire,
Did our nudge work for you or not? I totally agree with Andy and don't think our answers would change based on your most recent reply.

Duane
Hook'D on Access
MS Access MVP

 

[rrmcguire]

Hi Duane,

The ReaTitle doesn't exist though as a field, and I've already brought all the fields in just needing a calculated field which based on the abbreviation displays the description or ReaCode to ReaTitle

thanks

 

[Andrzejek]

If you do not like our approach, you may go the long route and do something like:

[pre]
Select ... ,
CASE reason_for_leaving
WHEN 'AGT' THEN 'Agent and Driver Left'
WHEN 'AL' THEN 'Agent Left'
...
END As XYZ
From ...
[/pre]
But this way you need to cover all the cases. And if/when a new case is introduced, or the description changes (and they will change no matter what they tell you) you need to modify your code.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Olaf Doschke]

Indeed, take Andys last sketched sample code as the solution, then you have your computed column containing the expanded long name.
But why do we do databases? To store data. What is each expanded reason text of the reason codes else, but data?

If you're not the database owner and have to live with the content as is, and you just read and report this data, then make yourself a database owner, create your own extra database, where you maintain such meta data things. You don't do this translation in a length CASE statement. If you'd do this in code at all, then only in inheritable code, in classes, but SQL is not object oriented, it's commands. So the next query you do that also makes the same translations cannot inherit this CASE statement, you copy it. One day you have 10 queries all using this lengthy CASE statement and then you want to add one three letter code and its long name and will need to add it in 10 places instead of adding one record in your data, your data you now are free to create in whatever database server.

There is no excuse like you're not maintaining and controlling this. You create a new database if needed.

Bye, Olaf.

 

[dhookom]

Here is a favorite blog post from Jeff Smith that supports what we have been trying to convince you Data Belongs in Your Tables - Not in Your Code.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

You also can get 'fancy' and create yourself a Function which will accept reason_for_leaving and return the full description of it.

Your statement would look something like:

[pre]
Select ... ,
FunctionNameHere(reason_for_leaving) As XYZ
From ...
[/pre]
And your Function would have the same CASE statement in one place. So if you use this Function in 10 queries, you have "to add one three letter code and its long name and will need to add it in 10 [one] places"

But that is still a work-around to the 'proper' relational data base approach.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Olaf Doschke]

Indeed, Andy. But if rrmcguire isn't allowed to add a table in this database, it's unlikely permissions allow adding a user defined table valued function.
And if you start this, you're ending up as someone else here needing help with queries using a massive set of functions and views based on further functions and views and the underlying structure is completely hidden.

Bye, Olaf.

 

[rrmcguire]

no, I am not the database owner. Basically I'm taking someone else's data and then creating a bar chart from this which is doing a count of the number of records which have each reason code but the people I'm producing this for don't want the reason codes displayed as the X-Axis they want the descriptions associated with them.

thanks for all help provided

 

[Andrzejek]

So, did any of the suggestions here work for you?
Did you try any of them?
Are you happy with the outcome? Or are you still looking for a solution?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[rrmcguire]

I think I'm going to need to write the codes out in the case statement. So what do you put after 'END' and is from the table you're taking the fields from?

Thanks

 

[Andrzejek]

So what do you put after 'END'"
Did you even try my suggestion? If you did, you would know the answer.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[rrmcguire]

when you're putting the select statement what am I supposed to put after it?

Select ... ,
CASE reason_for_leaving
WHEN 'AGT' THEN 'Agent and Driver Left'
WHEN 'AL' THEN 'Agent Left'
...
END As XYZ
From ...



the field 'reason_for_leaving' has multiple choices AGT, AL, ATT....then for each of those there is a description i.e. for AGT is AGent & Driver Left, AL is Agent Left, and what I'm trying to display is those descriptions.

 

[rrmcguire]

I have it writer as the below

select
(CASE `reason_for_leaving`
when 'AGT' THEN 'Agent and Driver Left'
when 'AL' THEN 'Agent Left'
when 'ATT' THEN 'Terminated due to Attitude'
when 'BKD' THEN 'Breakdown-Cant afford cost to fix'
when 'DIS' THEN 'Disagreements between truck owner'
when 'DL' THEN 'Driver Left'
when 'DTI' THEN 'Drug Test Issues'
when 'EOB' THEN 'EOBR Refusal'
when 'FOL' THEN 'Follow Up'
when 'FR' THEN 'Freight Refusal'
when 'MED' THEN 'Out due to Medical Reasons'
when 'MIA' THEN 'Unknown Cant Contact Driver'
when 'PAY' THEN 'Driver not getting paid by truck owner'
when 'PER' THEN 'Personal Issues to take care of'
when 'PRF' THEN 'Low Profits'
when 'REP' THEN 'Truck Repo'
when 'RET' THEN 'Retiring'
when 'SAF' THEN 'Terminated due to Safety Violations'
when 'TRR' THEN 'Trailer Rent'
when 'ZER' THEN 'left due to $0 checks'
)
END
From Driver


but it doesn't seem to be working

 

[Olaf Doschke]

Yes, you can't have CASE inside brackets and END outside. Simply remove the brackets.

Bye, Olaf.

 

[rrmcguire]

ok, so within the software we have I have to write it as such

CASE
when `reason_for_leaving`='AGT' THEN 'Agent and Driver Left'
when `reason_for_leaving`='AL' THEN 'Agent Left'
when `reason_for_leaving`='ATT' THEN 'Terminated due to Attitude'
when `reason_for_leaving`='BKD' THEN 'Breakdown-Cant afford cost to fix'
when `reason_for_leaving`='DIS' THEN 'Disagreements between truck owner'
when `reason_for_leaving`='DL' THEN 'Driver Left'
when `reason_for_leaving`='DTI' THEN 'Drug Test Issues'
when `reason_for_leaving`='EOB' THEN 'EOBR Refusal'
when `reason_for_leaving`='FOL' THEN 'Follow Up'
when `reason_for_leaving`='FR' THEN 'Freight Refusal'
when `reason_for_leaving`='MED' THEN 'Out due to Medical Reasons'
when `reason_for_leaving`='MIA' THEN 'Unknown Cant Contact Driver'
when `reason_for_leaving`='PAY' THEN 'Driver not getting paid by truck owner'
when `reason_for_leaving`='PER' THEN 'Personal Issues to take care of'
when `reason_for_leaving`='PRF' THEN 'Low Profits'
when `reason_for_leaving`='REP' THEN 'Truck Repo'
when `reason_for_leaving`='RET' THEN 'Retiring'
when `reason_for_leaving`='SAF' THEN 'Terminated due to Safety Violations'
when `reason_for_leaving`'TRR' THEN 'Trailer Rent'
when `reason_for_leaving`='ZER' THEN 'left due to $0 checks'

END

 

[Olaf Doschke]

Is this a MySQL database?

There is a = missing in the second last line.

Bye, Olaf.

Edit: I asked because backticks are a MySQL thing. You are just lucky the syntax there is very similar for the CASE statement.