CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Marchese Il Chihuahua
Marchese Il Chihuahua

Reputation: 1139

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

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

Upvotes: 1

Views: 167

Answers (1)

Mackan
Mackan

Reputation: 6271

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 & "'"

Upvotes: 1

Related Questions