Syntax Error Joining a table on itself in SQL through Microsoft Access

Question

I am trying join a table (sheet1) on itself 3 times on the column "DATE". But each time I join the next table, I want it to be on the date 3, 6, and 9 months prior to the original table's date.

I tried this query on Microsoft SQL Server Management Studio using another similar table and it runs correctly, but when I run the query through SQL in Microsoft Access I am getting a SYNTAX ERROR (missing operator).

Any help would be appreciated, thanks!

Sheet1 has 2 columns, "Date" and "SA372 returns".

select 
   Sa.Date ,
   Sa.[SA372 returns], 
   (1+ Sa.[SA372 returns])*(1 + Sb.[SA372 returns])* (1+ Sc.[SA372 returns])*(1+ Sd.[SA372 returns]) as Compound 

from sheet1 Sa

left join  

sheet1 Sb on  Sb.Date = DATEADD(day,-1,dateadd(month,-3,DATEADD(day,1,Sa.Date)))

left join 

sheet1 Sc on  Sc.Date= DATEADD(day,-1,dateadd(month,-6,DATEADD(day,1,Sa.Date)))

left join 

sheet1 Sd on  Sd.Date= DATEADD(day,-1,dateadd(month,-9,DATEADD(day,1,Sa.Date)))

order by Sa.date asc

So it should add 1 day to `Sa.Date`, and then remove 3 months from the result of that, and then remove 1 day from the result of that? The interval period is a string, so assuming your whole `DATEADD` bit works it should be `DATEADD("d",-1,dateadd("m",-3,DATEADD("d",1,Sa.Date)))` — Darren Bartrup-Cook, Jul 27 '17 at 15:51
Possible duplicate of [Multiple INNER JOIN SQL ACCESS](https://stackoverflow.com/questions/20929332/multiple-inner-join-sql-access) -- it's the same for LEFT JOINs, you need parentheses. — Andre, Jul 27 '17 at 15:51
@Andre - the JOINS are a duplicate of that question, the `DATEADD` also needs looking at though. — Darren Bartrup-Cook, Jul 27 '17 at 16:08
@DarrenBartrup-Cook Yes the whole point of the DATEADD is to subtract 3 months and also get the last day of the month. I changed day to "d" and month to "m" and am still getting a syntax error (missing operator). — user6003641, Jul 27 '17 at 20:06

score 1 · Accepted Answer · answered Jul 27 '17 at 16:05

Without testing, I think this is what you're after:

SELECT  
    Sa.Date
    , Sa.[SA372 returns]
    , (1+ Sa.[SA372 returns])*(1 + Sb.[SA372 returns])* (1+ Sc.[SA372 returns])*(1+ Sd.[SA372 returns]) as Compound 
FROM
    ((
      sheet1 Sa LEFT JOIN sheet1 Sb ON DATEADD("d",-1,DATEADD("m",-3,DATEADD("d",1,Sa.Date))) = Sb.Date)
                LEFT JOIN sheet1 Sc ON DATEADD("d",-1,DATEADD("m",-6,DATEADD("d",1,Sa.Date))) = Sc.Date)
                LEFT JOIN sheet1 Sd ON DATEADD("d",-1,DATEADD("m",-9,DATEADD("d",1,Sa.Date))) = Sd.Date
ORDER BY
    Sa.Date ASC

Note:

The brackets around each join except the last
The syntax in the DATEADD function. "d","m"
https://support.office.com/en-gb/article/DateAdd-Function-63befdf6-1ffa-4357-9424-61e8c57afc19
The joins join on the left hand side table first.
E.g. Sa LEFT JOIN Sb ON Sa.Field = Sb.Field
(I'm not sure if that's important - think it is though).

Syntax Error Joining a table on itself in SQL through Microsoft Access

1 Answers1