Using Select as a subquery within Update Query? (1 Viewer)

[JenO1]

Hi, I'm looking for a little help with nesting a query. I'm running a query in access to search a table with two fields of numbers. One field is "Dose" and the other is "Volume". Below I'm searching for the "Dose" when the "Volume" is equal to a value nearest to 2 and dividing by 100 to get the correct units.

SELECT TOP 1 [Table1]![Dose]/100 AS CentiDose 
FROM [Table1] 
ORDER BY Abs([Table1]![Volume]-2);

This works fine in returning the value in a single cell ("CentiDose") in the datasheet view. However I want to use update (or at least that's what I think?) to take this value and insert it into a field of one existing record in another table. I think the basic structure of an Update query I want to use looks like this

UPDATE OAR
SET Field1 = x
WHERE OAR.OARSer = 1;

where the table I want to update is "OAR", the primary key is named "OARSer" and x is the value returned from the other query. I having trouble combining the two queries. I've tried a few ways but I keep getting "operation must use an updateable query" when I try to run it. I'm a complete novice at this and any help would be really appreciated.

 

[plog]

Action queries (UPDATE/APPEND/DELETE) always set off warnings in my mind. More often than not, they are used to overcome poor table structures or misunderstandings of how databases are to work.

So back up. Explain what your database represents--no database jargon, just explain to me in plain English what your actual business does and what this database does to make doing that easier.

Then explain why you need to do this UPDATE.

 

[JenO1]

ok, this is a pre-existing database for a radiotheraphy unit in a hospital. So for a patient with a particular area of the body that's being treated, different organs are subject to various levels of radiation. They've been entering data into subforms manually for the last few years (and saving it as a pdf) but they want to automate it. So I've been creating tables for these different organs and importing the data via text files that are exported from another fancier piece of software. Importing these text files was hard enough as they weren't in a very agreeable format for importing to Access. So in one of these subforms you might have 5 organs (depending on where you're treating) where you need to know the "Dose" at a specific "volume" ie 2% for the first example I gave in the first post. But I have multiple tables to find percentages from. Since the table which is the control for this subform has about 20 entries for different organs, I want to write a few queries to update a field from different sources for different records (organs).

I wish I could just change the initial subform into more subforms and have several control sources but I am not allowed to change the existing structure of the database that much. I'm basically only able to add to and work around what is already in place. I can't really complain about these resctrictions either as I'm only working as an intern and need this experience to graduate. Sorry if I haven't explained myself very well.

 

[JenO1]

I've been trying use something like

UPDATE OAR SET OAR.Field = (SELECT TOP 1 [Table1]![Dose]/100
FROM [Table1]
ORDER BY Abs([Table1]![Volume]-2))
WHERE (((OAR.OARSer)=1));

but I keep getting "operation must use an updateable query". Can anyone help with what I'm doing wrong?

 

[arnelgp]

UPDATE OAR, (SELECT TOP 1 [Table1]![Dose]/100 As TopDose FROM [Table1] ORDER BY Abs([Table1]![Volume]-2)) As B SET OAR.Field = B.TopDose
WHERE (((OAR.OARSer)=1));

 

[JenO1]

UPDATE OAR, (SELECT TOP 1 [Table1]![Dose]/100 As TopDose FROM [Table1] ORDER BY Abs([Table1]![Volume]-2)) As B SET OAR.Field = B.TopDose
WHERE (((OAR.OARSer)=1));

Perfect! Thank you so much. I can finally move forward and finish this project!

 

[arnelgp]

goodluck w/your project!