Hello guys,
Im trying to make a sales database and need few opinions and help
My goal is to make a database which will manage the sales, billing, and orders from "my" warehouse when my stock level is low.
What I had in mind is to make next tables:
Another thing I want is... Lets say I have a field in the products table which is "stock level", it defines which quantity of a particular product I have (lets say 100 notebooks). Now when I go to finish an invoice which contains 10 notebooks, I would like the stock level of notebooks to change into 90, so 100 - 10 = 90, also if the stock level of notebooks is 0 to get an error that there are no notebooks available and cant proceed, will have to order from warehouse.
One more thing. Lets say the Quotation will have 5 statuses, active, approved, invoiced, expired and rejected. Now if the Quotation is approved we can invoice it, now when I do that I would like that access creates a new invoice with all the information from the Quotation. Dont know how I could make it.
Anyhow, priority for now are the tables and the relationships.
This is in short, I tried to explain good enough
If anyone could give some suggestions and fixes to this I would be grateful, and also if you need more info please ask.
Thanks
Im trying to make a sales database and need few opinions and help
My goal is to make a database which will manage the sales, billing, and orders from "my" warehouse when my stock level is low.
What I had in mind is to make next tables:
- Customers (which will store basic information of customer)
- Employees (basic info of employees)
- Quotation (offers which I make to my customers, number of the quotation, date when it is made and information of the customer, company info and which employee made the Quotation)
- Quotation details (just an extended version of Quotation, which will make it easier to create forms and reports based on it, i guess. It will contain the products which I want to sell or services and the quantity)
- Invoice (Recipe for customers, similar to Quotation)
- Invoice details (same as Quotation details)
- A table of a paper for customer returns (didnt figure out the name yet. However, It will be something like a warranty. It will contain on which invoice is the return based, the costumer and the item/s which hes returning)
- Products/Services (there I will store the products and the services the company offers. Thinking to separate them with a check box, to make 2 check boxes, one "is service" and another one "is product". So to check if its a product or a service. Or is it better to make 2 separate tables?)
- Last table gives me most of the trouble. I want a paper which I will send to the warehouse when some of the products are low on stock. Dont know exactly how to name it and what should it contain, some help here would be great.
Another thing I want is... Lets say I have a field in the products table which is "stock level", it defines which quantity of a particular product I have (lets say 100 notebooks). Now when I go to finish an invoice which contains 10 notebooks, I would like the stock level of notebooks to change into 90, so 100 - 10 = 90, also if the stock level of notebooks is 0 to get an error that there are no notebooks available and cant proceed, will have to order from warehouse.
One more thing. Lets say the Quotation will have 5 statuses, active, approved, invoiced, expired and rejected. Now if the Quotation is approved we can invoice it, now when I do that I would like that access creates a new invoice with all the information from the Quotation. Dont know how I could make it.
Anyhow, priority for now are the tables and the relationships.
This is in short, I tried to explain good enough
If anyone could give some suggestions and fixes to this I would be grateful, and also if you need more info please ask.
Thanks