Inventory Control DB

[nachesebro]

Hello All,


A quick background on what I am looking at doing: I work aboard a 700 foot cargo ship, thus the need to take accurate inventory of our consumables (cleaning supplies, paper products, etc) is paramount. Having designed a simple spreadsheet to take down to the dunnage room and tally everything and take it up to the pilot house for the mate to review to determine what needs to be ordered, a couple other metrics were brought to mind. We are looking at ways to figure out two metrics: Rate of Consumption, and Time to Out. Rate of consumption is fairly straight forward, it is simply the number of cases (or fraction of a case) we go through in a week (we only deal with it once per week, so we don't need anything much more specific than that). Time to Out would be the measurement of how many weeks we can make it before the product runs out. We were thinking about putting this into a spreadsheet, but I think it wouldn't be much more effort to make a database to perform this task, I think it would be a much more appropriate and time saving route to go. The other obstacle in the whole realm of things is the end users may or may not be all that tech savy, some have difficulty operating Outlook if an email has an attachment they need to open.

Having said all that, on my end, I'm not exactly sure how to go about setting up the database to display those two figures, but I don't want to get too involved with figuring that out if it cant be performed in a way that creates additional steps the end user would have to perform, otherwise I think the time I spend on my own working on this will be for nothing more for a nifty little file that will sit on the pilot house computer for years in some folder somewhere not being used.

If someone out there has dealt with this sort of thing, and can vouch for it not being too much of a headache, then I am down for an interesting database project and learning some things I haven't done before, or on the contrary, if it is a bit cumbersome to perform in Access, by all means let me know so I can cut my losses while I am still ahead!

Thank you so much.



Nate

[CJ_London]

what you are looking for is basic maths and a common requirement in many industries - all you need to do is ensure you have the (correct) data required to do the calculations.

Excel is certainly capable for this task as is a database. Which is easier is down to your skills or your willingness to accept a challenge - plus I'm guessing it needs to be robust for when you move on to another ship so others can maintain it. Behind the scenes (i.e. what you will need to do) databases are more complicated but from the inexperienced users perspective easier to use and if designed properly can last a long time. Excel on the other hand can be harder for the inexperienced user and more prone to failure (who overtyped a formula with a value and didn't notice for 6 months?)

Have a look at some of the access templates around inventory control - access has a number for you to look at and also google 'access templates' to find more. Understand what normalisation means (databases are/should be normalised, excel isn't).

Also think about how the data is going to be completed - are you wandering round the dunnage room with laptop in hand? or completing paper that needs to be entered later, or using as barcode reader?

Quite a bit to think about, but if you decide the access route you will find plenty of help on this and other forums

[orange]

Here is a free, generic data model for Ships Stores from Barry Williams' site It may be more than you want/need, but it should give some idea of the type of info that "could" be required.
It is generic, and may not apply to all of your needs. You can adapt as necessary if it is relevant.

Here are some of the requirements associated with the model.

Good luck.

[nachesebro]

Excellent information. That was one of the underlying concerns with Excel was the notorious fat finger, one wrong move and the whole thing ends up not working and ends up collecting dust and were back to scribbles on torn pieces of paper flying around for inventory...

The template that was built in was good, but rather excessive for what we need. Nonetheless there were some good things about it that I want to emulate in our version.

For now I am going the Access route, I have some refreshing to do getting back into creating the table structure that I want and that link to the example database gave me some good pointers.

Once I get further along I'm sure I will be back when its time to do the math for Time-To-Out. On paper I have an idea of how to get the math done. I think keeping a running average of how long it takes to use up one case of stuff and using that value to check against our on hands will give the value I want. Its just getting it all put together that will be a bit tricky, but that's another day and another thread.

Thanks for the pointers guys, appreciate your help.