MS Access 2007 SQL Syntax Error in Join expression

Question

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)) ;

Gordon Linoff · Accepted Answer · 2016-09-19 23:36:24Z

2

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
     )

edited Sep 19, 2016 at 23:36

answered Sep 19, 2016 at 14:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ayan Mukherje Over a year ago

Thanks for the answer, however I am still getting the same error even after replacing the code as per your suggestion.

onedaywhen Over a year ago

"Switch to a better database" too honest :)

Gordon Linoff Over a year ago

@AyanMukherje . . . I thought a subquery would do it. Perhaps a view is needed.

Ayan Mukherje Over a year ago

Well it worked after I made a couple of edits and checked the brackets. Thanks for your help Gordon :). I am new to this community and I've a couple of questions, any way we can connect and discuss them?

Parfait · Accepted Answer · 2016-09-20 00:52:13Z

1

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));

answered Sep 20, 2016 at 0:52

Parfait

108k19 gold badges103 silver badges138 bronze badges

Collectives™ on Stack Overflow

MS Access 2007 SQL Syntax Error in Join expression

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related