UPDATE Query

  • Thread starter Thread starter laurajayne.cozens
  • Start date Start date
L

laurajayne.cozens

Help! I am trying to write a query in access VBA. The query is doing
a count and inserting the figures into lots of different sub tables.
After all the counts are done, I want an update statement to update
one big table with all the figures.

The current code is:

DoCmd.RunSQL "UPDATE S_OFSTED_A SET ALL_ASD = (SELECT CountOfUNIQUE_ID
FROM S_OFSTED_ALL, S_OFSTED_A WHERE S_OFSTED_ALL.Eth_id =
S_OFSTED_A.Eth_id) "

S_Ofsted_A is my main table. It has a list of different ethnicitys
which each have an id. The table S_Ofsted_ALL contains all the count
firgures for the ofsted code 'ASD'. I want to populate the collum
ALL_ASD with the figures from S_OFSTED_ALL where the ethnicity fields
equal each other - so that for each ethnicity I am getting several
different figures.

Am I going about this completely the wrong way? Currently, when i run
this code i get runtime error 3073: Operation must use an updateable
query!!

What am i doing wrong?

Thanks for your help!

Laura
 
S_OFSTED_A is probably not updateable itself, and even if you don't update
it, Jet marks the whole query using it as not-updateable.


A possible solution is to push the data from S_OFSTED_A into a (temp) table,
and use that table, in your update query.


Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel

Thanks for your help but that didnt seem to work. Is it because of
the INSERT INTO command rather than creating a temp table??

However, I have just built an update query in access doing what i
needed and copied the sql code - it worked first time! God only knows
where i was going wrong but its working fine now!

Thanks for your reply though...

Laura
 
INSERT INTO should work, even if the data you inserted come from a
not-updateable query. If it does not work there is probable either a problem
with the insert into query syntax, or a compatibility problem, such as
appending a text value to an existing column that can only hold numerical
data (or a data relation integrity rule is not respected, etc.). It is NOT
required to be a query that creates a table.


Fine to see you did get a solution anyhow.


Vanderghast, Access MVP
 
Back
Top