CODEWITHSUNDEEP
sqlms-access
Ayan Mukherje
Ayan Mukherje

Reputation: 13

MS Access 2007 SQL Syntax Error in Join expression

I am trying to join four tables and I am getting the error 'Syntax error in Join Expression'

Please find below the query I am presently trying to write

SELECT a.*,
switch(a.[Start Date] between b.[SEASON1START] and b.[SEASON1END],b.[LRA_S1_RT1_SGL],
a.[Start Date] between c.[SEASON1START] and c.[SEASON1END],c.[LRA_S1_RT1_SGL],
a.[Start Date] between d.[SEASON1START] and d.[SEASON1END],d.[LRA_S1_RT1_SGL]) as [Negotiated Rate Local],
switch(a.[Start Date] between b.[SEASON1START] and b.[SEASON1END],
b.[RATE_CURR],a.[Start Date] between c.[SEASON1START] and c.[SEASON1END],c.[RATE_CURR],
a.[Start Date] between d.[SEASON1START] and d.[SEASON1END],d.[RATE_CURR]) as [Negotiated Currency]
FROM ((([Q1001 - Split Transactions] a 
left join [2014 Negotiated Rate] b on (a.[RX_ID] = b.[PROPCODE] And YEAR(a.[Start Date] = 2014))
left join [2015 Negotiated Rate] c on (a.[RX_ID] = c.[PROPCODE] And YEAR(a.[Start Date] = 2015)) 
left join [2016 Negotiated Rate] d on (a.[RX_ID] = d.[PROPCODE] And YEAR(a.[Start Date] = 2016)) ;

Upvotes: 1

Views: 74

Answers (2)

Parfait
Parfait

Reputation: 107577

Your original query should work. In MS Access, you can have expressions in ON clauses like WHERE clauses (though intricate expressions will not be viewable in Design View only in SQL View).

Specifically, you are not wrapping the YEAR() function properly. Consider the following adjustment:

FROM ((([Q1001 - Split Transactions] a 
left join [2014 Negotiated Rate] b 
      on (a.[RX_ID] = b.[PROPCODE] And YEAR(a.[Start Date]) = 2014))
left join [2015 Negotiated Rate] c 
      on (a.[RX_ID] = c.[PROPCODE] And YEAR(a.[Start Date]) = 2015)) 
left join [2016 Negotiated Rate] d 
      on (a.[RX_ID] = d.[PROPCODE] And YEAR(a.[Start Date]) = 2016));

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

MS Access does not permit constants in the on clause. The solution? Switch to a better database. Oh wait. That isn't always an option. So, subqueries to the rescue:

FROM ((([Q1001 - Split Transactions] a left join
        (SELECT b.*, 2014 as yyyy FROM [2014 Negotiated Rate] as b
        ) as b
        on a.[RX_ID] = b.[PROPCODE] And YEAR(a.[Start Date]) = b.yyyy
       ) left join
       (SELECT c.*, 2015 as yyyy FROM [2015 Negotiated Rate] as c
       ) as c
       on a.[RX_ID] = c.[PROPCODE] And YEAR(a.[Start Date]) = c.yyyy
      ) left join
      (SELECT d.*, 2016 as yyyy FROM [2016 Negotiated Rate] as d
      ) as d
      on a.[RX_ID] = d.[PROPCODE] And YEAR(a.[Start Date]) = d.yyyy
     )

Upvotes: 2

Related Questions