How do I perform an update statement with an inner join in an MS Access query

Jilliax33
Jilliax33 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
try this,
convert your query "[QueryMinPostingDate]" into a make table query, name the table tmpTable.

- use the tmpTable to update the ResultsTable

UPDATE ResultsTable
INNER JOIN tmpTable
ON ResultsTable.EncounterID_UniqueID_Internal = tmpTable.EncounterID_UniqueID_Internal
SET ResultsTable.MinOfPosting_Date = [tmpTable].[MinOfPosting_Date]

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial