Subtract one cell from another

[mad-q]

Hi, I have been looking for some time.

Two tables, one with stock in and another with estimates & sales. When an estimate is converted to an invoice I would like the number of items sold on the invoice to be deducted from the stock qty.

Many Thanks
Iain

 

[JANR]

An Updatequery should do it, BUT it isen't advised to store calculated values. What you are trying looks like inventory calc, so read this article of the many pitfalls of doing it this way. http://allenbrowne.com/AppInventory.html

JR

 

[ajetrumpet]

Hi, I have been looking for some time.

Two tables, one with stock in and another with estimates & sales. When an estimate is converted to an invoice I would like the number of items sold on the invoice to be deducted from the stock qty.

Many Thanks
Iain

this is complex. but if this is your setup, pull the number sold from wherever you get it, and write a line of code. it's that simple really, unless your data is not setup properly:

docmd.setwarnings false

docmd.runsql "update table set " & _

"[stock] = [stock] - WHEREVER YOU GET THE SALE QTY FROM " & _

"where [identifier] = '" & CURRENT IDENTIFIER (on a form, or whatever) & "'"

docmd.setwarning true

 

[mad-q]

Many thanks to the above. I did have it so that it calc the total from currentLevel-stockSold but wanted to avoid it becoming slow.

P.S. Like the "How to talk to a programmer"

Think I will have it calc as it goes then perhaps at end of week / month to update the main stock file.

Thanks

 

[ajetrumpet]

P.S. Like the "How to talk to a programmer"

so do I. whoever wrote it should be commended. but they should also concentrate on getting out among people so they don't become a genius behind the computer but a half-zombie too. that's a sad future.

 

[mad-q]

Many thanks for the information, very useful.

I have been able to set up a running stock level but I am trying to get it to show it in a datasheet view. So that as I type in a part number for a client it will give me the running total depending on the part number in that row. At present it will give me a big error because it doesn't know which part number to use. I want it shown in datasheet view since it is simple to see all the parts ordered.

I have the following basic formula to work out.

Running Total = Stock - Parts Sold - Parts ordered but not despatched.

Many Thanks for any ideas.