Sales database (1 Viewer)

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

With regard to:-

Hello1 said:

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.

Click to expand...

I would suggest reading Allen Browne's article on this issue:-
Inventory Control: Quantity on Hand

Allen provides a description of the issues and some VBA code that you might well be able to adapt to your database.

I'm just going to scattershot my thoughts:

1. This database has 2 sides--Sales & Inventory. I would work on this like it is two databases--1 for inventory, 1 for sales. Then after I have them working independently I would try and merge them.

2. You would not store stock level in a table. You would calculate it based on all the debits and credits you have made to inventory.

3. You probably shouldn't have seperate tables for invoices and quotes. Instead a quote should turn into an invoice by marking a field--perhaps by using that status field you mentioned.

My advice to starting a new database is to open up excel and make a huge spreadsheet of data that you expect the database to accomodate. Add columns, then fake data beneath each trying to cover all cases you expect to encounter. Once you think you have enough data and it captrues everything you could possible want--then you move to Access and build the tables/fields to accomodate your data. Finally, set up your relationsips tool and post a screenshot here and we can help debug it.

I hadn't noticed that before! Allen Browne actually mentioned the Northwind database. I was going to mention that as a base for you to start out from. Bear in mind that it is a sample database and not meant for production use.

If you decide to have a look at the northwind database you might find this video helpful:- VBA Beginner - Setup the Northwind dB - Nifty Access

crossposted here

http://www.accessforums.net/showthread.php?t=63127

and here

http://www.utteraccess.com/forum/index.php?showtopic=2040762&hl=

@hello1, please read this thread
http://www.excelguru.ca/content.php?184

plog said:

I'm just going to scattershot my thoughts:

1. This database has 2 sides--Sales & Inventory. I would work on this like it is two databases--1 for inventory, 1 for sales. Then after I have them working independently I would try and merge them.

2. You would not store stock level in a table. You would calculate it based on all the debits and credits you have made to inventory.

3. You probably shouldn't have seperate tables for invoices and quotes. Instead a quote should turn into an invoice by marking a field--perhaps by using that status field you mentioned.

My advice to starting a new database is to open up excel and make a huge spreadsheet of data that you expect the database to accomodate. Add columns, then fake data beneath each trying to cover all cases you expect to encounter. Once you think you have enough data and it captrues everything you could possible want--then you move to Access and build the tables/fields to accomodate your data. Finally, set up your relationsips tool and post a screenshot here and we can help debug it.

Click to expand...

The thing is, my project is for sales and someone else got the module for ordering, another one for human resources and guess inventory is a module itself. So if I would go only with sales, what should I exclude? But on the other side I dont see how sales would work without inventory, well, I guess it would but not completely. To make it short, its a college project and we got different modules which we will merge into one database later. My question is should I include inventory or exclude it?
Thanks a lot for the reply.

Grizmo thanks, I will check it out.

Edit: sorry for the cross posting guys, I was interested in as many suggestions as possible (and is the first time I heard about cross post, but yet I knew how to do it ). Links which CJ posted are the other 2 posts of me, there are no more. So you can check the answers there too.

Sounds like you need to talk to your group and/or professor to find out if you should include inventory.

My guess is this is a group project and you guys thought you could compartmentalize the pieces so you each could work independently. I don't think that's possible. I think you guys need to get together and hammer out your tables. If not the exact details, you do need the broad strokes--table names, how they are related, key fields & indexes.

Well, kind of a group project. However, if you could check here (accessforums.net/showthread.php?t=63127)
What do you think of ajax-s solution, could I implement that table and ignore inventory details? For example, Its one of many stores which my company owns, I have 2 footballs left to sell. Now before I go out of them I send a paper to the warehouse (big one) which my company owns, and I have nothing to do with the warehouse management or anything. I just send the paper that I need 10 more footballs and then they deliver them and I enter the footballs in the database (tblStockMovements) as quantity 10 and movement type - stock in?

plog said:

3. You probably shouldn't have seperate tables for invoices and quotes. Instead a quote should turn into an invoice by marking a field--perhaps by using that status field you mentioned.

Click to expand...

This is how I imagined it. To make a table with a field "type" where its either invoice or quote (a lookup menu, and make another small table so I have these 2 options available) and a field "status". But Im curious is it possible when I select type to be invoice, can I get in the status field only statuses related to invoices to ignore quote statuses, also a drop down menu. Also, if the type is quote and the status changes into invoiced, is it possible to change the type into invoice then? Does this sound any good at all