gcresse (9/1/2011)
The spreadsheet calculations are correct. I forgot to mention in my original post that the product is liquid and stored in a tank. As new receipts come in, they are mixed with the current inventory, changing the overall ingredient percents, so that the next shipment must take into consideration the new percents.I have been looking at Jeff Moden's article about Quirky Updates and wonder if somehow I can use that logic. The biggest issue I see is that I have multiple "running total" variables to keep track of (several ingredients for the same product) so I'm not sure if I can make it work. Has anyone used any variation of the Quirky Update that might work for this?
The quirky update will handle more than one running total...see
http://www.sqlservercentral.com/articles/T-SQL/68467/ Fig 19 in the article
I would suggest that you read the article at least twice (or even twenty times 😀 ) and then make an attempt in a safe (non prod) environment...I am sure that if you get stuck and post back the code you have tried , then I would expect someone, if not Jeff himself, wil chime in.
Based on my own experience ....if you do have problems and need to post back, then please provide a decent set of test data that is easy for us to use (see link in my sig) and also, do not try to over simplify your table and data....this quite often leads to you not getting the results you really need.
The quirky update has a lot of rules that MUST be used.....so read the article again 😀
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day