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:
facilID -> a unique autonumber to keep track of individual teachers facilLname -> the Last name of the facilitator facilFname -> the First name of the facilitator
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.
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.