Update query for stock levels (1 Viewer)

[monk_1987]

Hi guys

I've honestly been on this topic for a few days and can't figure it out :banghead: I have searched this forum and google for hours on end without finding an answer. So if someone could point me in the right direction or help me here that would be awesome

The problem
I have a form which has a list of stock we have available to sell. I can then create an invoice and put in the relevant details. I then can select a product and it will add it to the invoice (which is in the form of a subform). Then the user confirms the quantity of each product. This is where the problems start....

I want an update query which will subtract the quantity from the overall stock level and give me the new stock level. This is easy to do if the invoice is for only 1 product. But I it doesn't work if there is more than one product in the invoice. If the first product has a quantity of 2 being bought, it will subtract 2 for every product.

I hope this makes sense. Thanks in advance
Rhys

 

[plog]

Sounds like you've set up your tables incorrectly. You don't store data you can calculate; you calculate it. That means you should not have a field anywhere in your Tables that tells you how much of an item you have in stock, you should have a query that does that. The query would sum up all additions and subtractions to inventory for a product and determine the current inventory level using that method.

 

[monk_1987]

Thanks for the reply. The query does do the calculation for me. It just get the data from the table to see how much is currently in stock. Then I want the query to update that stock count (in the table) as it has been changed due to the order.

 

[plog]

And I am saying if you have the query that gets the inventory total, you don't need to update any table with it. You don't store a calculated value, especially one that is frequently changing. You simply refer to the queyr when you need inventory totals.

 

[CJ_London]

you really, really do not want to be updating a table with the current stock level, the number of issues you will get are too many to name. For a start you will need to think about what happens if the sale number is wrong and has to be corrected, or you have returns, or something is removed from stock, but not invoiced.

Much simpler to have a table of stock transactions - quantity in/out with a date, transaction type (delivered in/invoiced out/adjustment, etc) and document reference (e.g. invoice number or goods note number) - this is where your invoice line would be - then to determine your current stock, simply sum the quantity in/out column.

 

[monk_1987]

Right. I see what you're saying now. Apologies... Just one last question then to clarify. Put the stock level in a query and create a transaction table which can trace the orders?

 

[plog]

It sounds like you already have a transaction table with all stock movements. How else are you creating that query you said you have?