By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,689 Members | 1,121 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,689 IT Pros & Developers. It's quick & easy.

UnitsInStock Update Query

P: 3
Going over the Northwind sample database for practice, I added an update query to the units in stock field of the products table. The scenario being that as a product is ordered, the unitsinstock field is updated accordingly. However, every time I run the query, it updates all transactions on record not just the current transaction.eg I have 10 products and 4 are ordered, I run the update query giving me 6 product in stock. 2 more are ordered and the query is run again BUT it now takes those 2 PLUS the previous 4 (again) leaving me with no products when I should have 4 remaining. How do I fix this please?

Thanks
Jun 8 '07 #1
Share this Question
Share on Google+
4 Replies

Rabbit
Expert Mod 10K+
P: 12,441
Going over the Northwind sample database for practice, I added an update query to the units in stock field of the products table. The scenario being that as a product is ordered, the unitsinstock field is updated accordingly. However, every time I run the query, it updates all transactions on record not just the current transaction.eg I have 10 products and 4 are ordered, I run the update query giving me 6 product in stock. 2 more are ordered and the query is run again BUT it now takes those 2 PLUS the previous 4 (again) leaving me with no products when I should have 4 remaining. How do I fix this please?

Thanks
I don't know, it would help to see your update query SQL. Can't fix it if we can't see what you're using.
Jun 8 '07 #2

P: 3
It's a standard update query where [UnitsInStock]=[UnitsInStock]-[Quantity] but I want it to just work off that particular order not the entire database. The Products and OrderDetails tables are listed in the query. I've heard about a dynamic query attached to the orders form but don't know how to do this or even what it is..... :)

Thanks again
Jun 9 '07 #3

Rabbit
Expert Mod 10K+
P: 12,441
It sounds like you forgot to include a WHERE clause.

Expand|Select|Wrap|Line Numbers
  1. UPDATE Table1
  2. SET UnitsInStock = UnitsInStock - Quantity
  3. WHERE UniqueKeyField = ChosenUniqueKey;
  4.  
Jun 10 '07 #4

P: 3
Thanks Rabbit, I'll give it a go and let you know. I've also come up with a function from allen browne (www.allenbrowne,.com) but i'm not sure where it goes or how to initiate it so i'll work on both.

Thanks

Jim
Jun 21 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.