Sales database

[Hello1]

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:

• 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

[CJ_London]

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.

you should not store the stock level - but calculate it

You should have a table - something like

tblStockMovements
StockMovementPK autonumber
StockFK long - link to your products table
MovementDate date
MovementType text - e.g. opening stock, stock adjustment, stock in, stock out
Quantity number
DocumentID number/text - e.g. invoice number for stock out/PO number for stock in

then your current stock is simply the sum of the quantity for a particular stockFK. Other benefit is you can calculate your stock at any time by using a date range on movement date and if movementtype includes 'On Order' (for stock due in) and 'Reserved' (for stock due out') you can see what your future stock will look like as well.

[jwhite]

Cross-posted: http://www.utteraccess.com/forum/ind...ic=2040762&hl=

[Hello1]

Sorry, didnt know about the cross posting "rules". Here is the other forum I posted this http://www.access-programmers.co.uk/...16#post1512916
Thanks Ajax, I will check that out, not yet 100% clear to me but hope the other posts will help to connect it

[CJ_London]

there no problem with cross posting providing you let people know. As a newbie, you wouldn't be expected to know....

[Hello1]

Do you maybe have an example of "tblStockMovements".It would help a lot, but now when I read it few times I start to understand it a bit, i think. Guess I could calculate my current stock of a particular item by dividing "stock in" from "stock out"? Could you describe "opening stock" and "adjustment stock", what does it exactly mean?

[CJ_London]

not an example per se but the data would look something like

StockMovementPK..StockFK..MovementDate..MovementTy pe..Quantity..DocumentID
1..........................1...........01/01/2016...... Opening Stock...125.........""
2..........................2...........01/01/2016...... Opening Stock...97...........""
3..........................2...........03/01/2016...... Stock In............10...........PO1234
4..........................1...........04/01/2016...... Stock Out.........-50...........INV12456
5..........................3...........05/01/2016...... Stock In............20...........PO1275
6..........................1...........06/01/2016...... Stock Out.........-25...........INV12388

on 04/01
Stock 1 has 75 items in stock
Stock 2 has 107 items in stock
Stock 3 has 0 items in stock

query would be

SELECT StockFK, sum(Quantity) as stock balance
FROM tblStockMovements
WHERE MovementDate<=[Enter Date]
GROUP BY StockFK

on 06/01
Stock 1 has 50 items in stock
Stock 2 has 107 items in stock
Stock 3 has 20 items in stock

query would be

SELECT StockFK, sum(Quantity) as stock balance
FROM tblStockMovements
GROUP BY StockFK

you can link tblStockMovements to your products table on the StockFK field and bring the product description from there rather than using the StockFK number

similarly you can link the documentID field to your invoice header table or PO order header table and from there link to your customer or supplier table to pick up the name of the customer/supplier. This would involve using left joins which may be beyond you at the moment, but you get the gist of how it works

[Hello1]

Ok thanks a lot, now I understand much more
Just instead PO (which is purchase order, I assume) I would use just Order, because I dont pay for the goods, Im assuming that Im ordering from a big warehouse which my company owns, and Im just one of many small stores which my company owns, or something like that. So I will send them just an order document which contains the goods and quantity I need, without price, coz they dont need it, I guess.
So how sounds the rest of the database, should I proceed with making the tables including yours now?

[CJ_London]

So how sounds the rest of the database, should I proceed with making the tables including yours now?

I understand this is an education project - take the advice on the other threads and talk to your colleagues first and at least agree the 'interface' between each area i.e. who needs to know what from each area for their own area to work.

[Hello1]

Hi again guys.
Got a quick question. Why doesnt the supplier have any relationship with products but yet the supplier IDs are in the products table? Should there be a connection or is it better to just make a combo box like in this example? This is from the Northwind template.
Thanks

[CJ_London]

from the + to the left of supplierIDs in the products table, this is a multivalue field. Implication is that a product has or can have multiple suppliers. If you open the field (click on the + button), you may find a connection, but if not, you can still trace back via the purchase orders. Google multivalue fields to learn what they are and what they are not. Part of what they are is they provide a many to many relationship table - but that is hidden in this view and as far as I understand it cannot be shown.

If a relationship does exist, or you created it, be aware you then have a relationship 'loop' via the direct relationship and also the purchase orders. The means that when the user comes to complete a record in purchase order details table, both supplier and product must exist and you can only select products supplied by that supplier - which makes sense from a business rules point of view - but may have data management issues if for example it is decided to move the order from one supplier to another

[Hello1]

The means that when the user comes to complete a record in purchase order details table, both supplier and product must exist and you can only select products supplied by that supplier - which makes sense from a business rules point of view

I think this is what I need. I dont think that there is a product produced by 2 or more different suppliers, in my case. When it comes to that part, after I chose a supplier, are there automatically going to be only the products which the supplier offers or all of the products in the combo box and only the chosen suppliers products will be selectable? If not, can I make only the chosen supplier products appear in the combo box and does it require VBA or macros? :/

Off to bed now, u dont have to hurry

[CJ_London]

I chose a supplier, are there automatically going to be only the products which the supplier offers or all of the products in the combo box and only the chosen suppliers products will be selectable?

that depends on your form design - relationships are only about how the data works together, they are not query joins although they look the same, much like the views of tables and queries.

If not, can I make only the chosen supplier products appear in the combo box and does it require VBA or macros? :/

yes - use a query. products would normally be chosen using a combo box, the query would be the rowsource to the compo box - look at the northwind example

[Hello1]

Is this any good. Im making this database in another language so I did a fast translation, there might be some mistakes. Anyhow, would like to know are the relationships any good and do they make any sense and what to change. Thanks
Dont have time now but will describe more what my goal is a bit later.

https://1drv.ms/u/s!AgExhJ1MpmCGiG4v-lVG9DMgqyaj

[CJ_London]

without knowing your business in detail, it is not possible to say. At a glance it looks OK, but I have no idea whether it does what you want.

Need to know what it is the business does (retail? online? both?), the various processes that occur (e.g. customer order comes via post? face to face? telephone? web site?, user A then checks stock (physically? from records? and if there is sufficient they do this, otherwise they do that). The various business rules that apply (e.g. order quantity must not exceed X, outstanding balances with suppliers must not exceed Y etc.