How do I perform an update statement with an inner join in an MS Access query
I am trying to perform an update statement in Microsoft Access 2010. A seemingly simple task is racking my brain.
-I have 1 table: ResultsTable.
-I have 1 query: QueryMinPostingDate
-I am trying to update the table:
ResultsTable.MinOfPostingDate
to
QueryMinPostingDate.MinOfPostingDate
Here is Attempt 1:
UPDATE
[ResultsTable]
SET
[ResultsTable].[MinOfPosting_Date] =[QueryMinPostingDate].[MinOfPosting_Date]
FROM [ResultsTable]
INNER JOIN [QueryMinPostingDate]
ON [ResultsTable].[EncounterID_UniqueID_Internal] = [QueryMinPostingDate].[EncounterID_UniqueID_Internal]
Result:
I receive the error
"Syntax Error (missing operator) in query expression..."
Here is Attempt 2:
UPDATE ResultsTable
INNER JOIN QueryMinPostingDate
ON ResultsTable.EncounterID_UniqueID_Internal = QueryMinPostingDate.EncounterID_UniqueID_Internal
SET ResultsTable.MinOfPosting_Date = [QueryMinPostingDate].[MinOfPosting_Date]
Result:
I receive the error
"Operation must use an Updateable Query"
Oh what fun with MS Access!!