I need some help before I start

[cheeseslice]

Hello all,

This is my first post here and I need a bit of help.

I've been given the task of creating a stock control/inventory system for one of the staff canteens and really I'm looking for a place to start, and by that I mean is access the software that I need to use?

I'll describe roughly what the requirements are, I'm sure I'll miss some out:

All products to be stored with prices etc
Suppliers info stored and linked with products
Stock inventory of all products

Stock is manually counted every Friday and a report is submitted to finance dept. (so stock control needs to be automatic but allow manual updating)

History of product prices. Prices fluctuate a lot between suppliers. Need to be able to produce charts of comparisons between suppliers over the year.

Fridge temperatures etc to be logged every hour
Meat temperatures etc. to be logged so many times a day

Cost of recipes, portions by linking ingredients with products and prices, this would then tie in to weekly menu and allow an estimate of cost per meal, profit etc.

Reports for all of these and probably a lot more.


Basically what I have been instructed to build is a standalone application that will run on a laptop which has office 2003 installed, which is why I am naturally inclined to go for access.

They want the database to operate as/like an application with a windows gui feel.

Should I be thinking of making this in access alone? Should I be going for an access database with a visual front end or should I be forgetting about access altogether? Someone has suggested that I look into visual foxpro, I have never used this before so it would require a bit of learning.

Is the access switchboard easy enough to customise with tabs, menus etc. or is that really me moving to vb.net

I hope this question sort of makes sense, I think I have just written down everything bouncing about in my head.

 

[HiTechCoach]

Hello all,

This is my first post here and I need a bit of help.

I've been given the task of creating a stock control/inventory system for one of the staff canteens and really I'm looking for a place to start, and by that I mean is access the software that I need to use?

I'll describe roughly what the requirements are, I'm sure I'll miss some out:

All products to be stored with prices etc
Suppliers info stored and linked with products
Stock inventory of all products

Stock is manually counted every Friday and a report is submitted to finance dept. (so stock control needs to be automatic but allow manual updating)

History of product prices. Prices fluctuate a lot between suppliers. Need to be able to produce charts of comparisons between suppliers over the year.

Fridge temperatures etc to be logged every hour
Meat temperatures etc. to be logged so many times a day

Cost of recipes, portions by linking ingredients with products and prices, this would then tie in to weekly menu and allow an estimate of cost per meal, profit etc.

Reports for all of these and probably a lot more.


Basically what I have been instructed to build is a standalone application that will run on a laptop which has office 2003 installed, which is why I am naturally inclined to go for access.

They want the database to operate as/like an application with a windows gui feel.

Should I be thinking of making this in access alone? Should I be going for an access database with a visual front end or should I be forgetting about access altogether? Someone has suggested that I look into visual foxpro, I have never used this before so it would require a bit of learning.

Is the access switchboard easy enough to customise with tabs, menus etc. or is that really me moving to vb.net

I hope this question sort of makes sense, I think I have just written down everything bouncing about in my head.

Access is probably the fastest at Rapid Application Development (RAD) compared to VB.net. FoxPro is a lot closer to Access for RAD. That would be if your knowledge of how to develop in each were equal.

The big question is what database development platform are you proficient using? How skilled are you at database design? How must do you know about inventory control?

My guess is that you are new to database development. If this is true Access will probably be the easiest to learn rapidly.

I have specialized in creating this type of application for years. What you have describe can easily be a challenge for a very experience developer using any software development platform.

Basically what I have been instructed to build is a standalone application that will run on a laptop which has office 2003 installed, which is why I am naturally inclined to go for access.

What is the time frame for completion?
It sure sounds like you are new to database design. Based on that , you should it would be reasonable to expect this to to take you a year or longer to get this completed unless you can devote full time to this project.

I would urge you to look at the software that is currently available to handle what you need. Custom software can easily cost more to develop that buying it.

 

[cheeseslice]

Thanks for replying.

For the past number of years any applications/solutions that I need to provide in work have been php/mysql based simply because they are easiest to implement into the company intranet.

I haven't used access in quite a while but I think I could pick everything back up again quite quickly, I am constantly using vba in excel so I would imagine that it would just be a case of getting some a reference book for vba in access to get me going there.

As far as stock control/inventory systems go, I don't have a clue. I spent the whole of last night reading up on restaurant stock systems and you are right, it is quite complicated.

I would have between 4-7 weeks to work at this project full time, of course some of this time would be spent(every couple of hours!) relearning some of the skills that I would need.

At the minute I am trying to work out the tables that I am going to need,, if anyone knows of sample database designs that I could have a look at I would appreciate it.

As far as the look and feel goes, will the built in switchboard suffice?

thanks again

 

[gemma-the-husky]

seriously

unless you are pretty expert - I would be inclined to avoid developing a system yourself - and I even then I would think twice.




If you do still want to do it yourself - you really need to get an agreed spec before you start - even though its within company - as I think otherwise you might find something like this is a bit like topsy - it will grow and grow, and you will be asked for more and more things that you didnt expect to provide.

 

[cheeseslice]

seriously

unless you are pretty expert - I would be inclined to avoid developing a system yourself - and I even then I would think twice.




If you do still want to do it yourself - you really need to get an agreed spec before you start - even though its within company - as I think otherwise you might find something like this is a bit like topsy - it will grow and grow, and you will be asked for more and more things that you didnt expect to provide.

I understand that this is a big job but I have been asked to provide a solution to this problem so that's what I have to do. Basically I just need to work out the best tools to achieve this is the quickest time and that will give me the least amount of work.

I should be able to get something up and running after 7 weeks. Even if I only manage to get the basics working and add updates on at a later date. I suppose that's another question I need answer, does access lend itself to having patches/updates easily applied ?

thanks for the reply

 

[gemma-the-husky]

I started this with a link to a dbs, and some appropriate thoughts -

but as I then thought about it a bit more, I added some more general thoughts - so I decided to add this intial note, which I hope you find helpful.

So - if your firm is looking for inventory lite, then it may be doable - but if they are looking for a full-on system (and the comments about recording temperatures maybe point that way) then it becomes much harder.

And if you are learning about both access, and inventory this on the go, I would say you have very little chance of achieving a good result. (How did you get charged with this task? ARe you IT? or an accountant?)

If you are looking at providing a full-on system then I would think you need to be one of these two things - either an experienced programmer with a good working specification - or an accountant with a real understanding of what the firm wants - and even then 7 weeks will be pushing it.

----------------------

so - you could try this link to a MS template

http://office.microsoft.com/en-us/templates/inventory-management-database-TC001018458.aspx

it certainly isnt bad - I have used a modfiied version of this, for some serious stuff - however, it doesnt track prices/stock valuation - and therein lies a real can of worms - ie what accounting basis do you use to value your stock .... and going on from there, what costs for each item do you want to include in your recipe costing. this isnt easy - every time a raw material price changes, and you buy stuff in, then it affects your stock costing - so it all depends what costing method you use.

you need your accounts dept to tell you exactly what they want - but the stuff with the prices is real seriously hard.

The trouble is, stuff like this is virtually the same whether you are a tiny company, or a major plc. If you can cut corners, and go for the simplified system, then it may be more do-able.

But a full production inventory system with back order management, standard costing, automatic re-ordering, price control etc etc, can/WILL cost many thousands (sterling or dollars), and thats with a system that is likely being sold to many users - thereby sharing the R&D costs.

access is certainly as easy as any other environment - and unless you know something better its as good as anything

------------------
but 7 weeks from scratch is not a lot at all - even if you have no other stuff to do

 

[cheeseslice]

Hey,

I work in IT and my experience would lie in applications development, I haven't had to use access though in quite a few years.

I have no idea about how their stock taking works, so I've been trying to give myself a crash course from google, just to understand the basics, I'll learn more when I get back to work tomorrow morning but I'll try and explain what I think the bare minimum will be.

From what I understand they will be happy enough if in 4-7 weeks I have a database up and running that will allow basic stock control, the other facilities I mentioned don't necessarily have to be all tied in by that stage. There will not be any automatic reordering, this system will provide a report at the end of each week that will allow the manager to see current stock levels, cost required to replenish to minimum stock levels etc.

They do have data on paper that goes back many years of stock requirements for each week so I suppose this will have to be incorporated at a later date so that they can see the trends. Apparently there are huge differences each month depending on what is going on.

How would I go about implementing price history for each product? Food seems to change price quite a lot depending on quantity etc so prices are going to change for each weekly report. Off the top of my head I'm guessing I would have tables for:

suppliers
products -lamb, beef, milk etc.
productsCategory - meat, dairy etc.
productsSuppliers - they don't always buy from the same supplier

productsStockCard - lead time, min stock level, description, shelf life

productsCost?

should I have a productsCost table, have productsID, the cost, the date of the week.

that way I suppose when running the report I could have the query check the price that is closest to the date of the week of the report being printed? It would also mean that I could easily present a graph showing weekly price changes over the year.

To be honest I think my biggest problem is going to be making this database efficient enough to deal with things as the data grows.

I'm trying to break the tables up as much as possible to minimize the amount of records being searched but I don't know I'm going too far or not far enough.

Would there be anything wrong with having all the information from the productsStockCard stored in the products table or am I right to split that information up?

 

[G37Sam]

Tell you what, I'll develop the database for you and email it to you.. then pass you my paypal address for a nice check lol just kidding...

I'm a Mechanical Engineer and writing =sum(A1:A6) on excel was as close to programming as I got in my academics. Access is not very hard to learn and you can get used to it easily. My first "Access" job came up completely random, I didn't know much about Access but they pay was good back in college so I took it and started learning from there. A few db's later and I found myself quite handy. Bottom line is, if you want to do it, you will, just be patient and keep a few beers handy all the time lol

If you decide to use MS Access (which from reading your post looks like the smart decision), I'm sure we'll be more than happy to help you out.

 

[PNGBill]

Just some thoughts.

Your first post mentions "one of the staff canteens" Are there a number of canteens ? and will they all be using the system?

One advantage you may have is that most Canteens have virtually no stock at the end of the week and if so, this could mean Average Costing is not such an issue due to the high stock turn and Last Cost may well give a workable solution to start with.

The people specifying the job, do they really know what is involved or just want what they dream about?

Excel may be a temporary solution to your Recipe issue. It is quite easy to export data from access to excel and vice versa. You could export Weekly, Bi Weekly or even Monthly the Costs from Access and Excel will provide the price back to access for the cost of a "muffin".

Due to the markup and wastage how critical is it that the muffin price changes on Wednesday instead of waiting for the weekend update??

The above could mean you use a quickly convertible template and while you work on the extras, the users can rethink their spec so essential work only is done and you don't end up spending a month on something that isn't used.

In a previous "life" we just assumed our canteen type businesses had "minimum stock" at the end of each month. Before pc's were common but the point being in this type of business you will find it very hard to get Exact Qtys and a shrinkage is to be accepted. How to you account for the half bag of flour, half packet of butter etc.

 

[dkinley]

but 7 weeks from scratch is not a lot at all - even if you have no other stuff to do

For the system in question, the stakeholders involved and the learning curve - I'd guess you might get through two decompositions of the requirements (if you had them all) in 7 weeks. And this isn't even writing code yet.

That is, if you could conduct the interviews and then get an executive committee to meet and agree on ideas so you can blame them for the decision-making when the rest of stakeholders get upset at delivery.

-dK

 

[cheeseslice]

The people specifying the job, do they really know what is involved or just want what they dream about?

I have to answer this first! Went to first meeting last week, was shown everything currently being done on paper etc.

I turned to my manager and said I only have 7 weeks free to do this and the catering manager showing me the paper work said "I don't mind waiting", does that answer your question!! hahahah

There are 3 canteens as such but really it is one canteen manager and all food is stored in a central place(the main canteen), the other canteens would sell snack food or food that is easily prepared like paninis or sandwichs. I can see that if the system works for them they may well want three seperate systems all coming together but at the minute it is just the one.

They do a weekly update of data so the 'muffin' price would only be updated on the Friday when the new orders for the suppliers are being put together. As far as I know the plan is for them to order on a week by week basis, so once they get this sorted then the recipe cost should be fine as the price they pay on the Friday before will apply for all recipes that week.

How do I include wastage? I never thought about that. I am going to have to have a way of measuring theft/wastage.

Am I along the right tracks with having the products cost in it's own table?

Thanks for all the replies, they are most helpful. I'm starting to understand exactly what I have gotten involved in.

One last thing, It took me 3 bottles of cider to get through my reading of restaurant stock control last night

 

[PNGBill]

Just have a good look at the request for historical prices as this may not really be important.
Who needs to know what an item cost 12 months ago?? You want to know your Sales and profit and maybe cost of goods sold but the actual purchase prices of an item??
What good is this? Just .pdf your price list each month and email these if anyone has nothing else to do.

Computers should make the things we do now easier and quicker and maybe achieve more but some people just go wild with Wish Lists that can easily side track the issue.

There is facilities for temperature readings available. Freezer Trucks and containers use these and they don't have to be linked to Inventory control databases. You could get some Petty Cash and buy a Highest / Lowest Thermometer for each fridge so they can see the coldest it has been and the hottest it has been on any day. They would have get off their bums and look though

Take away the fridge temperature, Price history archives, recipe costings and you could just about copy "Northwind" and be up and running in a very short time - Point of Sale excluded.