By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,453 Members | 937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,453 IT Pros & Developers. It's quick & easy.

Stock Control Database

P: 5
Hello all,
I'am currently working on a stock control database of a small business.The business deals with buying products from suppliers and store it in the shrowroom. Then these products are sold to customers.

I have made the database so that it will calculate the product quantity level after sales and purchases and also product a reorder report.

The attachment shows the table design of my databae.http://www.postimg.com/image/155000/capture-154094.jpg
Can someone check my relationships if it is correct??

I need help for calculating the product quantity level.

Shall I use queries for that...that is one delete or update query that will decrease the quantity in the tblProduct upon Order( Sales/Customer Order). Also an append query for adding to the quantity in the tblProduct upon purchase Order

Thanks in advance.
Jun 29 '14 #1
Share this Question
Share on Google+
1 Reply


burrina
P: 6
Here is SQL code for tblProducts that may help you.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProducts.ProductID, tblProducts.ProductName, Sum(tblProducts.Inventory) AS StockLevel, Nz([ReOrderLevel],0-Nz([StockLevel],0)) AS ReStockAmnt, tblProducts.ReorderLevel
  2. FROM tblProducts
  3. GROUP BY tblProducts.ProductID, tblProducts.ProductName, tblProducts.ReorderLevel;
  4.  
HTH
Jun 29 '14 #2

Post your reply

Sign in to post your reply or Sign up for a free account.