CODEWITHSUNDEEP
sqlms-accessselectjoinnested
MTdoe
MTdoe

Reputation: 3

Nested select statement w/ multiple joins (MS Access)

I'm having issues transferring a query that works in SQL Server Management Studio to MS Access. I'm getting an error:

Syntax error in JOIN operation

The line 'FROM ProductRun' is highlighted when I get this error.

I think Access requires brackets when dealing with multiple Join statement which I've done. I'm stuck. Please help.

SELECT 
    LoadTable.DateTimeStamp, ProgramTable.Value 1, PL.Value 2, PL.Value 3
FROM
    ((LoadTable 
LEFT JOIN 
    ProgramTable ON LoadTable.DateTimeStamp = ProgramTable.DateTimeStamp) 
LEFT JOIN 
    PL ON LoadTable.DateTimeStamp = PL.DateTimeStamp)
LEFT JOIN 
    (SELECT [StartTime], [EndTime]
     FROM ProductRun
     WHERE ProductRun.StartTime >= '11/1/2017' 
       AND ProductRun.Startime <= '12/1/2017') ON (LoadTable.DateTimeStamp >= DATEADD(MINUTE, 15, ProductRun.StartTime) 
                                               AND LoadTable.DateTimeStamp <= DATEADD(MINUTE, -15, ProductRun.EndTime))
ORDER BY 
    LoadTable.DateTimeStamp;

This is my nested select statement in Management Studio which works just fine:

LEFT JOIN (SELECT [StartTime] 
      ,[EndTime]
FROM [ProductionReporting].[dbo].ProductRun
 WHERE StartTime between '11/1/2017 12:00:00 AM' and '11/25/2017 12:00:00 AM') as M3
 ON M1.DateTimeStamp between DATEADD(MINUTE,15, M3.StartTime) and DATEADD(MINUTE,-15, M3.EndTime)

Upvotes: 0

Views: 66

Answers (1)

Kashif Qureshi
Kashif Qureshi

Reputation: 1490

Try this:

SELECT loadtable.datetimestamp, 
       programtable.value AS 1, 
       pl.value           AS 2, 
       pl.value           AS 3 
FROM   ((loadtable 
         LEFT JOIN programtable 
                ON loadtable.datetimestamp = programtable.datetimestamp) 
        LEFT JOIN pl 
               ON loadtable.datetimestamp = pl.datetimestamp) 
       LEFT JOIN (SELECT starttime, 
                         endtime 
                  FROM   productrun 
                  WHERE  productrun.starttime >=#11/1/2017# 
                         AND productrun.starttime <=#12/1/2017# ) a 
              ON loadtable.datetimestamp >= Dateadd("n", 15, a.starttime) 
                 AND loadtable.datetimestamp <= Dateadd("n", -15, a.endtime) 
ORDER  BY loadtable.datetimestamp;

Upvotes: 1

Related Questions