Storing live data? (Stock control help)

[nicksource]

Hi guys,

I'm new to Access except using it years back but much has changed from the 2000 version to the 2007 version - it's slowly coming back to me though, as it's mostly just an interface change. Although I'm highly experienced in SQL (mySQL) and database design / structure from being a Web Programmer.

I'm wanting to store a stock amount for products. Using 3 tables, for products, orders and sales. Here's a real basic layout:

[U]products[/U]
[COLOR="Red"]prodID [/COLOR]| productName | productStock

[U]orders[/U]
orderID | supplier | [COLOR="Red"]prodID[/COLOR] | quantityBought

[U]sales[/U]
saleID | customer | [COLOR="Red"]prodID[/COLOR] | quantitySold

The products table is just a list of the products. Sales is where I log what I sell to customers and the orders are what I buy from my suppliers.

What I'm wishing to do is use the products table to store the stock of that item so as soon as I make a sale, that quantity for that prodID is subtracted instantly from the productStock and when I make an order it's instantly added.

Is this possible, or is there a good way to do this?

All help appreciated!

Thanks,
Nick.

 

[pbaldy]

There's a pretty good discussion on the topic here:

http://allenbrowne.com/AppInventory.html

it continues after the function, so make sure you go all the way down. Personally I wouldn't store the value. I've written an app that includes inventory, and I just calculate on hand from transactions.

 

[neileg]

I agree with Paul. There's lots of discussion in these forums, too, on stock control or inventory.

Consider combining your sales and orders tables into one. If you hold orders as a positive quantity and sales as negative, a simple sum on the table will give you the current stock.