Multiple Left Joins not working together, but work seperate

[BustyAshley]

Query keeps saying JOIN Expression not supported... I can't figure out why? (It works independently for each one, but not when I put the three togethers


SELECT DISTINCTROW [06 Rev Rec].ContractID, [06 Rev Rec].AssetNum, [06 Rev Rec].FeatureNum, [06 Rev Rec].Period, [06 Rev Rec].Revenue, [06 Rev Rec].PSP, [09 ACR Lookup].[MFR], [09 ACR Lookup].[MODEL#], [09 ACR Lookup].[SYSTEM DESCRIPTION]

FROM (([06 Rev Rec]
LEFT JOIN [09 ACR Lookup] ON nz([06 Rev Rec].[AssetNum],"") = nz([09 ACR Lookup].[MFR],""))
LEFT JOIN [09 ACR Lookup] ON nz([06 Rev Rec].[AssetNum],"") = nz([09 ACR Lookup].[MODEL#],""))
LEFT JOIN [09 ACR Lookup] ON nz([06 Rev Rec].[AssetNum],"") = nz([09 ACR Lookup].[SYSTEM DESCRIPTION],"")

GROUP BY [06 Rev Rec].ContractID, [06 Rev Rec].AssetNum, [06 Rev Rec].FeatureNum, [06 Rev Rec].Period, [06 Rev Rec].Revenue, [06 Rev Rec].PSP, [09 ACR Lookup].[MFR], [09 ACR Lookup].[MODEL#], [09 ACR Lookup].[SYSTEM DESCRIPTION];

 

[plog]

So many bad things. Before I get to your issue, indulge me:

1. You should only use alphanumeric characters in table/field names. No spaces, no number signs, just letters and numbers and maybe an underscore. Just makes writing code easier.

2. Your GROUP BY clause makes your DISTINCTROW redundant.

3. Why the NZ values in your JOIN criteria? I mean its a LEFT JOIN so..so why are you...I'm so confused.


I think if you remove the 2 right parenthesis after the FROM and then the left parenthesis after each of the 1st two LEFT JOINS that might do it.

If not, can you upload your database? That way I can play with it.

 

[BustyAshley]

lol turns out, this was the SQL statement I wanted :/... I needed the NZ because I have blank ASSETNum's but I only needed 1 from statement because the three columns I wanted to add, could all be grabbed from that.

SELECT DISTINCTROW [06 Rev Rec].ContractID, [06 Rev Rec].AssetNum, [06 Rev Rec].FeatureNum, [06 Rev Rec].Period, [06 Rev Rec].Revenue, [06 Rev Rec].PSP, [09 ACR Lookup].[MFR], [09 ACR Lookup].[MODEL], [09 ACR Lookup].[SYSTEM DESCRIPTION]

FROM [06 Rev Rec]
LEFT JOIN [09 ACR Lookup] ON nz([06 Rev Rec].[AssetNum],"") = nz([09 ACR Lookup].[ASSETNUM1],"")

GROUP BY [06 Rev Rec].ContractID, [06 Rev Rec].AssetNum, [06 Rev Rec].FeatureNum, [06 Rev Rec].Period, [06 Rev Rec].Revenue, [06 Rev Rec].PSP, [09 ACR Lookup].[MFR], [09 ACR Lookup].[MODEL], [09 ACR Lookup].[SYSTEM DESCRIPTION];

 

[BustyAshley]

AKA off the asset number I wanted columns MFR, System Descrip & Model, from my lookup which all are determined based on the asset number