how to subtract product insert into order from store

• Question

• Hi for all

I have Store System the main table is product I went to

- when insert new item into the product table if it exist then the quantity of this product increase by one

if it not exist in the table it insert to it .

- when make new order then the quantity of decrease from quantity of main table unit the quantity be zero

then the product not appear lookup of the order

thanks a lot for all menbers

Thursday, September 15, 2016 2:09 PM

• Hi,

The usual approach to calculating inventory on hand is to simply add a transaction record with a positive number to increase inventory and a negative number to decrease it. In this way, adding up (Sum) the transactions should give you the quantity on hand.

To remove a zero quantity from the "lookup," add a criteria against Qty of more than (>) 0.

Hope it helps...

Thursday, September 15, 2016 2:32 PM
• No. Calculations are mostly done when needed. Storing calculated values in tables is mostly not recommended.
Thursday, September 15, 2016 7:35 PM

All replies

• Hi,

The usual approach to calculating inventory on hand is to simply add a transaction record with a positive number to increase inventory and a negative number to decrease it. In this way, adding up (Sum) the transactions should give you the quantity on hand.

To remove a zero quantity from the "lookup," add a criteria against Qty of more than (>) 0.

Hope it helps...

Thursday, September 15, 2016 2:32 PM
• Hi,

The usual approach to calculating inventory on hand is to simply add a transaction record with a positive number to increase inventory and a negative number to decrease it. In this way, adding up (Sum) the transactions should give you the quantity on hand.

how to do that !!
Thursday, September 15, 2016 6:32 PM
• When you receive inventory, you enter a positive number. When you take from the inventory, you enter a negative number. To get quantity on hand, you add all transactions together. For example:

9/2/2016, Sold Items, -5

9/3/2016, Sold Items, -2

9/5/2016, Sold Items, 10

Total quantity on hand = 8

Hope it helps...

Thursday, September 15, 2016 7:22 PM
• [quantity of product ]-[quantity on the order]

remainder value after subtraction must return to the quantity field of the product table

Thursday, September 15, 2016 7:31 PM
• No. Calculations are mostly done when needed. Storing calculated values in tables is mostly not recommended.
Thursday, September 15, 2016 7:35 PM
• You might like to take a look at Inventory.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates the basic methodology for a simple inventory database, and computes the current stock levels in the way the DBguy describes, allowing for adjustments as a result of stock takes.

Ken Sheridan, Stafford, England

Thursday, September 15, 2016 9:46 PM