Update query help

[KenshiroUK]

Okay this is very simple, I wish to create a query based on my sum to update my stock count.

I have created a query that performs a sum of taking what was ordered and subtracting it from what is in stock. This returns a figure of how many are left, what I wish to do is take that figure and update my stock in my Inventory table. My stock level in Inventory is Count and in the query the final sum is called 'Left in Stock'.

My only problem is when a new order is placed and I run the query again I do not wish for duplicate orders to be subtracted off stock, so when a customer orders another of the same product it does not count the original order again, only the new one.

 

[Guus2005]

That is proces data and should not be stored.
Like you said it creates all kind of problems when you do.

Use a query to show your current stock instead.

 

[KenshiroUK]

Wont that mean I'd have to manually update each stock count when a customer buys a particular product? I can just somehow create an update query to automatically change the quantity of stock?

 

[RainLover]

If you go to Allen Browne's site he has the information on Inventory.

I have used it a lot and found it very fast and accurate.

 

[Guus2005]

I don't know what your database looks like but when you have a Stock count and you want to know what the current stock is just subtract every item sold from the original stock count. Which is what you said:

I have created a query that performs a sum of taking what was ordered and subtracting it from what is in stock. This returns a figure of how many are left, what I wish to do is take that figure and update my stock in my Inventory table.

The blue part is the query you use to determine the stock left. Lets call this query qryStockleft. (Id, stockleft)

You can use this query in combination (JOIN) with the inventory table to show the actual stock for each item (the red part). Call this query qryInventory.

When you want to see the actual stock at any time just open qryInventory.

HTH

 

[RainLover]

In my signature there is a link to some sample Databases.

Download the one under Inventory Control.

You can't really do this by queries as the amount of data can become quite large so the query would take ages to run. These is also spoilage to take into account. This is done by stocktakes.

So every now and then you need to store the current value. End of Month would be a good time but this all depends on the type of business you are running.