Translate Table Values in SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,

There is a field in my table called ContactType. The values are 1, 2, and
3. I want to translate these values to the English equivalents during a SQL
query.

I used to be an Oracle developer and in Oracle SQL I would use the built in
function 'decode' to accomplish this task.

For example:

Select decode(ContactType, 1, "phone", 2, "fax", 3, "email)
From Contacts;

Is their an equivalent function which I can call in Access? I'm looking for
the simplest solution.

Thanks for your feedback.

Regards,
Bryan
 
Bryan,

In this case, the applicable function in Access is Choose()...

SELECT Choose([ContactType],"phone","fax","email") AS TextType
FROM Contacts
 
It appears that [ContactType] is a yes/no datatype in the table. Do I need
to convert to an integer in order for choose() to work? Right now choose()
is returning null.

Here is the sql:
SELECT Users.UserName, ContactAdd.ContactUserCredentials,
DateValue([ContactAddDateTime]) AS ContactDate, Patients.PatientPerinetID,
Patients.PatientLastName, Patients.PatientFirstName,
choose([ContactAdd.ContactType], "Scheduled", "Unscheduled") AS ContactType,
ContactAdd.ContactMode, ContactAdd.ContactAddContactMeans,
ContactAdd.ContactAddResult, ContactAdd.ContactAddDispositionID,
ContactAdd.ContactAddUnsuccessful, ContactAdd.ContactAddUnsuccessfulOther,
ContactAdd.ContactAddReasonAssessment, ContactAdd.ContactAddReasonEquipment,
ContactAdd.ContactAddReasonTargetViolation,
ContactAdd.ContactAddReasonCrisis, ContactAdd.ContactAddReasonNonCompliance,
ContactAdd.ContactAddReasonInstructions,
ContactAdd.ContactAddReasonReturnPatientCall,
ContactAdd.ContactAddReasonCallFromPatient,
ContactAdd.ContactAddReasonInventory, ContactAdd.ContactAddReasonOther
FROM Patients INNER JOIN (ContactAdd INNER JOIN Users ON ContactAdd.UserID =
Users.UserId) ON Patients.PatientID = ContactAdd.PatientID;




Steve Schapel said:
Bryan,

In this case, the applicable function in Access is Choose()...

SELECT Choose([ContactType],"phone","fax","email") AS TextType
FROM Contacts

--
Steve Schapel, Microsoft Access MVP

Greetings,

There is a field in my table called ContactType. The values are 1, 2, and
3. I want to translate these values to the English equivalents during a SQL
query.

I used to be an Oracle developer and in Oracle SQL I would use the built in
function 'decode' to accomplish this task.

For example:

Select decode(ContactType, 1, "phone", 2, "fax", 3, "email)
From Contacts;

Is their an equivalent function which I can call in Access? I'm looking for
the simplest solution.

Thanks for your feedback.

Regards,
Bryan
Click to expand...
Click to expand...
 
Bryan,

Yes, the Choose() function relates to an integer field, so would be
correct for your original example. For your second example, these
should all work...

Choose(Abs([ContactType])+1,"Unscheduled","Scheduled")
Switch([ContactType]=True,"Scheduled",[ContactType]=False,"Unscheduled")
IIf([ContactType],"Scheduled","Unscheduled")

It would be inadvisable to alias the calculated field as ContactType, it
should be different from the name of the field.
 
Back
Top