Access 2007 freaks out (“JOIN not supported”) when I put constant expressions in my left joins. I originally wrote this query in Access 2003, and it still works in Access 2007 as long as I don’t make any edits, but if I change anything (e.g., add a return to the end) , it won’t compile. If I take out the company part and latest cost, the query runs (but I get the wrong results, naturally).
Any idea how to include clauses like this in Access 2007?
SELECT [AFAs].afa, sum( [quantity] * NZ( [Latest Cost].[Factory Cost] * NZ(1+[MRB/APB],1),List_Price )) AS [Total Part Cost]
FROM ((AFAs LEFT JOIN Installed_Components ON [Installed_Components].AFA = [AFAs].afa) LEFT JOIN [Latest Cost] ON
([Latest Cost].[Part Number] = [Installed_Components].[part_number])
([Latest Cost].[condition] = [Installed_Components].[part_condition] )
( [cpart]='Company Part')
( [Latest Cost].[Factory Cost] > 0)
) LEFT JOIN [2011 Purchase complete parts] ON [2011 Purchase complete parts].[Material] = [Installed_Components].[part_number] AND Installed_Components.part_condition='new'
GROUP BY [AFAs].afa;