Update Query doesn't work (Access replies with several messages)

[Nicola M]

Hi all! Access 2003.
Due to a mistake in the design phase (it is also my fault) now I need to
update a new, null value field in the table B inserting some data stored in
the table A, following the match between a field contained in both the tables.
I tried with a lot of combination with and without the help of the query
wizard but Access replies me that I have to use an updateable query (but it
already is) or showing a Syntax Error in .....

To help you to understand my situation here a brief explanation of the issue.
Table T_People with PK IDPEOPLE. Table T_COURSES with FK IDPEOPLE. Table
T_Skills with FK IDPEOPLE. After the deployment of the DB we have discovered
that the skills are relative to a single course (in fact after every course
skills can change) and they are not to a person. Fortunately only few
hundreds of records are now in the DB so we're working to fix this issue. I
could insert manually these data but I'd like to know (and thus to learn) how
to get the same results with a query...

Thank in advance for your time and suggestions.

Nicola M

 

[Nicola M]

Hi MG, thanks for your speed.
My query is just like yours (some less parenthesis but any big deals). Now
Access shows a warning about the update operation but after I pressed Yes
another message says "Operation must use an updateable query". After Reading
the button help contents I checked all the issues but... nothing.
The DB is write enabled;
The destination table involved is without relation (actually before it had
and Access showed only the "Operation... query" message. After removed the
relations Access shows before the message warns about the update query use.
I have all the permissions on the DB, on the network and on the server
folder in which the DB is...

The problem is just in the use of the subquery: I tried with a stupid
parameters in the SET part of the query and it works (SET = 1, SET = "AA"
etc). When I retype the subquery after the equal sign I get again the above
mentioned messages.

Uff!!!

Nicola M

 

[John Spencer]

UPDATE tableA INNER JOIN TableB
ON TableA.ID = TableB.id
SET TableA.Column_Name=[TableB].[Column_Name]
WHERE tableA.column_name IS NULL


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Nicola M]

It works. Thank you very very much.
Nicola M.

UPDATE tableA INNER JOIN TableB
ON TableA.ID = TableB.id
SET TableA.Column_Name=[TableB].[Column_Name]
WHERE tableA.column_name IS NULL


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi MG, thanks for your speed.
My query is just like yours (some less parenthesis but any big deals). Now
Access shows a warning about the update operation but after I pressed Yes
another message says "Operation must use an updateable query". After Reading
the button help contents I checked all the issues but... nothing.
The DB is write enabled;
The destination table involved is without relation (actually before it had
and Access showed only the "Operation... query" message. After removed the
relations Access shows before the message warns about the update query use.
I have all the permissions on the DB, on the network and on the server
folder in which the DB is...

The problem is just in the use of the subquery: I tried with a stupid
parameters in the SET part of the query and it works (SET = 1, SET = "AA"
etc). When I retype the subquery after the equal sign I get again the above
mentioned messages.

Uff!!!

Nicola M