Trying to update rows based on a count variable

[arstein]

Apologies if this is a basic question. Here's my situation:

I'm trying to update rows in a table based on a count of items in the same table. The table in question contains order line items. On each line item, I'd like to store the total number of items attached to that order (because it affects how the individual line items are processed).

I have a query that seems like it should do the trick, but Access doesn't like it:

UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");​

Here's the error I get:

Operation must use an updateable query.​

Thoughts?

 

[llkhoutx]

I think that your sql, particularily the subquery, is too complex for Access to update on.

I would save the count on a hidden unbound form field, the use that field in a simple update query.

 

[arstein]

OK. I'm not really sure why this is too complicated -- Access doesn't seem to have trouble parsing it, it just seems to object to the nature of the query, leading me to wonder if there is an approved way to do the same thing.

But if your method works better, I'm game. I'm also an Access neophyte, so any chance you can provide more details on how to do what you suggest?

 

[RV]

You forgot to add an alias in your statement for the subquery:

UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales AS S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");

RV

 

[arstein]

Thanks. But I still get the same error.

 

[llkhoutx]

There are multiple ways to do just about anything in Access without any appreciable overhead expense.

Elegant queries sometime eat your lunch. Multiple simple queries run sequentially are almost always substantially faster that one elegant query.