Updatable query with a join (1 Viewer)

[MikeAngelastro]

Hi,

I'm trying to run the following query:

"UPDATE Inventory INNER JOIN qryItemReorderPoints ON Inventory.[Item Number] = qryItemReorderPoints.[Item Number] SET Inventory.[Reorder Point] = qryItemReorderPoints.ReorderQuantity;"

But I keep getting the message "Must be an updatable query."

Checking KB Article 328828 has all the conditions that would cause this message but this one doesn't fit any mentioned in the article. The join is a one-to-one and there should be no ambiguity. I don't want to have to create a cursor to do it. Any ideas?

Thanks,

Mike

 

[Pat Hartman]

Does qryItemReorderPoints contain aggregate functions?

 

[MikeAngelastro]

Yes. It does.

 

[Pat Hartman]

That's why the query is not updatable. Jet requires that ALL components of a query be updatable and since aggregate queries are NOT updatable, any query that references an aggregate query will be not updatable also.

Storing aggregated data is poor idea anyway. You should calculate it as you need it. If you insist, you'll either need to use a cursor (slower) or turn the aggregate query into a make-table query and then join to the temp table to get the aggregate values.

 

[MikeAngelastro]

Thanks Pat. I think I will use a make table query and destroy the new table after using it.

I agree with you on the storing of aggregated data. But I am modifying an existing program that has a reorder point field in the inventory table and all I have to do is update that field once a month based on the prior three month's sales. I'm just trying to have as little impact on the original program as possible so I don't break it.

Mike

 

[MikeAngelastro]

Hi,

Pat has said that with JET, if any of the queries in a chain uses aggregation, you can not update a table using it. Does anyone know if this is also true with SQL Server?

Thanks,

Mike

 

[Pat Hartman]

SQL server is a relational database also. Try it but I don't think it will work. You cannot do it via an Access query. You would need to create a pass-through query to have any chance of it working.

 

[MikeAngelastro]

Thanks Pat. I would create the queries in SQL Server and link them to Access. I will give it a try.