Changing the format from numerical value to text value in an SQL select query

Question

In the string below, I am trying to convert the value 'tblContacts.JobTitle from a numerical value to a text value without having to link my query to the table where the original record ('tblJobTitles.JobTitle') sits. The field 'JobTitle' in the table 'tblContacts' pulls it's value from the table 'tblJobTitles'. When I use the query below, unfortunately the query extracts the unique ID number of the job title instead of the text value. Is there to reformat the select query to do so? The below is my code where I thought that you could format the value as text by adding '.text' but unfortunately this doesn't work.

 sql_get = "SELECT [tblContacts].[CompleteName], [tblContacts].[CurrentPosition], [tblContacts].[Level], [tblContacts].[ContractType], [tblContacts].[ID], [tblContacts].[Foto], [tblTeams].[Team], [tblJobTitles].[JobTitle] FROM [tblContacts] INNER JOIN [tblTeams] ON [tblContacts].[Team] = [tblTeams].[ID] LEFT JOIN [tblJobTitles] ON [tblJobTitles].[ID] = [tblContacts].[JobTitle] WHERE [tblTeams].[team] = '" & cboDepartments.Value & "'"
Me.frmstaticdatadepartments08.Form.RecordSource = sql_get

Mackan · Accepted Answer · 2015-05-01 23:43:34Z

1

As I understand it tblContacts.JobTitle is a foreign key. The actual job title sits in another table, tblJobTitles. The only way to get it is to ask for it, either by a sub-select or by joining in the other table.

SELECT tblContacts.CompleteName, tblContacts.CurrentPosition, tblContacts.Level, 
tblContacts.ContractType, tblContacts.ID, tblContacts.Foto, 
tblTeams.Team, tblJobTitles.JobTitle 
FROM tblContacts 
LEFT JOIN tblJobTitles ON tblContacts.JobTitle = tblJobTitles.Id
INNER JOIN tblTeams ON tblContacts.Team = tblTeams.ID 
WHERE [tblTeams].[team] = '" & cboDepartments.Value & "'"

I had to guess on the some column names, tblJobTitles.Id and tblJobTitles.JobTitle, but I hope you get my meaning.

The change I did was to join in tblJobTitles and then ask for the title instead of the reference id, tblJobTitles.JobTitle.

Edit:

Apparently Access requires parentheses for multiple joins - see msdn:

In cases where you need to join more than one table, you can nest the INNER JOIN clauses.

So do this instead:

SELECT tblContacts.CompleteName, tblContacts.CurrentPosition, tblContacts.Level, 
tblContacts.ContractType, tblContacts.ID, tblContacts.Foto, 
tblTeams.Team, tblJobTitles.JobTitle 
FROM (tblContacts 
INNER JOIN tblTeams ON tblContacts.Team = tblTeams.ID)
LEFT OUTER JOIN tblJobTitles ON tblContacts.JobTitle = tblJobTitles.Id
WHERE [tblTeams].[team] = '" & cboDepartments.Value & "'"

edited May 1, 2015 at 23:43

answered May 1, 2015 at 22:29

Mackan

6,2712 gold badges29 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Marchese Il Chihuahua Over a year ago

Thanks Mackan. I am getting a syntax error message. Is it possible that you have missed a set of partentheses?

Mackan Over a year ago

@MarcheseIlChihuahua Looking over your query you posted in comments, one thing is not correct: [tblJobTitles].[JobTitle] = [tblContacts].[JobTitle] These two columns can't be bound together (I assume). You need the ID-field from tblJobTitles, not the JobTitle (the Jobtitle goes in the Select). See my original answer for differences

Mackan Over a year ago

LEFT JOIN is a precaution since I don't know if I could use INNER. LEFT will make sure that all records are returned from Contacts, even if they have job titles or not. With an inner join only those with job titles would be returned

Marchese Il Chihuahua Over a year ago

OK thanks for all your help.. By the way, this query works. Perhaps looking at this you could see the error:

Mackan Over a year ago

@MarcheseIlChihuahua Yes, that brought a bit of clarity. See my update in answer. Nothing I've ever run into before though. Also changed it to LEFT OUTER JOIN

|

Collectives™ on Stack Overflow

Changing the format from numerical value to text value in an SQL select query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related