showing the Text in sted of ID

[Greg553]

hello, I know this is most likely an easy fix, just can't seem to find the answer.

I'm trying to get my query and report to show the sport name instead of the sport ID.

I am storing the Sport ID in the sport field . each sport has it's own ID.
when printing a report I want to show the sport not the sport ID.
I just can not figure how to get that to happen. I know i must join them some how but not able to figure out the code/expression.

 

[jrbarnett]

What is your query SQL code?

John

 

[Greg553]

SELECT [lname] & " " & [fname] AS Name, tbl_playerInformation.DateEntered, tbl_playerInformation.Grade, tbl_playerInformation.Height, tbl_playerInformation.Weight, tbl_playerInformation.Gender, tbl_playerInformation.Sport1, tbl_playerInformation.Position1, tbl_playerInformation.Player_ID
FROM tbl_playerInformation;


taken from the SQL view.
i know once i see it i will be able to duplicate the process...

 

[kjv1611]

If the Sport ID points to the SportID and SportName in a different table, then you need to include that table in your SQL/query.

So it'd be something like this, I imagine:

[TT]
SELECT   i.[lname] & "  " & i.[fname] AS Name, i.[DateEntered],
         i.[Grade], i.[Height], i.[Weight], i.[Gender], 
         [HIGHLIGHT]s.[Sport1][/HIGHLIGHT], i.[Position1], i.[Player_ID]
FROM     tbl_playerInformation i
              [HIGHLIGHT]INNER JOIN[/HIGHLIGHT]
         [HIGHLIGHT]tbl_Sports s[/HIGHLIGHT]
              [HIGHLIGHT]ON i.[Sport1] = s.[SportID][/HIGHLIGHT];
[/TT]

--

"If to err is human, then I must be some kind of human!" -Me

 

[Greg553]

this is what I have at the moment, still producing the ID instead of the actual sport


SELECT [lname] & " " & [fname] AS Name, tbl_playerInformation.DateEntered, tbl_playerInformation.Grade, tbl_playerInformation.Height, tbl_playerInformation.Weight, tbl_playerInformation.Gender, tbl_playerInformation.sport1, tbl_playerInformation.Position1
FROM tbl_sportIdentification INNER JOIN tbl_playerInformation ON tbl_sportIdentification.Sports_ID=tbl_playerInformation.sport1;

 

[PHV]

Replace this:
,tbl_playerInformation.sport1
with something like this:
tbl_sportIdentification.sport_name


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Greg553,

As both my and PHV's examples point out, you need to be referring to the actual field which stores the TEXT of your Sport in your SELECT clause. I highlighted the changes for that piece in my example above.

The only other change I gave was in using aliases for the table names, so as to shorten the code length, and make it easier to read.

--

"If to err is human, then I must be some kind of human!" -Me