Help: Access -> ASP & displaying LookUp fields

I have an ASP page pulling data into a table on our web which displays employee information. One of the fields in the Access table I pull the information from is a lookup field referencing another table with the options in the database.

How do I write the SQL query so it returns the text value of the field to my ASP page instead of the numeric value of the lookup table?

i.e.

when I call the field in the table holding the values I get the JTID instead of the JobTitle...

JTID JobTitle
1 Public Service Assistant
2 Library Aide
3 Substitute PSA
4 Shelving Assistant
5 Reference Assistant

Can you post what your current query is? I'm not quite following how you have things set up.

<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim empPhoto 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim lngRecordNo 'Holds the record number to be updated

'Read in the record number
lngRecordNo = CLng(Request.QueryString("ID"))

'Create ADO con obj
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../fpdb/EmployeeDatabase.mdb")

'Create an ADO recordset object for detail pages
Set empPhoto = Server.CreateObject("ADODB.Recordset")

'Initialise SQL query
strSQL = "SELECT tblEmployeeData.*, tblJobTitlesLU.*, tblDeptLU.* FROM tblEmployeeData, tblJobTitlesLU, tblDeptLU WHERE EID=" & lngRecordNo

'Open the recordset with the SQL query
empPhoto.Open strSQL, adoCon
%>

I am having a difficult time following you, so I'll give you an example of what I think you are trying to do.

Suppose you have 3 tables - Employee table, which is a parent table to a table called management_employee and a table called worker_bees.

In the main employee table, I will have the following fields:
EID, last_name, first_name, mid-initial, street address, city, state, zip, phone, e_mail

In the table management_employee table, i have the following fields:
EID, office_no, ext_no, title, shift

And further suppose that there is similar infor in the worker_bees table.

If I were en employee in your company, and you wanted my information from the management_employee table, you'd have to do something like this:
select last_name, first_name, office_no, ext_no
from employee inner join management_employee
on employee.eid = management_employee.eid
where last_name = 'zimm'

I hope that helped. I'm not sure that that's what you were looking for.

Zimm,
table.* returns everything in that table.


sbelongia,
if you're trying to return employee's info, with their job titles and department number, you'll need to join all three tables, excuse the old syntax



if you're using mysql, oracle or if it just doesn't work, look up left join syntax on the *= syntax i used above. The left join says that lets say we have an employee and if for bad DB design reasons, they don't have a listing in Departments because maybe they're a new department or whatever reason, that row would be left out of the return. This left join syntax returns ALL employees no matter what. Let me know if you need more help.

Also, you need ' and a ' around constants in Tsql

un, I know. I didn't know how much SQL syntax the originator of the thread knew, so I was as explicit as I could be.

ah sou, wakattayo.