Hi
I'm new here so sorry if this is already covered somewhere.
Anyway, I'm trying to create a new recordset in Dreamweaver CS6. After a lot of googling and hours of trying, I almost have it working.
There are 3 tables linking into my new table. However 2 of the columns are filled from the same table depending on their role in that table. I'll try to explain a bit more.
I'm creating a database for job interviews. In the interview table there is a InterviewID, InterviewDate, TitleOfPost (titles come from another table), Hospital (Hospital name comes from another table) and then there is a Chairperson and a Secretary. These last 2 come from a 'Boardmembers' table and within that there is a Role field. The 2 options in that are Chairperson and a Secretary.
I'm trying to create the Interview table and I have this so far:
SELECT InterviewID, InterviewDate, Post, HospitalName, Boardmembername AS Chairperson, BoardmemberName AS Secretary FROM Interview_tbl
INNER JOIN TitleOfPost_tbl ON TitleOfPost_tbl.PostID = Interview_tbl.TitleOfPost
INNER JOIN Hospital_tbl ON Hospital_tbl.HospitalID = Interview_tbl.Hospital
INNER JOIN Boardmember_tbl ON Boardmember_tbl.BoardmemberID = Interview_tbl.Chairperson
This all works and displays fine, however I can't get the last bit of information (the name of the secretary) to display. Seems to be a problem because the Chairperson and Secretary details come from the same table.