Updatable query with a join

[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

 

[MikeAngelastro]

Yes. It does.

 

[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

 

[MikeAngelastro]

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