Update Query

[mary.stocks]

Hi

I have a query that takes the sum of the quantities of different stock from an order table and an update query that takes those figures and takes them off of the quantites in a stock table but when i run it it says operation must use an updateable query. Any ideas


thanks

 

[Rob.Mills]

How do you have the queries setup? Are you actually trying to run the update on the query that performs the calculations? If so, a query that does calculations can't be updating and that's where you're getting the error.

I haven't actually tried this but you might try creating a query that includes that actual table joined to the query that creates the sums and updating the table that way.

 

[neileg]

Slightly off the point of your question, but you should not store stock values in a table. You should cacluate these every time. There are many reasons for this, but the most obvious is that running your update query twice will subtract the orders twice, and throw out all of your stock balances.

 

[mary.stocks]

I have but i cant work out how to get it to alter the stock levels when i pit an order in so if you can give me any pointers it would be much appreciated

thanks

mary

 

[neileg]

You should do a search on here for stock and inventory. There is a lot of traffic on this type of application. There's also an inventory wizard in Access.

Basically, the stock balance is the sum of all stock inwards - sum of all stock outwards. I don't know if by orders you mean stock in or stock out! I also don't know how you have structured your database. I would have three tables. One for stock in, one for stock out, and one for stock adjustments. If I needed the balance on a form, I would use a query on each of these tables, and total these to give the balance.

 

[Rich]

http://users.bigpond.net.au/abrowne1/AppInventory.html