Returning readable lookup value not integer value from table

  • Thread starter Thread starter Rey
  • Start date Start date
R

Rey

Howdy all.

Trying to research how you return the readable name, e.g. Joe Blow
instead of 8 from a field, e.g. claimspecialistID.

Have a large SQL statement where the recordset gets exported to Excel.
However the data in several fields is just numbers not Outbound, No
Correspondance, etc.

Appreciate your suggestions.

Thanks,

Rey
 
Sounds as though you may have used Lookup Fields in your tables. See
http://www.mvps.org/access/lookupfields.htm at "The Access Web" for why most
of us recommend not using them.

If that's what you've done, you're going to have to create a query that
joins your tables together in order to get the descriptions.
 
Thanks Doug for the reply.
Had previously run across the article but will reread...
Not only the form but the table fields have the lookup table fields
included. Then when I query I get the integer value not readable name.

In creating a query that includes the lookup table value not key, I'm
encountering issues...probably due to query...
I get an initial set of 5 records when not including lookup values but
after I substitute/join the first lookup table I get 3 records which
continues until I - presuming here as I have not checked on the actual
data - substitute a lookup table say WorkOutStatus then query states
that I'm about to add 0 records...

I'm presuming that I replace/substitute the lookup table value/
description field, e.g. ClaimSpecialistNames.ClaimSpecialistNm in
place of the C.ClaimSpecialistID so I get the individual's name
instead of the key/integer value associated w/individual's name in
table. Correct?

BTW, in generating the SQL statement via concatenating field names,
e.g. strSQL = "Select a.Fld1, a.Fld2" method, I encountered that on
viewing the entire SQL statement after it failed that several field
names where split across two lines...I had to add additional spaces to
have the field name not be split. I've not run across this previously
but then have not had a query composed of so many fields.

Is there a line length limitation that I'm encountering?

Thanks again for your help,

Rey
*********************************

Below is a sample query put together using Access query designer.

SELECT FC.CallID, FC.CertNoID, FC.CallDate, FC.CallTime,
FC.CallerName, FC.ContactStatusID, FC.Notes, BSD.BorrowersFullName,
C.CertNoID, C.Dear, ContactType.ContactType,
ClaimSpecialistNames.ClaimSpecialistNm,
WhoInitiatedContactList.WhoInitiatedContactList,
DoNoCallList.BorrDonotCallList, WWS.WorkingWServicerStatus,
WorkOutStatus.WorkOutStatus, WorkOutType.WorkOutType,
ContactStatus.ContactStatus, C.PendingLitigationFlag,
C.PendingLitigationType, C.PLAttorneyName, C.PLAttorneyPhone,
C.PLCaseNo, C.BankruptcyFileFlag, C.BKType, C.BKAttorneyName,
C.BKContactPhone, C.BKCaseNo, C.HomeListedForSaleFlag,
C.HFSUnderContract, C.HFSRealtorName, C.HFSRealtorPhone,
C.HFSListPrice, C.HFSListingNo, C.HFSClosingDate, C.CorrectWorkPhone,
C.CorrectHomePhone, C.CorrectMobilePhone, C.CorrectMailingAddress,
C.CorrectMailingCity, C.CorrectMailingStateOrProvince,
C.CorrectMailingPostalCode, C.AlternativeServicerContactrNm,
C.AlternativeServicerPhone, C.FollowupDate, C.FollowupCompletedDate, C.
[PMISavings$], C.RequestedPayOffAmt, C.BorrOutreachCompleted,
C.BorrEmailAddress, C.BorrBestTimeToCall, C.CoBorrBestTimeToCall,
C.CoBorrPhone, C.CoBorrEmailAddress, C.LastModifiedDate INTO
TestSubstitution
FROM (((((((((BorrowerData BSD INNER JOIN CustomerContacts AS C ON
BSD.CertNoID = C.CertNoID) INNER JOIN FollowupCalls FC ON C.ContactID
= FC.ContactID) INNER JOIN ContactStatus ON C.ContactStatustId =
ContactStatus.ContactStatustId) INNER JOIN ClaimSpecialistNames ON
C.ClaimSpecialistID = ClaimSpecialistNames.ClaimSpecialistID) INNER
JOIN WhoInitiatedContactList ON C.WhoInitiatedContactID =
WhoInitiatedContactList.WhoInitiatedContactId) INNER JOIN ContactType
CT ON C.ContactTypeID = CT.ContactTypeID) INNER JOIN
WorkingWServicerStatus WWS ON C.WorkingWServicerID =
WWS.WorkingWServicerID) INNER JOIN WorkOutStatus ON C.WorkoutStatusID
= WorkOutStatus.WorkOutStatusId) INNER JOIN WorkOutType ON
C.WorkoutTypeID = WorkOutType.WorkOutTypeId) INNER JOIN DoNoCallList
ON C.BorrReqDoNotCallID = DoNoCallList.BorrDonotCallId
WHERE (((FC.CallDate)=#4/7/2008#) AND ((FC.ContactStatusID)=9 Or
(FC.ContactStatusID)=11))
ORDER BY FC.CallDate;
 
Forgot to add to previous post how to return Yes or No when the lookup
value comes from a value list for the field.

Thanks,
Rey
 
I sorry, but I'm having problems understanding exactly what problem you're
describing.

I'll try to address what I do understand.
I'm presuming that I replace/substitute the lookup table value/
description field, e.g. ClaimSpecialistNames.ClaimSpecialistNm in
place of the C.ClaimSpecialistID so I get the individual's name
instead of the key/integer value associated w/individual's name in
table. Correct?
Click to expand...

Assuming you've added the ClaimSpecialistNames table to your query, that's
correct.
BTW, in generating the SQL statement via concatenating field names,
e.g. strSQL = "Select a.Fld1, a.Fld2" method, I encountered that on
viewing the entire SQL statement after it failed that several field
names where split across two lines...I had to add additional spaces to
have the field name not be split. I've not run across this previously
but then have not had a query composed of so many fields.

Is there a line length limitation that I'm encountering?
Click to expand...

Break your code into shorter lines.

strSQL = "Select a.Fld1, a.Fld2, " & _
"b.Fld1, c.Fld3, a.Fld3 " & _
"FROM a INNER JOIN b " & _
"INNER JOIN c " & _
"ON b.Fld1 = c.Fld2 " & _
"ON a.Fld1 = b.Fld2"

or

strSQL = "Select a.Fld1, a.Fld2, "
strSQL= strSQL & "b.Fld1, c.Fld3, a.Fld3 "
strSQL= strSQL & "FROM a INNER JOIN b "
strSQL= strSQL & "INNER JOIN c "
strSQL= strSQL & "ON b.Fld1 = c.Fld2 "
strSQL= strSQL & "ON a.Fld1 = b.Fld2"
[/QUOTE]

Thanks Doug for the reply.
Had previously run across the article but will reread...
Not only the form but the table fields have the lookup table fields
included. Then when I query I get the integer value not readable name.

In creating a query that includes the lookup table value not key, I'm
encountering issues...probably due to query...
I get an initial set of 5 records when not including lookup values but
after I substitute/join the first lookup table I get 3 records which
continues until I - presuming here as I have not checked on the actual
data - substitute a lookup table say WorkOutStatus then query states
that I'm about to add 0 records...

I'm presuming that I replace/substitute the lookup table value/
description field, e.g. ClaimSpecialistNames.ClaimSpecialistNm in
place of the C.ClaimSpecialistID so I get the individual's name
instead of the key/integer value associated w/individual's name in
table. Correct?

BTW, in generating the SQL statement via concatenating field names,
e.g. strSQL = "Select a.Fld1, a.Fld2" method, I encountered that on
viewing the entire SQL statement after it failed that several field
names where split across two lines...I had to add additional spaces to
have the field name not be split. I've not run across this previously
but then have not had a query composed of so many fields.

Is there a line length limitation that I'm encountering?

Thanks again for your help,

Rey
*********************************
 
Back
Top