Update from Select

[ScottishFencer]

Hi,

Not sure if this is the right place.

I have a table that I want to update. There are a list of IDs that need to be updated from a 0 to a value derived in a select query. I can create a select query that gives me the right data but when I try to turn it into an Update I get the error message "Operation must use an updateable table". This will be becasuse one of the fields is coming from the Select.

In essence what I want to do is:

UPDATE ID.Table SET ID.Table = ID.SelectQuery
WHERE AssessmentID > 60000

This seems to be a prett entry level query yet I am banging my head against a brick wall.

 

[lupins46]

Are you doing this in the query grid?

If so you need to add the table you want to update and the query with the new values to the query. Join them on the matching fields.
To check what is going to happen in your update query add the existing field and the field containing the new value to the output grid.
Also add the AssessmentID and put in your criteria.
View the datasheet - this will show you the old and proposed new values for each record.

Now use the menu to convert to an update query.
In the UpdateTo cell for your current id value you put the name of the id field from your select query in the format:
queryname.id
You can delete the column containing the queryname.id from the grid and save the query.

Make sure you have a backup.
Run the query.

 

[ScottishFencer]

I am using the QBE grid. All of the queries are stored in teh Acess front end and I don't have the time to re-write into SQL (a project for another day). PLus I fin JetSQL confusing to write in.

What you have outlined is what I have tried.

I'll post the JetSQL below:

UPDATE tblCAD INNER JOIN qryIntegratedAssessments0506 ON tblCAD.txtMem = qryIntegratedAssessments0506.SQCMem SET tblCAD.txtAID = [qryIntegratedAssessments0506].[txtaid]
WHERE (((tblCAD.txtID)>60000));

Am I missing something?

 

[lupins46]

No this does not make sense.

Re-reading your originalpost you said that you wanted to update a field containing 0 to an id value.
What is the test for >60000 for then?

Also, please explain how you know which record in the query matches which record in the table .

 

[ScottishFencer]

Sorry if my original post wasn't clear.

I have a table that I have already added some records to. The txtAid field had inadvertantly been created as '0'. I put together a query that would give me the right values for txtAid and now I want to update tblCAD's txtAid field with the right values taken from the query.

Those records with an ID > 60000 are the correct ones.

 

[lupins46]

I'm still unclear, Im afraid.

You have one table(A) which contains a mixture of 0 values and non-zero values in txtAid?
So are you saying you have records on the table twice, once with a 0 txtaid and once with a txtaid of over 60000?

What is the 'select query' you are referring to then? What is the source for that?

And I'm still back the this Q. For any particular record, how do you know what specific value to replace a specific 0 with?