Using an calculating update query

[KenshiroUK]

Hi guys, I wish to use a calculation to update my stock. Now I know you're not supposed to do this however I do not see any way around this to automatically update stock, as requested by my manager.

I have created this function Left In Stock: [count]-Sum([quantity-purchased]) which returns my figure I have based this on date in a select query. All I wish to do is just update my Count with left in stock on an update query.

My query only shows what customers have bought from the stock. Would there be anyway to use a select query and show all products that includes the calculation?

 

[jdraw]

You aren't using Count as a field name I hope.
see http://allenbrowne.com/AppIssueBadWord.html for Access ' reserved words.

Also, you should avoid naming conventions that use special characters (-) in field and object names. For ease of use and avoiding syntax errors stick to alphanumerics and underscore only.

If you do a StockTaking, you could calculate inventory by (untested)
LastStockTaking - Sum(PurchasedQuantities) + Sum(NewStockAdded)


LastCount - removedFromStock + addedToStock

 

[KenshiroUK]

You aren't using Count as a field name I hope.
see http://allenbrowne.com/AppIssueBadWord.html for Access ' reserved words.

Also, you should avoid naming conventions that use special characters (-) in field and object names. For ease of use and avoiding syntax errors stick to alphanumerics and underscore only.

If you do a StockTaking, you could calculate inventory by (untested)
LastStockTaking - Sum(PurchasedQuantities) + Sum(NewStockAdded)


I am unfortunately. However I did not develop this database, I'm just using whats been given to me. The problem is what I have been told to do whether its the right or wrong thing to do. Any other way I can use my calculation to just update the quantity of stock?

 

[jdraw]

Why do you have to Update the value in a table? You can calculate the value in a query, and you can run the query when needed? Or you could use the query as the record source of a form.

It is NOT a good practice to store Calculated values in tables.

That does not mean you can't do it. It just says you could be introducing more trouble than you think.

 

[KenshiroUK]

Like I said mate, its basically the way he wants it! Until I have time after christmas to rewrite and create a brand new DB.

The way it was explain that when we send reports to Amaazon and other companies they want it in a text file. Now if I create a select query just on calculated values, when we send that inventory text file nothing will be updated in it.

Not really sure on how to get around this.

 

[RainLover]

I can't see a way around storing the Stock take value. I believe it is the correct thing to do in spite of what others will tell you.
Even Allen Browne who is classed as a Guru stores Stock take Values.
The big problem is that you need to make each new calculation based upon the Date of the last Stock Take.
So you need, Value of Last Stock take, Plus purchases from that date Less Sales from that date. And this has to be done for each and every product you have.
I could not do this in a query but I can using VBA. This is provided you have the correct Data stored in an acceptable structure.
You could go to Allen Brown's web site for detailed instructions. Google will find it for you. Look for his article on Inventory Control.
For a working example go to my link in my signature for a Database I wrote some time ago. Again look for Inventory Control.
This is a case for breaking the rule for storing Calculated Values.

 

[KenshiroUK]

thanks! I basically did a create table query that is based on date. So when I enter today day it pulls up all orders of today, and what we have in stock and what has gone out.

 

[RainLover]

thanks! I basically did a create table query that is based on date. So when I enter today day it pulls up all orders of today, and what we have in stock and what has gone out.

Your point is what. Where do you go to from there.

 

[jdraw]

Here's the link to Allen Browne's info
http://allenbrowne.com/appinventory.html

What exactly does this mean?

I basically did a create table query that is based on date. So when I enter today day it pulls up all orders of today, and what we have in stock and what has gone out.

 

[KenshiroUK]

Here's the link to Allen Browne's info
http://allenbrowne.com/appinventory.html

What exactly does this mean?

well I created a select query with the sum method subtracting quantity-order from qnty with returns what we have in stock. Now I have added a filtering of data that prompts the user to enter DD/MM/YY. When you enter that say I selected today's date, it returns all the orders that was placed on stock for today.

 

[RainLover]

jdraw

If you are familar with Allen Brown's concept I will get out of the way and let you steer the OP in the right direction.

 

[RainLover]

KenshiroUK

Are you winning. If not where are you up to.

Have you looked at the links provided.

 

[jdraw]

Rain,
I know you have an inventory application, and are trying to help this poster.
I'll bow out of this one.
There are plenty of issues to go around.

Till next time.