Translate Table Values in SQL

[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

 

[Steve Schapel]

Bryan,

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

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

 

[Guest]

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;

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

 

[Steve Schapel]

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.