Using INNER JOIN with 3 tables

[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?

 

[Joe Fallon]

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.

 

[Chris Haas]

Where's the "PACEInfo" coming from in your error?

 

[Ken Tucker [MVP]]

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

 

[Herfried K. Wagner [MVP]]

I have an application that uses an Access database to
gather information on students' test scores. In the

Only FYI:

Group for ADO.NET questions:

<
Web interface:

<http://msdn.microsoft.com/newsgroup...roup=microsoft.public.dotnet.framework.adonet>