INNER JOIN syntax problem 3

[nastar1]

The following SQL query is returning a reject for syntax errror in JOIN. Using the MS example, is not helping me understand if my use of parens are the reason or if I'm using the wrong FROM syntax. Any ideas why this is not processing?

SELECT tblWA.*, tblCLINRATE.Rate, tblNAMES.LastName, tblNAMES.OffSym, tblNAMES.Phone, tblNAMES_1.LastName, tblNAMES_1.OffSym, tblNAMES_1.Phone, tblNAMES_2.LastName, tblNAMES_2.OffSym, tblNAMES_2.Phone
FROM tblWA INNER JOIN
( tblCLIN INNER JOIN
( tblCLINRATE INNER JOIN
(tblNAMES INNER JOIN
(tblNAMES_1 INNER JOIN
tblNAMES_2
ON tblWA.CLIN = tblCLIN.CLINID)
ON (tblCLIN.CLINID = tblCLINRATE.CLINID) AND (tblWA.FY = tblCLINRATE.Year))
ON tblWA.POC = tblNAMES.EMPID)
ON tblWA.IssOff = tblNAMES_1.EMPID
ON tblWA.ProjOfcr = tblNAMES_2.EMPID;

 

[r937]

try like this --

SELECT ...
  FROM ((((
       tblWA 
INNER 
  JOIN tblCLIN 
    ON tblCLIN.CLINID = tblWA.CLIN
       )
INNER 
  JOIN tblCLINRATE 
    ON ( tblCLINRATE.CLINID = tblCLIN.CLINID 
     AND tblCLINRATE.Year = tblWA.FY )
       )
INNER 
  JOIN tblNAMES 
    ON tblNAMES.EMPID = tblWA.POC
       )
INNER 
  JOIN tblNAMES_1 
    ON tblNAMES_1.EMPID = tblWA.IssOff
       )
INNER 
  JOIN tblNAMES_2 
    ON tblNAMES_2.EMPID = tblWA.ProjOfcr

r937.com | rudy.ca

 

[n554386]

Your problem is that you don't define which fields to join on after the join statement.

 

[nastar1]

Thanks the syntax now passes however, I'm getting a Jet Error "Jet cannot find the input table tblNAMES_1".

The current query is below. Since tblNAMES_1 and tblNAMES_2 are aliases, am a violating some kind of Jet Engine rule about using them in this kind of query?

SELECT tblWA.*, tblCLINRATE.Rate, tblNAMES.LastName, tblNAMES.OffSym, tblNAMES.Phone, tblNAMES_1.LastName, tblNAMES_1.OffSym, tblNAMES_1.Phone, tblNAMES_2.LastName, tblNAMES_2.OffSym, tblNAMES_2.Phone
FROM ((((tblWA INNER JOIN tblCLIN ON tblCLIN.CLINID = tblWA.CLIN) INNER JOIN tblCLINRATE ON (tblCLINRATE.Year = tblWA.FY) AND (tblCLINRATE.CLINID = tblCLIN.CLINID)) INNER JOIN tblNAMES ON tblNAMES.EMPID = tblWA.POC) INNER JOIN tblNAMES_1 ON tblNAMES_1.EMPID = tblWA.IssOff) INNER JOIN tblNAMES_2 ON tblNAMES_2.EMPID = tblWA.ProjOfcr;

 

[r937]

perhaps this...

...
INNER 
  JOIN tblNAMES 
    ON tblNAMES.EMPID = tblWA.POC
       )
INNER 
  JOIN [b]tblNAMES AS tblNAMES_1[/b] 
    ON tblNAMES_1.EMPID = tblWA.IssOff
       )
INNER 
  JOIN [b]tblNAMES AS tblNAMES_2[/b] 
    ON tblNAMES_2.EMPID = tblWA.ProjOfcr

r937.com | rudy.ca

 

[nastar1]

r937,

Thanks that solved the Jet error.

However, I get no records selected now.

I should get 167 total records. One for every existing tblWA record.

And within each tblWA record, I'm trying to display the person's name, office and phone from the tblNAMES depending upon the tblWA 'employee' field. I have four fields in the tblWA that hold 'employee' foreign keys back to the tblNAMES. Perhaps my table structure and relationships is not correct using the alias's.

 

[PHV]

Replace the 3 INNER JOIN tblNAMES with LEFT JOIN tblNAMES

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nastar1]

PHV, the LEFT JOIN made all the difference in the world.

Thanks

R937, The 'AS' keyword also helped a great deal getting me on track.

Thanks to both of ya.