Update Query problem/question

[Ginny2222]

Hi all,

Is it possible to use the result of a query to update a field in a Table. I keep getting an error - "operation must use an updateable query".

Any help appreciated.

rgs
Ginny

 

[boblarson]

Yes, you can update information through a query but, as you have found out, you must use a query that is updateable.

Here's some good info about it:
http://msdn2.microsoft.com/en-us/library/aa198446(office.10).aspx

http://www.utteraccess.com/forums/showflat.php?Board=82&Number=1534273

 

[Ginny2222]

Thanks for the quick response, I'll read up ! !
Ginny

 

[Ginny2222]

Hi Bob, it looks like I'm stuck. I have a query returning a single calculated value from 2 underlying tables. (I can output a second field which would have a text name which can be used as a criteria.) I now want to store this value in a table without creating a new table or appending it. Is there any way this can be achieved?
Ginny

 

[boblarson]

Just so you know - most calculated values should not be stored. Here's another good post on that:
http://www.utteraccess.com/forums/s...=334811&page=0&view=collapsed&sb=5&o=&fpart=1

 

[Ginny2222]

Yes, I'm aware of that, but in this case, we want to run current month report from this database, but for a certain number of fields we are holding values to show a trend. This is an interim solution until something better is developed. Hence the departure from good practice!
Thanks for your help Bob,
Ginny

 

[ajetrumpet]

Is it possible to use the result of a query to update a field in a Table.

Of course it is!!...

UPDATE yourtable, thequeryholdingthevalue SET

          yourtable.yourfield = relevantquery.field (only holding one value)

WHERE clause, if needed

Non-Updatable queries do not affect this.

 

[Ginny2222]

Thanks Pat, I've a lot to learn ! !
Yes, 'jumping through hoops' I have my data in a new table for each new month. I'll now work on getting this data into an existing table using update. I'm not feeling confident enough to try using DAO (Access 2000 I'm afraid) yet.
Ginny

 

[Ginny2222]

Thanks Ajetrumpet, will try again.