CODEWITHSUNDEEP
joinms-access-2010
Sinaesthetic
Sinaesthetic

Reputation: 12211

is there a better way to join multiple fields to the same table?

i have a table full of employee names and one the fields there is called 'initials'. I have another table that contains training records with 3 fields that reference the employee id in the employee table (trainer). I'm trying to write a query that will return a table that will display the initials of the appropriate person in the employee table for each field in the training record table. I'm having a hard time wrapping my head around this one and was hoping I could get some help. This is the test query I started with before the logic processor in my head overheated and shutdown:

SELECT sign1.initials, sign2.initials, sign3.initials
FROM record_trainingRecord tr 
INNER JOIN lut_employee sign1 ON (sign1.id = tr.sign1Trainer)
INNER JOIN lut_employee sign2 ON (sign2.id = tr.sign2Trainer)
INNER JOIN lut_employee sign3 ON (sign3.id = tr.sign3Trainer)

This was based on some stuff I was reading on the forums, but I'm getting a syntax error (missing operator). halp. tia

Upvotes: 0

Views: 1145

Answers (1)

Sinaesthetic
Sinaesthetic

Reputation: 12211

Ok so from everything I could gather, this is an issue with MS Access. I had to actually nest everything so this is really messy and pretty much makes me want to /wrists but it's working:

SELECT sign1.initials, sign2.initials, sign3.initials
FROM lut_docs docs
INNER JOIN (lut_employee sign1 INNER JOIN
             (lut_employee sign2 INNER JOIN
                (lut_employee sign3 INNER JOIN record_trainingRecord tr 
                 ON sign3.id = tr.sign3Trainer) 
              ON sign2.id = tr.sign2Trainer)
            ON sign1.id = tr.sign1Trainer)
      ON docs.docNum = tr.docNum
WHERE tr.id = ?

the docs was another part of my solution not related to my original post. But I wanted to make sure this was up to show as a solution for Access...

Upvotes: 1

Related Questions