UPDATE
hist_cert_val
SET exemption_type =
(
select exmpt_type_code
from exemption
where exemption.property_id = hist_cert_val.property_id
and tax_year =
(
select param_value
from wheeler_params
where param_name = 'TAX_YEAR'
)
and status_cd = 2005
and property_id not in
(
select property_id
from temp_mult_exmpt
)
)
WHERE property_id in
(
select property_id
from exemption
where tax_year =
(
select param_value
from wheeler_params
where param_name = 'TAX_YEAR'
)
and status_cd = 2005
)
and property_id not in
(
select property_id
from temp_mult_exmpt
);


I'm thinking this is an updateable query. However Access returns this error

Operation must use an updateable query

:(

Suggestions?

TIA

Any number of things could be wrong. Run the query as a select query. How many rows does it return? Do any of the queries aggregate data? What happens if you use a join rather then subqueries? Access/Jet is usually better with joins than subqueries.

There are no aggregations; out of 2,674 possible rows it should return update 30 rows; I have tried joins and tried using QBE to build the query from the tables in the GUI. But get the same error. I am going to sleep on it tonite then run the queries inner-most to outer and see if there is some other error creating the heartburn.

I'm just a beginner, but here's a line that looks suspicious to me:

select exmpt_type_code
from exemption
where exemption.property_id = hist_cert_val.property_id


It looks suspicious cuz here you are referencing two tables even though you have mentioned only one table in the FROM clause. You might want to try mentioning both tables, or perhaps do an inner join instead. True, you do mention that table in the outer query, but I'm not sure that's good enough.

I can see that you are updating a table called hist_cert_val that has at least two columns called exemption_type and property_id.

o Are there any other columns in the table? Sometimes if a query leaves out any required columns, the dataset is returned as read ONLY.

Check your tables and make sure they all have primary keys.

Check your tables and make sure they all have primary keys.

property_id is the key field but is not set as 'primary key' in Access as they (with exception of the table being updated) are in the back end. The tables are linked and will not allow editing.

I am away from the office now but I will change hist_cert_value.property_id to primary key tomorrow and see what shakes out.

Thanks

If the tables you are joining do not have primary keys, Access cannot determine the cardinality of the relationship and may assume that the join is creating a Cartesian Product which is not updateable.

If the tables you are joining do not have primary keys, Access cannot determine the cardinality of the relationship and may assume that the join is creating a Cartesian Product which is not updateable.

Well I guess then I will need to create a temp table, assign a primary key, insert into the new table, then process the update using the temp table instead of the linked table.

OK. Doable - a pain in the ass but doable :D

Thanks

Why would you not open the back end and add a PK to the source table? All tables should have primary keys even if they are just meaningless autonumbers.

Why would you not open the back end and add a PK to the source table? All tables should have primary keys even if they are just meaningless autonumbers.

Because the BE is a SQL Server database not belonging to me and is proprietary.

Does your subquery:

(
select exmpt_type_code
from exemption
where exemption.property_id = hist_cert_val.property_id
and tax_year =
(
select param_value
from wheeler_params
where param_name = 'TAX_YEAR'
)
and status_cd = 2005
and property_id not in
(
select property_id
from temp_mult_exmpt
)
)


return more than 1 row per row in the outer query? If it does, or could, SQL server will give you that message. SQL Server knows that you cannot set a field equal to a result set and if it suspects you are doing that, it will choke. I'm not sure what would happen if you just returned the top 1 from that subquery but it would be interesting to find out. Same thing with "grouping by" exmpt_type_code. Both of these are quick things you can try.

Also, for the even inner more sub-query:

and tax_year =
(
select param_value
from wheeler_params
where param_name = 'TAX_YEAR'
)


you might want to change the "tax_year =" to "tax_year in". Same principle applies, if the subquery can return more than one row, you cannot use "=".

and tax_year =
(
select param_value
from wheeler_params
where param_name = 'TAX_YEAR'
)


This bit of code comes from a table that is cloned annually and those parameters that have need of change like 'TAX_YEAR' are audited/edited by the user. So no chance of TAX_YEAR being more than one value. There are four major entities using the database and have reason to call thing differently among them ... its a long story. But the parameters table is critical for creating global reports for the four.


and property_id not in
(
select property_id
from temp_mult_exmpt
)


The 'temp_mult_exempt' table is a table that is created annualy by script then populated annualy by script before this script is run. And is a one-to-one relationship with the update table ( 'hist_cert_val') so, there should not be a possible array to be returned. I am at home now but will double check the possiblility of having multiple entries in the temp table when I get back to the office - anything is possible - it would be a good pre-test to have anyway regardless.

Additional: I suppose it could be possible for there to be multiple 'exemptions', though none I can think of immediately. But will check that out as well and again, needs to have a pre-test mechanism anyway. Maybe a redesign of the process is in order?

Thanks for the heads up.

Why not start with

UPDATE
hist_cert_val
SET exemption_type = my exemption type

WHERE property_id = (a non-exempt property_id)

;

Where the bits in red are actual ligitimate values. This will at least prove you can update the target data.

Then add the subqueries a bit at a time until you can't update.

It goes without saying that you should take not of what you are changing and take a back-up or work on a copy first.

hth
Chris

It doesn't have to return more than one value for SQL Server to dump it. It just needs to look like it could return more than 1 value. Thus, using in on the inner-inner-query would resolve it if that were the problem and the inner query might look like it were only going to return 1 value if you used distinct/group by.

Chris' idea is quite good. Break it down into the tiniest components and build it up until it doesn't work. That'll tell you alot.

If you are ABSOLUTELY certain that the tables have logical primary keys that will result in identifying unique records, you can create psuedo indexes that Access will accept as fact. WARNING - if you do this and the indexes you define are not unique, you are likely to corrupt the server table.

Open Access help. In the table of contents drill down to the Microsoft Jet SQL Reference/Data Definition Language (DDL)/ CREATE INDEX.
This will give you the code you need to create a psuedo index and hopefully, that will make your update query work.

Access help may not find the create index help entry if you search for it which is why I suggest the drill down method since that works regardless of your Access version.

I have encountered this error on nearly every update query I have attempted with few exceptions. It seems if the table relationship is 1:M then Access will not allow an update. Most of the time I am running update queries because I have created a table to 'pivot' data into. Which by its nature is attempting to bring data from the many side of the relationship into an aggregation of some kind of filter data base on some code field or type field, etc.

For MS to default to 'undoable' status just because there is 'normally' a 1:M relationship seems to be absurd to me. For instance if a SELECT Count(*) From TheManyTable WHERE MeetsSomeCriteria is used to populate a field ( as a logical table with a 1:1 relationship ) in the created tables' field then it is no longer a 1:M but a 1:1. Yet access will not allow it, presumably, because the normal relationship is 1:M.

:confused: Isn't the rendering of data from multiplicity the whole idea behind various action queries?

In a relational database, it is considered poor practice to store calculated values. No RDBMS makes this easy. So, if that is the problem, you will need to make temp tables to store the aggregation. Don't forget to add PKs to these tables.

Did you try creating the PSUEDO indexes? Your DBA may have not given you access to the actual tables but to views instead. Jet MUST know the primary key of a table in order to update it.

In a relational database, it is considered poor practice to store calculated values. No RDBMS makes this easy. So, if that is the problem, you will need to make temp tables to store the aggregation. Don't forget to add PKs to these tables.

Did you try creating the PSUEDO indexes? Your DBA may have not given you access to the actual tables but to views instead. Jet MUST know the primary key of a table in order to update it.

I knew that was coming...

It's an example of how to loop through fields and use select case and a function return. Of course I would not store calculated values - just wanted to give an example (however loose) :cool: