Populating a table with values from another table

[mp3909]

I have a table in access where 2 of its columns need populating. It will be getting its values from another table.
I am not sure how to do this?

[mp3909]

I need to populate two fields "LineId" and "BTMU_Relationship_Office" in this table:



from this table (which also has those 2 fields).



but where the field "OrgnsnCod" from the first table = "OrgnsnCod" from the second table
AND ""TradeTyp" in the first table is equal to "CSPA"

[June7]

Try starting with a SELECT query that joins the 2 tables on the 2 pairs of fields. Then switch to UPDATE query and set the fields to update with which source fields.

[mp3909]

I came up with this SQL code:

Sub runUpdateSQL()
Dim sSQL As String
sSQL = "UPDATE [Trade_Exposure_Report_All]"
sSQL = sSQL & vbNewLine & "LEFT OUTER JOIN [tblCSPAmapping]"
sSQL = sSQL & vbNewLine & "ON [Trade_Exposure_Report_All]![OrgnsnCod]=[tblCSPAmapping]![OrgnsnCod]"
sSQL = sSQL & vbNewLine & "SET [Trade_Exposure_Report_All]![LineId]=[tblCSPAmapping]![LineId], [Trade_Exposure_Report_All]![BTMU_Relationship_Office]=[tblCSPAmapping]![BTMU_Relationship_Office]"
sSQL = sSQL & vbNewLine & "WHERE [Trade_Exposure_Report_All]![TradeTyp]='CSPA'"
sSQL = sSQL & vbNewLine & "AND [Trade_Exposure_Report_All]![LineId] Is Null"
sSQL = sSQL & vbNewLine & "AND [Trade_Exposure_Report_All]![BTMU_Relationship_Office] is Null"
CurrentDb.Execute sSQL
End Sub

and in the immediate window, my sql statement looks like this

UPDATE [Trade_Exposure_Report_All]
LEFT OUTER JOIN [tblCSPAmapping]
ON [Trade_Exposure_Report_All]![OrgnsnCod]=[tblCSPAmapping]![OrgnsnCod]
SET [Trade_Exposure_Report_All]![LineId]=[tblCSPAmapping]![LineId], [Trade_Exposure_Report_All]![BTMU_Relationship_Office]=[tblCSPAmapping]![BTMU_Relationship_Office]
WHERE [Trade_Exposure_Report_All]![TradeTyp]='CSPA'
AND [Trade_Exposure_Report_All]![LineId] Is Null
AND [Trade_Exposure_Report_All]![BTMU_Relationship_Office] is Null

but I get error when I try to execute it, it says "JOIN expression not supported".
I don't understand why?

[June7]

Did you construct SQL with query builder?

Not joining on both pairs of fields - compound join.

[mp3909]

Yes, I did use Query Builder and just changed the join from Inner to Left as I want all the records from my left table.
Still do not understand what's exactly wrong though?

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[mp3909]

sorry June7, but I have no clue what you are on about.
I really do not have time to waste, wished someone just pointed straight to the answer.

[June7]

No clue about what? How to do compound join? How to attach file to post? I did point to an answer - compound join. Then I offered to review your db. I don't have time to waste either. I will now direct my attention to other threads.

Good luck with your project.

[Minty]

By the look of it you don't need to do this as you can always pull that data from the source table, by simply querying with a join based on the orgnsncode, which appears to be the primary key.
No need to duplicate the data or update your second table.

[mp3909]

solved.
Nothing about compound joins, instead it was syntactical error, replaced "!" with "."