Help with INNER JOIN error 1

[serino]

I need some help with an INNER JOIN error, I am receiving a syntax error message (Missing Operator) and I don't know where I faulted.

SELECT A.CountOfJobName, A.Status, A.AccountRep, A.Company, A.JobName, A.DateYear, A.NewDateStamp AS STAGE_01, B.NewDateStamp AS STAGE_02, C.NewDateStamp AS STAGE_05
FROM T_002_Sales_Stage_Import_PT5_Table AS A
INNER JOIN T_002_Sales_Stage_Import_PT5_Table AS B ON (A.CountOfJobName = B.CountOfJobName) AND (A.AccountRep = B.AccountRep) AND (A.Company = B.Company) AND (A.Status = B.Status) AND (A.JobName = B.JobName) AND (A.DateYear = B.DateYear)
INNER JOIN T_002_Sales_Stage_Import_PT5_Table AS C ON (A.CountOfJobName = C.CountOfJobName) AND (A.AccountRep = C.AccountRep) AND (A.Company = C.Company) AND (A.Status = C.Status) AND (A.JobName = C.JobName) AND (A.DateYear = C.DateYear)
WHERE A.CURRENTSTAGE = '01-NEW LEAD'
AND B.CURRENTSTAGE = '02-CLIENT INTERVIEW'
AND C.CURRENTSTAGE = '05-DESIGN FILE PREP';

 

[SkipVought]

Hi,

Make sure that you have a SPACE between each term.

I can't see anything that would indicate a missing operator.

I reformatted your query to 1) make it more understandable and 2) insert SPACES at appropriate places

SELECT
  A.CountOfJobName
, A.Status
, A.AccountRep
, A.Company
, A.JobName
, A.DateYear
, A.NewDateStamp AS STAGE_01
, B.NewDateStamp AS STAGE_02
, C.NewDateStamp AS STAGE_05 
 FROM
 T_002_Sales_Stage_Import_PT5_Table A 
 INNER JOIN T_002_Sales_Stage_Import_PT5_Table B 
    ON (A.CountOfJobName = B.CountOfJobName)
   AND (A.AccountRep     = B.AccountRep)
   AND (A.Company        = B.Company)
   AND (A.Status         = B.Status)
   AND (A.JobName        = B.JobName)
   AND (A.DateYear       = B.DateYear) 
 INNER JOIN T_002_Sales_Stage_Import_PT5_Table C
    ON (A.CountOfJobName = C.CountOfJobName)
   AND (A.AccountRep     = C.AccountRep)
   AND (A.Company        = C.Company)
   AND (A.Status         = C.Status)
   AND (A.JobName        = C.JobName)
   AND (A.DateYear       = C.DateYear) 
 WHERE A.CURRENTSTAGE = '01-NEW LEAD'
   AND B.CURRENTSTAGE = '02-CLIENT INTERVIEW'
   AND C.CURRENTSTAGE = '05-DESIGN FILE PREP';

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[serino]

I am still receiving the error message. See attachment.

 

[SkipVought]

Try this alternate form of INNER JOIN...

SELECT
  A.CountOfJobName
, A.Status
, A.AccountRep
, A.Company
, A.JobName
, A.DateYear
, A.NewDateStamp AS STAGE_01
, B.NewDateStamp AS STAGE_02
, C.NewDateStamp AS STAGE_05 
 FROM
  T_002_Sales_Stage_Import_PT5_Table A 
, T_002_Sales_Stage_Import_PT5_Table B 
, T_002_Sales_Stage_Import_PT5_Table C
 WHERE (A.CountOfJobName = B.CountOfJobName)
   AND (A.AccountRep     = B.AccountRep)
   AND (A.Company        = B.Company)
   AND (A.Status         = B.Status)
   AND (A.JobName        = B.JobName)
   AND (A.DateYear       = B.DateYear) 
   AND (A.CountOfJobName = C.CountOfJobName)
   AND (A.AccountRep     = C.AccountRep)
   AND (A.Company        = C.Company)
   AND (A.Status         = C.Status)
   AND (A.JobName        = C.JobName)
   AND (A.DateYear       = C.DateYear) 
   AND A.CURRENTSTAGE = '01-NEW LEAD'
   AND B.CURRENTSTAGE = '02-CLIENT INTERVIEW'
   AND C.CURRENTSTAGE = '05-DESIGN FILE PREP';

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I dunno, I must have overwritten my previous reformat of your INNER JOINs.

Anyhow, the last post I believe, is correct.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

I would try change the single quotes to double quotes.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[combo]

A direct copy of SQL based on visual design of your query (hope so):

[pre]SELECT A.CountOfJobName, A.Status, A.AccountRep, A.Company, A.JobName, A.DateYear, A.NewDateStamp AS STAGE_01, B.NewDateStamp AS STAGE_02, C.NewDateStamp AS STAGE_05
FROM (T_002_Sales_Stage_Import_PT5_Table AS A INNER JOIN T_002_Sales_Stage_Import_PT5_Table AS B ON (A.JobName = B.JobName) AND (A.Status = B.Status) AND (A.Company = B.Company) AND (A.AccountRep = B.AccountRep) AND (A.CountOfJobName = B.CountOfJobName)) INNER JOIN T_002_Sales_Stage_Import_PT5_Table AS C ON (B.JobName = C.JobName) AND (B.Company = C.Company) AND (B.AccountRep = C.AccountRep) AND (B.Status = C.Status) AND (B.CountOfJobName = C.CountOfJobName)
WHERE (((A.CURRENTSTAGE)="01-NEW LEAD") AND ((B.CURRENTSTAGE)="02-CLIENT INTERVIEW") AND ((C.CURRENTSTAGE)="05-DESIGN FILE PREP"));[/pre]

So except of double quotes in criteria strings you need braces:
[pre]... FROM (T_002_Sales_Stage_Import_PT5_Table AS A ... AND (A.DateYear = B.DateYear)) ...[/pre]

 

[serino]

Thank you all so much...It was the braces I was missing.