Stock/Inventory Control - Help :confused:

Hi guys,

Before you say anything, yes i have spent endless hours searching the forums for what im trying to find. The simple fact being that i can't find anything that fits my situation or i simply can't understand it.

Heres the problem -

I've been given the task, by a family member, to design and build a database. The database is for his carpet shop/warehouse. Being of such a business the warehouse always has stock coming in and going out. The manager can't keep track quikcly enough.

He therfore needs -

- A database of products in stock
- A history of old stock and who it was sold to (which customer/invoice)
- An easy, user freindly, way of entering stock as quickly as possible as it comes in
- A form for orders, which, in the top half the customer information is entered and in the bottom half a products/stock search by the stock ID/number to find the products they are buying. Also a subtotal/total box.
- The stock needs to be deducted from the stock/products list when it is bought and goes into a history database/table.
- Reports based on Orders, with only relevant information, for customer invoices.
- A stock search facility where all stock, present and past, can be searched to see what is most popular according to manafacturer, colour, quality etc.

It is not a shop/EPOS application that i am trying to create. Its simply a stock control system which, as a bi-product, produces invoices and reports etc. It goes without saying that, as staff members don;t have extensive computer knowledge, the whole database must very user freindly and simple and clear etc.

Now MY problem -

I can produce simple databases and can set up straight forward queries/reports etc. But the whole stock update thing really confuses me.

- i dont know what the set-up/design of the database should be
- i dont know how to do the stock update thingy
- i dont know how to create a search facility for products being purchased
- i dont know how to move the stock from the stock table into a history table with the customer/invoice information
- i dont know, qithout creating 10 different queries, how to make a stock search facility which the user can determine the criteria for searching
- i dont know how to make the whole thing look simple and user friendly!

What im looing for guys is for anyone to point me in the right direction. i Might sound lazy just asking for people to help a bit but i have spent weeks trying different things out, using examples, reading books, EVERYTHING. It all seems complicated until someone explains it to me. I dont mind doing it all myself but i dont know where or how to start. Can anyone suggest a suitable design/set-up? Can anyone provide a sample/example i could look at that is very similar? Can anyone help in any ways what so ever. I thought i was fairly good with computers but this proves that when it comes to advanced access im just a noob. if anyone can help a guy in need what so ever please do.

you all sounded really helpfull in other posts and i hope you can do the same for me. please remeber im a bit of a novice so be gentle! Thanks in advance for any help that you can provide me with.

All the best, James

Things you will want to do first:

1) Read up on Normalization, Database and Relations theory.

2) Grab some samples of inventory samples (some are here in fact; go over to sample database forums)

3) Play with it, and even break it so you know what make it tick and what doesnt.

4) Start with something small. (e.g. Make a database to simply store a stock. then make another to look up a stock and so on)This is to help you from making the mistake of going all tilt without understanding what you are doing and literally dig yourself a hole to China.

5) When you are stuck, feel free to ask away. Remember, it helps a lot if you have very specific question, has described the steps you have taken to remedy and goal you are trying to accomplish in this situation. You can never be too verbose; it's always better than "I did so and so and it doesn't work. HELP ME!"

I second Banana's advice and offer a link that will suggest the complexity of the problem.
Inventory Control: Quantity on Hand
Good luck with the project.

thanks for your replys, but i dont know if you guys saw the bit about me not know any code or anything what so ever. lol. I don't understand all this normalisation stuff. You really need to drop to my level. I have no idea where to start all i know is what the carpet shop is looking for. I'm sure that the best way of me learning it is to look at examples but none of the examples out there are similar to the carpet shop situaion.

I have no idea where to start. Can someone help me. I dont have a clue about all this stock on hand business. i don;t know what my database design or relationships should be.i need someone who can quickly make some simple examples that would help me if i give them any info they need.

Thanks again, James

Maybe you can find something on this site that can get you started.
http://www.databaseanswers.org/data_models/index.htm

Isn't your question a bit like 'I want to win the Indy 500 but I don't know how to drive. Please help me.'?