Using MS Access how to perform an update with multiple joins and where clauses?

[wblakenc]

I am having a bit of trouble with getting some MS Access SQL to work. Here is the high level:

I have values in one table, by15official that I need to use to update related records in another table, investmentInfo. Pretty straight forward except there are quite a few joins I need to perform to make sure the right record is updated in the investmentTable and I think I could figure this out with regular sql, but Access is not playing nicely. The following is my sql I am trying to use (which results in this error: "Syntax error (missing operator) in query expression ..." I tried the following that I modified from some code I found on StackOverflow. I have since been told that the following is not ACE/Jet Syntax, but here it is:

update iiset ii.investmentType = by15.InvestmentType
from investmentInfo as ii
inner join 
    (select by15Official.InvestmentType, by15Official.InvestmentNumber
     from (((by15official left join investmentinfo on by15official.InvestmentNumber = investmentInfo.investID) 
    left join fundingSources on fundingSources.investId = investmentInfo.id)
    left join budgetInfo on budgetInfo.fundingID = fundingSources.id)
    where investmentinfo.submissionType = 2
    and budgetInfo.byYear = 2015
    and budgetInfo.type = 'X') as by15
on by15.InvestmentNumber = ii.investID

I have also tried:

[COLOR=#000088][FONT=Segoe UI]update[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] investmentInfo[/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]set[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] investmentInfo[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]investmentType [/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI]=
[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI]([/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]select[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] by15Official[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]InvestmentType
[/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]from[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI]((([/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]by15official [/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]left [/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]join[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] investmentinfo [/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]on[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] by15official[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]InvestmentNumber [/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI]=[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] investmentInfo[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]investID[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI])
[/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]left [/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]join[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] fundingSources [/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]on[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] fundingSources[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]investId [/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI]=[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] investmentInfo[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]id[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI])
[/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]left [/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]join[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] budgetInfo budgetInfo [/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]on[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] budgetInfo[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]fundingID [/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI]=[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] fundingSources[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]id[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI])
[/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]where[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] investmentinfo[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]submissionType [/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI]=[/FONT][/COLOR][COLOR=#006666][FONT=Segoe UI]2
[/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]and[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] budgetInfo[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]byYear [/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI]=[/FONT][/COLOR][COLOR=#006666][FONT=Segoe UI]2015
[/FONT][/COLOR][COLOR=#000088][FONT=Segoe UI]and[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] investmentInfo[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]investID [/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI]=[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI] by15official[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI].[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI]InvestmentNumber[/FONT][/COLOR][COLOR=#666600][FONT=Segoe UI])[/FONT][/COLOR]

But the above returns a error "Operation must use an updateable query". Any suggestions? I am not sure what to do. I asked over at the MS Forums and was basically told to read the manual, which I have but doing an update with this number of joins is confusing to me. Any help you can provide would be appreciated.

 

[ChrisM]

Not a total Access expert, but my success rate on Updates with left joins is maybe.....5% of the time. Almost all the times I have to write temp results to a table first. Sometimes you can get it to work by editing the query properties and playing with the "unique records" "unique values" properties, but I can't offer strong guidance on those.

 

[wblakenc]

Thanks Chris. That is actually not a bad idea, do the select to grab the "unique" records in my investments table (i.e. those that match those in the BY15Official) and update the investment table with that data. Should make it easier (although a muti step process which is kind of annoying).

I will post back with what I did.

 

[wblakenc]

Quick FYI. I was able to get some assistance over at the MS Forums, here is how the SQL needs to be formatted. Hopefully this will help someone else.

[COLOR=#333333][FONT=Segoe UI]UPDATE  
 (   
    (      
      by15official LEFT JOIN investmentinfo
      ON by15official.InvestmentNumber = investmentInfo.investID     
    )    
   LEFT JOIN     fundingSources 
  ON investmentInfo.id = fundingSources.investId    
  )
  LEFT JOIN budgetInfo
  ON fundingSources.id = budgetInfo.fundingID  
SET investmentInfo.investmentType = by15official.InvestmentNumber 
WHERE (investmentinfo.submissionType = 2) 
And (budgetInfo.byYear = 2015)  [/FONT][/COLOR]