Using INNER JOIN with 3 tables

  • Thread starter Thread starter Nathan
  • Start date Start date
N

Nathan

I have an application that uses an Access database to
gather information on students' test scores. In the
database there are three tables which are joined by one-
to-many relationships: Students, Subjects, and Tests. I
am trying to create a query that joins these three tables
and show all the subjects for only one student, and all
the tests taken in each of those subjects. This is the
query I have entered in the Query Builder:

SELECT Subjects.SubjectName, Subjects.SubjectID,
Tests.Score
FROM Students INNER JOIN
Subjects ON Students.StudentID =
Subjects.StudentID INNER JOIN
Tests ON Subjects.SubjectID =
Tests.SubjectID
WHERE (Students.Surname = '<Student'sSurname>') AND
(Students.FirstName = '<Student'sFirstName>')

(In the WHERE clause I have entered a specific student's
first name and surname.)

And I get the following error message:

Syntax error (missing operator) in query
expression 'Students.StudentID = Subjects.StudentID INNER
JOIN PACEInfo ON Subjects.SubjectID = PACEInfo.SubjectID'.

If I leave out the second INNER JOIN clause, everything
works fine. Is it possible to join three tables, and if
so, where am I going wrong?
 
It is possible.
You have a syntax problem.
Build the same query in the Access query window with 3 joined tables and
then view the SQL.
 
Hi,

You need to put the first inner join in brackets ().

SELECT Subjects.SubjectName, Subjects.SubjectID, Tests.Score
FROM ( Students INNER JOIN Subjects ON Students.StudentID =
Subjects.StudentID) INNER JOIN Tests ON Subjects.SubjectID = Tests.SubjectID
WHERE (Students.Surname = '<Student'sSurname>') AND (Students.FirstName
= '<Student'sFirstName>')

Ken
 
Back
Top