Stock/Inventory Control - Help.

[james_IT]

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

 

[ByteMyzer]

You might try:
Microsoft Office Templates: Inventory management database

...for starters.

 

[RuralGuy]

To add to ByteMyzer's suggestion, here's a couple more links to get you started.
Library of Free Data Models
Inventory Control: Quantity on Hand

Unless you don't trust your instincts, you should have realized by now that your given task is *not* a trivial one. Good luck with the project.

 

[RV]

James,

please do not double post:

http://www.access-programmers.co.uk/forums/showthread.php?t=110586

You've been supplied by a couple of links that should get you started

RV

 

[RuralGuy]

And http://www.access-programmers.co.uk/forums/showthread.php?t=110587

 

[neileg]

Don't underestimate the complexity of this type of application. If you are a bit confused then hopefully you recognise how hard this is going to be!

Seriously consider
1) Using and existing template
2) Buying a shrinkwrapped application

 

[RuralGuy]

Good luck on your project.

 

[Banana]

secondly, no offence but the advice you all gave is rubbish. isnt it obvious to check other forums and tutorials. Ive looked at literally loads of templates and examples. None of them are easy to change or understand if your are a begginer. Thanks for the intention of helping but none of it has helped. If i could afford to buy a custom written software i would.

Well, if you cannot grasp those concepts, then I would stronlgy encourage you to go and tell your family member to get a professional to do it. Only other option is to go and take a class on database. Both will cost money.

Also, if you were looking for a "drop-in" or "plug and play" template, there is no such thing. Every databases is a bit different, though they may share the fundamental design, and they have their own business rules. It is almost given that you will have to adapt a database to your need. Again, if you cannot get started, see the above options.