Stock Control via Access?

[Mr. Twig]

Hello, I work for a small company and currently run a stock control database via OpenOffice's version of Excel. I have just set up a new PC and am buying Microsoft Office for it, but am unsure whether to get the full version with Access or not.



We stock around 300 items which can all be used together to make a bespoke product. Alternatively each individual part is also sold individually. For simplicity I will pretend we sell Lego to use as an example.

Of those 300 items, lets say 4 particular items make a Lego car, another 4 make a house etc. although they are not necessarily the same 4 items for each car or house. Maybe one car is red and another is blue, or one house is two stories whilst another is three etc.

Lets say a customer orders a car, the four parts could be:

Yellow body
Big wheels
Tinted windows
Pink interior

As you can see each component that makes up the car is customisable; different colours/sizes etc.

On my current system I cannot input 'Car' as each car can be made to different specifications, so I have to make an entry in the outgoing row for each part individually. Whilst this works perfectly well to tell me how many of each part are being sold each month and how many are on the shelf at any one time, that is all it does and there is potential for so much more useful data to be collected such as how many are complete cars or individual pieces, which customers are buying what and how often etc. It's also a long process which leaves plenty of room for error by accidentally altering the wrong column throwing the whole thing off, which does happen from time to time (and takes a long time sifting through invoices/delivery notes to correct).

Can Access be used as an input form to link to the Excel document? So when I put Customer A buys a car in Access, the corresponding numbers will alter in the Excel spreadsheet?

Can you make a customisable product in Access? By which I mean I can enter that Customer B has bought a car, and it will require me to select the exact individual components that it's made up from?

Can you set different item prices for different customers? Because we have different levels of discount for B2B customers, and the retail price for end customers, so it depends who's buying as to how much we charge.

Is Access capable of doing the stock numbers all on it's own?

If not, and I know this is the wrong forum for this question, but does anyone know if Excel is capable of having an input form which can do similar, so that I can at least keep track of how many complete units are being sold?

Thanks in advance for any replies, I really just need to know if Access is suitable for my needs and can perform the tasks I need it to before buying. The how is not important at the minute, I've spent around an hour on google looking into it, I've seen many good uses for it and some similar examples for stock control, I can't really find anything that answers my queries directly.

[orange]

Before buying Office or any software, you should do some research. First you should have a good knowledge of your business and any anticipated future business. You should be able to describe your business and business rules in plain English (no jargon). This description can start at extreme overview - the 30,000ft view - and then get progressively more detailed. The description should be clear and concise and recognizable and understandable by any and everyone in your business. That is it should be clear to all - not just an IT view or a Finance view.

From your business rules and description you should be able to build a data model of the "things" that are important to your business. Once you have the description and a data model, and solid list of your requirements, you can then compare "OTS" (off the shelf packages) and/or develop and build yourself or via contracted personnel. You do a cost/benefit analysis to some level to see what option(s) are most feasible/practical for your business. You look for database cost and maintenance, user interfaces, reporting, querying ... and be realistic in rating the various alternatives.

If you do decide, after analysis, to build your own system, you have to get training in database concepts, before any specific software (Access, MySql, SQL Server...).

Your questions are indicative of someone unsure of the steps involved in planning, designing, developing, testing and maintaining a database application. Beware of the marketers who want to sell you software --you need to make several decisions. Some of these decisions will be critical to your business, so don't approach this in a knee-jerk way and buy based on database software initial cost. You really have to do some research and most of it will be related to your business, its processes and business needs and priorities.

There are several inventory management, stock control are associated products on the market. There are lots of database management systems for sale. But you need to understand WHAT you need, then compare your needs with what is available. There are several posts in the forums; several videos(free) on youtube. You can also get trial versions of software...but you have to know what you need.

A friend of mine used to say--sure we can build anything it's just SMOP. Keep this in mind when you think of landing a person on the moon.(SMOP)

*SMOP -simple matter of programming.

Try searching google
https://www.google.com/search?q=inve...utf-8&oe=utf-8

this one for using Access for inventory management
https://www.google.com/search?q=inve...ement+msaccess

Here's a free generic data model for Customers and Inventory
http://databaseanswers.org/data_mode..._mgt/index.htm

There are several free videos re database concepts at the beginning of this link
https://www.accessforums.net/access/...tml#post221691

Getting started with database design - overview) available here
http://www.databaseanswers.org/approach2db_design.htm

Good luck. Let us know how you proceed.

[Mr. Twig]

Before buying Office or any software, you should do some research. First you should have a good knowledge of your business and any anticipated future business. You should be able to describe your business and business rules in plain English (no jargon). This description can start at extreme overview - the 30,000ft view - and then get progressively more detailed. The description should be clear and concise and recognizable and understandable by any and everyone in your business. That is it should be clear to all - not just an IT view or a Finance view.

From your business rules and description you should be able to build a data model of the "things" that are important to your business. Once you have the description and a data model, and solid list of your requirements, you can then compare "OTS" (off the shelf packages) and/or develop and build yourself or via contracted personnel. You do a cost/benefit analysis to some level to see what option(s) are most feasible/practical for your business. You look for database cost and maintenance, user interfaces, reporting, querying ... and be realistic in rating the various alternatives.

If you do decide, after analysis, to build your own system, you have to get training in database concepts, before any specific software (Access, MySql, SQL Server...).

Your questions are indicative of someone unsure of the steps involved in planning, designing, developing, testing and maintaining a database application. Beware of the marketers who want to sell you software --you need to make several decisions. Some of these decisions will be critical to your business, so don't approach this in a knee-jerk way and buy based on database software initial cost. You really have to do some research and most of it will be related to your business, its processes and business needs and priorities.

There are several inventory management, stock control are associated products on the market. There are lots of database management systems for sale. But you need to understand WHAT you need, then compare your needs with what is available. There are several posts in the forums; several videos(free) on youtube. You can also get trial versions of software...but you have to know what you need.

A friend of mine used to say--sure we can build anything it's just SMOP. Keep this in mind when you think of landing a person on the moon.(SMOP)

*SMOP -simple matter of programming.

Try searching google
https://www.google.com/search?q=inve...utf-8&oe=utf-8

this one for using Access for inventory management
https://www.google.com/search?q=inve...ement+msaccess

Here's a free generic data model for Customers and Inventory
http://databaseanswers.org/data_mode..._mgt/index.htm

There are several free videos re database concepts at the beginning of this link
https://www.accessforums.net/access/...tml#post221691

Getting started with database design - overview) available here
http://www.databaseanswers.org/approach2db_design.htm

Good luck. Let us know how you proceed.

I have never used a database application such as Access, nor has anyone here. I have a head for complex computer programmes but my usage of business related stuff is fairly minimal, the other staff aren't really techy and simplistic usage is what we need. By that I mean easy to put orders in/out through. Excel is absolutely perfect in that sense, but it's very limited in it's ability to manage stock.

I've tried to put some research in but I came up empty handed in the things that really matter. I can see from the many examples, tutorials and questions I have come across looking for answers that Access is a brilliant product for managing stock of off the shelf items. Our products are complex and every component of a complete unit is variable, and I can come across no definitive answer as to whether Access can allow for this in a more detailed way than just listing each individual component and altering stock values of each one seperately like I already do with Excel.

There are also other uses we can put Access to, I can also find uses for Publisher, but not enough to justify the extra cost cost of professional.

We have looked into stock control products previously, but the ones we found that would suit our needs were too expensive to justify the outlay, we're only a small company.

The main question I really want answering which I couldn't find an answer for was the one about customisable items. That would be the key as to whether Access would be a genuine improvement for us over Excel - we already have a satisfactory sales database, but it would be nice to have the sales and stock systems linked together. The rest were simply secondary thoughts I figured I may as well ask whilst I was on here.

[darshit_goswami]

Access can do it.. correct.

But question is still the same. If you want fully customized product to suit your business need you will need to hire a developer. Basic stock and management is not that hard to accomplish with little bit of learning with Access. Search access tutorials and start practicing or building.

If you want it to be done free ( if you dont want to buy MS office licence) then you can always opt for open office or libre office. they too offer database solution. That should save some money for your business.

Regards

[orange]

Database and spreadsheets are different tools. Access and Excel are built on very different object models.
You should watch the videos in the links I gave you in the previous post. You will quickly see what database is about.
These videos are well worth watching regardless of whether you buy, build or move to another company. They are about basic database concepts and techniques.

Your question still has an air of "I don't know what a database software is" in my view, and I think you're going to have to investigate/research that point until you're comfortable. You are in spreadsheet mode, and that will not do you much if any benefit with relational database.

My background is in integrated, corporate database and data dictionary/modelling. And I can assure you relational database and spreadsheet are different tools for different purposes.

it would be nice to have the sales and stock systems linked together.

this is something that happens by analysis of your business, processes, rules and requirements, a thorough understanding of your personnel and a focused commitment/approach. It is an approach/philosophy of the business often proposed by management after a review of processes, costs etc. None of this happens by buying software or hardware. There is some business rationale, then a plan for implementation.