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.Subjec tName, Subjects.Subjec tID,
Tests.Score
FROM Students INNER JOIN
Subjects ON Students.Studen tID =
Subjects.Studen tID INNER JOIN
Tests ON Subjects.Subjec tID =
Tests.SubjectID
WHERE (Students.Surna me = '<Student'sSurn ame>') AND
(Students.First Name = '<Student'sFirs tName>')

(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.Stude ntID = Subjects.Studen tID INNER
JOIN PACEInfo ON Subjects.Subjec tID = PACEInfo.Subjec tID'.

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.
--
Joe Fallon

"Nathan" <an*******@disc ussions.microso ft.com> wrote in message
news:00******** *************** *****@phx.gbl.. .

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.Subjec tName, Subjects.Subjec tID,
Tests.Score
FROM Students INNER JOIN
Subjects ON Students.Studen tID =
Subjects.Studen tID INNER JOIN
Tests ON Subjects.Subjec tID =
Tests.SubjectID
WHERE (Students.Surna me = '<Student'sSurn ame>') AND
(Students.First Name = '<Student'sFirs tName>')

(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.Stude ntID = Subjects.Studen tID INNER
JOIN PACEInfo ON Subjects.Subjec tID = PACEInfo.Subjec tID'.

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?

[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.Subjec tName, Subjects.Subjec tID, Tests.Score
FROM ( Students INNER JOIN Subjects ON Students.Studen tID =
Subjects.Studen tID) INNER JOIN Tests ON Subjects.Subjec tID = Tests.SubjectID
WHERE (Students.Surna me = '<Student'sSurn ame>') AND (Students.First Name
= '<Student'sFirs tName>')

Ken
----------------------
"Nathan" <an*******@disc ussions.microso ft.com> wrote in message
news:00******** *************** *****@phx.gbl.. .

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.Subjec tName, Subjects.Subjec tID,
Tests.Score
FROM Students INNER JOIN
Subjects ON Students.Studen tID =
Subjects.Studen tID INNER JOIN
Tests ON Subjects.Subjec tID =
Tests.SubjectID
WHERE (Students.Surna me = '<Student'sSurn ame>') AND
(Students.First Name = '<Student'sFirs tName>')

(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.Stude ntID = Subjects.Studen tID INNER
JOIN PACEInfo ON Subjects.Subjec tID = PACEInfo.Subjec tID'.

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?

[Herfried K. Wagner [MVP]]

* "Nathan" <an*******@disc ussions.microso ft.com> scripsit:

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:

<news://msnews.microsof t.com/microsoft.publi c.dotnet.framew ork.adonet>

Web interface:

<http://msdn.microsoft. com/newsgroups/default.asp?url =/newsgroups/loadframes.asp? icp=msdn&slcid= us&newsgroup=mi crosoft.public. dotnet.framewor k.adonet>

--
Herfried K. Wagner [MVP]
<http://www.mvps.org/dotnet>