MS-ACCESS Using LEFT JOIN with INNER JOIN

Question

SELECT
U.[Current Month], 
U.[Security Name],
U.[CM Return],
(P1.[Return] * P2.[Return] * P3.[Return] * P4.[Return] * P5.[Return] * P6.[Return] * P7.[Return]) AS [7 Month Return],
(P1.[Return] * P2.[Return] * P3.[Return] * P4.[Return] * P5.[Return] * P6.[Return] * P7.[Return] * P8.[Return]) AS [8 Month Return],
(P1.[Return] * P2.[Return] * P3.[Return] * P4.[Return] * P5.[Return] * P6.[Return] * P7.[Return] * P8.[Return] * P9.[Return]) AS [9 Month Return],
(P1.[Return] * P2.[Return] * P3.[Return] * P4.[Return] * P5.[Return] * P6.[Return] * P7.[Return] * P8.[Return] * P9.[Return] * P10.[Return]) AS [10 Month Return],
(P1.[Return] * P2.[Return] * P3.[Return] * P4.[Return] * P5.[Return] * P6.[Return] * P7.[Return] * P8.[Return] * P9.[Return] * P10.[Return] * P11.[Return]) AS [11 Month Return]
FROM ((((((((((((
[Prior Month Returns Tbl] AS U
INNER JOIN [Securities] AS S ON U.[Security Name] = S.[Security Name])
LEFT JOIN [Data 9 17 Monthly] AS P1 ON P1.[Security ID] = S.[ID] AND P1.[Date Month] = DateAdd("m",-1,U.[Current Month]))
LEFT JOIN [Data 9 17 Monthly] AS P2 ON P2.[Security ID] = S.[ID] AND P2.[Date Month] = DateAdd("m",-2,U.[Current Month]))
LEFT JOIN [Data 9 17 Monthly] AS P3 ON P3.[Security ID] = S.[ID] AND P3.[Date Month] = DateAdd("m",-3,U.[Current Month]))
LEFT JOIN [Data 9 17 Monthly] AS P4 ON P4.[Security ID] = S.[ID] AND P4.[Date Month] = DateAdd("m",-4,U.[Current Month]))
LEFT JOIN [Data 9 17 Monthly] AS P5 ON P5.[Security ID] = S.[ID] AND P5.[Date Month] = DateAdd("m",-5,U.[Current Month]))
LEFT JOIN [Data 9 17 Monthly] AS P6 ON P6.[Security ID] = S.[ID] AND P6.[Date Month] = DateAdd("m",-6,U.[Current Month]))
LEFT JOIN [Data 9 17 Monthly] AS P7 ON P7.[Security ID] = S.[ID] AND P7.[Date Month] = DateAdd("m",-7,U.[Current Month]))
LEFT JOIN [Data 9 17 Monthly] AS P8 ON P8.[Security ID] = S.[ID] AND P8.[Date Month] = DateAdd("m",-8,U.[Current Month]))
LEFT JOIN [Data 9 17 Monthly] AS P9 ON P9.[Security ID] = S.[ID] AND P9.[Date Month] = DateAdd("m",-9,U.[Current Month]))
LEFT JOIN [Data 9 17 Monthly] AS P10 ON P10.[Security ID] = S.[ID] AND P10.[Date Month] = DateAdd("m",-10,U.[Current Month]))
LEFT JOIN [Data 9 17 Monthly] AS P11 ON P11.[Security ID] = S.[ID] AND P11.[Date Month] = DateAdd("m",-11,U.[Current Month]))
WHERE S.ID = 14

Above is the MS-ACCESS Query that i want to execute. What i want to do is simple for the security id 14 i want to count the multiplication of the previous 7 to 11 months returns from the [Data 9 to 17 Monthly] table for the current month in [Prior Month Returns Tbl]. the query works fine if i use inner join but it will omit the result if not all the previous 11 months records are present in the [Data 9 to 17 Monthly] so thats why i am using left join but i am getting error that JOIN EXPRESSION NOT SUPPORTED. Please Help. I know this query would work fine in t-sql. What should i do that this query also work in MS-Access?

http://office.microsoft.com/en-us/access-help/HV080760616.aspx or http://stackoverflow.com/questions/16608313/join-expression-not-supported-in-access — Andrey Morozov, Dec 28 '14 at 07:55

score 2 · Accepted Answer · answered Dec 28 '14 at 13:38

2

In an outer join, MS Access does not support conditions that are not between the two tables. This is a very strange limitation in my opinion.

You can work around it with subqueries. Here is an example:

LEFT JOIN
(SELECT P1.*
 FROM [Data 9 17 Monthly] AS P1
 WHERE P1.[Date Month] = DateAdd("m",-1,U.[Current Month]))
) as P1
ON P1.[Security ID] = S.[ID]

Alternatively, you could combine all the data into a single row using conditional aggregation:

LEFT JOIN
(SELECT [Security ID],
        MAX(IIF(P1.[Date Month] = DateAdd("m", -1, U.[Current Month])), [RETURN], 1) as Return_01,
        MAX(IIF(P1.[Date Month] = DateAdd("m", -2, U.[Current Month])), [RETURN], 1) as Return_02,
        . . .
        MAX(IIF(P1.[Date Month] = DateAdd("m", -12, U.[Current Month])), [RETURN], 1) as Return_12
) as P
ON P.[Security ID] = S.[ID]

And then adjust the outer SELECT as needed.

Or, you could upgrade to a more ANSI-compliant database.

answered Dec 28 '14 at 13:38

Gordon Linoff

1,242,037
58
646
786

The first solution looks good. worked for me it resolved the JOIN EXPRESSION NOT SUPPORTED error but access asked me for the value of U.[Current Month]. why did it ask me for value it should be taken from the inner join i did before right? for now i have worked around this issue by creating separate queries for each Previous month multiplication. – Krunal Parekh Dec 30 '14 at 12:47
@KrunalParekh . . . I messed up on that part of the answer. I didn't notice that `[Current Month]` comes from a different table. That complicates matters a bit. I wonder if your original query would work with `and on` rather than `and` (yet another MS Access weirdism). – Gordon Linoff Dec 31 '14 at 22:18
It took me hours to come across the solution. (Damned MS A!) – Martin F Jan 28 '15 at 22:53

MS-ACCESS Using LEFT JOIN with INNER JOIN

1 Answers1