Daily Stock Summary

[benc]

I am trying to design a db that handles a daily stock summary.

Tables:

tblPurchases
tblSales
tblStockSummary

tblStocksummary:

Date | Product Name | Start Stock | Tot Purchased | Tot Sold | End Stock

I am trying to figure out whats the best way round this.

1st Idea. to create some sort of query or code that when run would total puchases of each product for the day then add the results to the stock summary table and then do the same for the sales. It would then calculate the end stock. The end stock would then be copied to the start stock for the next day. There are always only going to be three products.

I need this as there has to be record of Daily Stock.

If this is the correct way about this, i dont have a clue about how to write the query or coding can anyone suggest any ideas or any examples i can look at. Thanks

 

[mohammadagul]

I am trying to design a db that handles a daily stock summary.

Tables:

tblPurchases
tblSales
tblStockSummary

tblStocksummary:

Date | Product Name | Start Stock | Tot Purchased | Tot Sold | End Stock

I am trying to figure out whats the best way round this.

1st Idea. to create some sort of query or code that when run would total puchases of each product for the day then add the results to the stock summary table and then do the same for the sales. It would then calculate the end stock. The end stock would then be copied to the start stock for the next day. There are always only going to be three products.

I need this as there has to be record of Daily Stock.

If this is the correct way about this, i dont have a clue about how to write the query or coding can anyone suggest any ideas or any examples i can look at. Thanks

Hi ,

I was also making an Inventory System For Stock Control... This is What i Did...

1. Make a Table for Purchasing Records by the name of tblPurchase
2. Make a table for Sales Records by the Name of tblSales

Make the Purchase order as Primary Key and Sales Invoice As Primary Key

3. Make a Table whihc contain you data for Stock Control and also data from tbl Purchase and tblSales. Name it as tblStock

Make realtion ship of both Table with the tblStock on Purchase order Field and Sales Invoice key.

4. Make a Query of Purchase Order using tblPurchase and tblStock.
5. Make a Query of Sales Invoice using tblSales and tblStock

6 Make a Union Query using the above two queries made in Step 4 and 5

you will now get both purchasing and sales in the same query thus giving you the stock purchased and sold ..... Customize this query.....

being a nice guy as i am ,,,, i have created a sample database for you and attached here .....

if you have any problem contact me on my email... mohammadagul@hotmail.com
hope you get it...

best of luck