Calling a related field

[sage703]

I'm trying to display the textual value of a record rather than it's numeric
value, since the numeric value is just an ID. The main table I'm basing my
display results on shows the textual value of that particular field in each
record in the DB, but for some reason when I call it using the #FieldName#
syntax I get the ID number from the table its related to! Any ideas?

[mxstu]

Are you using Access?

[sage703]

Yes, I'm using Access for now. Later we'll be porting to SQL Server.

[mxstu]

It sounds like you're talking about how the information is displayed when you
open up Access. Access allows you to format the "display" of information
without necessarily changing the underlying data or table structure. For
example, if you have two tables
Customer [ CustomerID, CustomerName ]
CustomerAccount [CustomerAccountID, CustomerID, AccountNumber]

Access allows you to format the information so that when you open the
[CustomerAccount] table, it displays the "CustomerName" instead of the numeric
"CustomerID". This type of formatting has no effect on the underlying table.
CustomerID is still a number. If you want to retrieve the "CustomerName" you
would need to do a JOIN with that table:

SELECT ca.CustomerName -- retrieve customer name not ID
FROM Cusotmer c, CustomerAccount ca
WHERE ca.CustomerAccountID = 1 AND
ca.CustomerID = c.CustomerID

[sage703]

Thanks mxstu, but I really want to know how to display the information
correctly using Coldfusion. Are you suggesting I need to make a Join in Access
before I can display the text-version correctly with Coldfusion?

[mxstu]

Without knowing your table structure, it's hard to say. I'm just guessing what
you mean by "text-version" . Maybe you can post a simple example of what the
current output is and what you would like it to be. Be sure to include table
structure and I'm sure someone can help.

[sage703]

Here's the Coldfusion query I use to get the specific record the user selected
in a previous page:

<cfquery name="programs" datasource="#data_source#">
SELECT ID, Program_Name, Provider_Name_Short, School, Program_Web,
Program_Description,
Location_City, Location_State, Program_Notes, Program_Contact,
Contact_Email,
Contact_Phone, Distance, Degree_Level, Attendance_Options,
Length_of_Program_Years,
Length_of_Program, Occupational_Information, Rural_Opportunities,
Admission_Prerequisites,
Rural, Focus_Program
FROM Healthcare_Catalog
WHERE Program_Name='#URL.Program_Name#'
</cfquery>

Now, for example, to get the 'Program Name', I just code this:

<p>
<b>#Program_Name#</b>
</p>

However, to get the name of the School associated with that record, I used
this:

<b>School: </b> #School#

Unfortunately, this code gives the result (displayed to the user):
School: 13 <!--- or some other number --->

This is not intuitive, because what does '13' mean, right? So I want it to
display the School associated with the ID value of 13, not the actual ID
number.

For example, the school might be UAA, which means more than 13. However, 13
might be the ID number in the 'Schools' table that references the unique value
UAA.

Ok, so here's how each of my tables looks:

Schools Table:
ID = primary key
School = text field

Healthcare Catalog Table (that I'm accessing in the Coldfusion page - see
above):
Program_Name = primary key
School = number field <!--- related to ID field in Schools table

The weird thing is that the Schools field in the Healthcare table is a number
field yet has values such as this: 'UAA - College of Health and Social Welfare'

I didn't create this table fyi, I'm just making the forms.

Does this help?

[mxstu]

The weird thing is that the Schools field in the Healthcare table is a number
field yet has values such as this: 'UAA - College of Health and Social Welfare'

Yes, like I said, Access allows you to format how information is displayed
when you view a table through the Access application, but this doesn't change
the underlying structure (i.e. you "see" a name when you open the table, but
the column values are still number IDs). You can always get rid of the
formatting if it's causing you problems.

That does help. It would seem that the column [Healthcare_Catalog].[School]
contains the school ID and not the name. Of course
([Healthcare_Catalog].[SchoolID] probably would have been a more intuitive name
;-). Anyway, you need to do a JOIN with the [School] table to get the name of
the school. Something like

--- Not tested
SELECT hcc.ID, hcc.Program_Name, hcc.Provider_Name_Short, s.School,
...(other fields)...
FROM Healthcare_Catalog hcc, School s
WHERE hcc.Program_Name='#URL.Program_Name#' AND
hcc.School = s.School

Also, assuming each program name is unique in this table, your queries would
probably be faster if you searched on the [Healthcare_Catalog].[ID] instead of
the [Program_Name].

[sage703]

Thank you mxstu, I think I got it finally!

S./