Link to home
Create AccountLog in
Avatar of Jilliax33
Jilliax33

asked on

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"

Any help would be greatly appreciated!
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Jilliax33
Jilliax33

ASKER

Yep, that worked.
Oh what fun with MS Access!!