I've got two tables in MS Access that keep track of class facilitators and the classes they facilitate. The two tables are structured as follows:
tbl_facilitators
facilID -> a unique autonumber to keep track of individual teachers
facilLname -> the Last name of the facilitator
facilFname -> the First name of the facilitator
tbl_facilitatorClasses
classID -> a unique autonumber to keep track of individual classes
className -> the name of the class (science, math, etc)
primeFacil -> the facilID from the first table of a teacher who is primary facilitator
secondFacil -> the facilID from the first table of another teacher who is backup facilitator
I cannot figure out how to write an Inner Join that pulls up the results in this format:
Column 1: Class Name
Column 2: Primary Facilitator's Last Name
Column 3: Primary Facilitator's First Name
Column 4: Secondary Facilitator's Last Name
Column 5: Secondary Facilitator's First Name
I am able to pull up and get the correct results if I only request the primary facilitator by itself or only request the secondary facilitator by itself. I cannot get them both to work out, though.
This is my working Inner Join:
SELECT tbl_facilitatorClasses.className,
tbl_facilitators.facilLname, tbl_facilitators.facilFname
FROM tbl_facilitatorClasses
INNER JOIN tbl_facilitators
ON tbl_facilitatorClasses.primeFacil = tbl_facilitators.facilID;
Out of desperation I also tried a Union, but it didn't work out as I had hoped. Your help is greatly appreciated. I'm really struggling to make any progress at this point. I don't often work with SQL.
SOLUTION
Thanks to @philipxy I came up with the following query which ended up working:
SELECT tblCLS.className,
tblP.facilLname, tblP.facilFname, tblS.facilLname, tblS.facilFname
FROM (tbl_facilitatorClasses AS tblCLS
INNER JOIN tbl_facilitators AS tblP
ON tblCLS.primeFacil=tblP.facilID)
INNER JOIN tbl_facilitators AS tblS
ON tblCLS.secondFacil=tblS.facilID;
When performing multiple Inner Joins in MS Access, parenthesis are needed...As described in this other post.
Question&Answers:os