Luddite Looking for a Leg Up (2 Viewers)

Reserved Sanity

New member
Local time
Today, 12:52
Joined
May 11, 2020
Messages
2
Hi all,

I don't belong here, so I come hat in hand to the gurus.

I am the 2IC of a navy cadet unit, and I'm trying to find a stock management tool for our uniform store. I thought an inventory template for Access would work, but everything I've found is far to complex for my needs. I need to record the specific uniform items a cadet is issued, including the item name (e.g. black trousers) and size (e.g. 95R). These items may be exchanged for bigger sizes so (we are dealing with growing teenagers) so I want to be able to track the items currently issued to an individual. I am not really concerned about tracking the stock levels of the store itself and don't really need to record this at all.

I've spent the last 3 days trying to school myself in MS Access 2019 (I have a MS 365 subscription) in the hopes of building a system. I had initially hoped to find a template that would suit my needs, but haven't found one/have insufficient literacy to adapt one... I very much appreciate any help you might be able to offer.

This should be simple, I know, but I'm struggling to achieve this task on my own!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:52
Joined
Jul 9, 2003
Messages
16,245
As you have discovered, a template or a better description I think is, an "off-the-shelf package" never really meets all of your needs. However I'm sure it must go some way to providing the functionality you require. So you have several choices:-

Continue to try and find an off-the-shelf package that meets all your needs - (very unlikely)
Use the inventory template for Access you have found, identify the features you would like to retain, and use that as the basis for your ideal stock management.

Something else which you may have not considered, start out with pieces of paper! Jumping straight into software like Access or Excel, without thinking about the process you are following is a mistake that most people make, they expect the software to sort out all of the problems for them, however this approach generally makes even more problems!

Develop your process on paper, used forms, checklists, and the like. Paper forms are easy to handle and modify and everyone understands them. Once you have a system running effectively in this manner then it is time to think about the software requirements you need.
 

Reserved Sanity

New member
Local time
Today, 12:52
Joined
May 11, 2020
Messages
2
Hi Gizmo,

Appreciate your swift response.

We have been operating using paper for over 10 years which has been working 'okay'. The process currently involves a ring binder with a divider between male and female cadets. Each cadet has a plastic sleeve housing their store card, a list of uniform items with their sizes marked against them.

This works in a basic sense, but doesn't easily allow for uniform exchanges (that's a new sheet) and isn't easily reportable. Hence the desire for digitisation.
 

June7

AWF VIP
Local time
Today, 04:52
Joined
Mar 9, 2014
Messages
5,425
How have you tried to school yourself? Have you studied tutorials before jumping in? Spend a solid week with an introductory book to learn basics of relational database principles, Access functionality, programming concepts, VBA language and/or macros.

If you really don't care about inventory balances nor retaining history of what cadets used to have, this should not be so difficult. A normalized structure could be like:

tblCadets
CadetID
LastName
FirstName
Rank

tblUniformItems
ItemID
ItemDescription

tblCadetUniform
Cadet_FK
UniformItem_FK
ItemSize

When cadet changes to another size of black trousers, just change value in ItemSize field.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:52
Joined
Feb 19, 2002
Messages
42,981
We don't know what a "2IC of a navy cadet unit" is so none of us is going to be able to picture your job or duties. I find myself with some time to help provided you are a school or non-profit. To get us started, scan all the different documents you use to pdf, blanks if you have any and also filled with data. I'm assuming this won't contain sensitive data. If they do, then black out last names and IDs. Upload the images. We'll use this as the start of an application. We also need to know if used items go back into inventory.

Personally, I've never seen a MS created template that I actually thought was useful. Some experts maintain their own websites and by diligent searching, you might run into something you could adapt but without much Access or general business knowledge, it would be hard for you to envision that a lending library template might actually be a good model to base this project on. The lending library has people, it has books (clothing) and people borrow books (clothing) and return them. The lending library is actually simpler than the typical inventory template and is probably much closer to what you are looking for once you can conceptually substitute clothing for books.

Gizmo makes some great points. You need to learn how to balance the bike before you can even ride it let alone win a race so start with paper. Try to identify entities - top level things such as people and inventory items. Then you need to identify processes - taking something out of inventory, putting something into inventory. And you also needs to identify reporting requirements. For example, you need to review inventory balances so you can determine what needs to be reordered. Creating statistical reports that show how much of which items are used within a given time frame will help determine what your lead time needs to be. If you issue 5 shirts a week but it takes 3 weeks to replenish inventory, you need to include that info in your reorder algorithm.

If you are interested, please send me a PM and we can discuss what help I can provide and how we'll use the time. I have GoToMeeting so we'll do this with screen sharing software. Tell me who this project is for in the PM and include your email.

It'll be fun:)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:52
Joined
Feb 28, 2001
Messages
27,001
Pat, 2IC is "2nd in command" which is a common Navy abbreviation meaning the same as "assistant to the boss" or "assistant unit leader." The one you want to watch out for is the HMFIC because if the team calls their leader by that acronym, that person usually has a serious case of attitude.

@Reserved Sanity : June7's approach might be all that you need. Basically, a person list, an issuables list, and that list at the bottom that says "an X was issued to person Y" with perhaps a date just so you can say later, "Cadet, we gave you a new pair of socks two weeks ago. Did you have an issue with not enough fiber in your diet?" (Just exaggerating a little.) This will work because you are not tracking items and also because, other than size differences, uniform parts not related to rank insignia are interchangeable, so have no serial numbers or other individual item ID.

June's list called tblCadetUniform is what we call a JUNCTION TABLE - which is a topic you search in this forum or online to find reading material. You use it for what we call a "many to many" relationship. I.e. One cadet can have several different uniform elements, and one particular size of shirt could be issued to several different cadets with similar body sizes.
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:52
Joined
Jan 23, 2006
Messages
15,364
I agree with the comments from the other responders, Especially with a description and a model on paper that you can test with some sample tables and sample scenarios. Bottom line is design and trial it before you're committed to physical database. Much easier to change things on paper than a physical database.
I'll suggest a video on a stock control/management system by Software Matters. I'm not suggesting that the database is what you need, but the video will show you some of the thought processes and techniques involved in designing and building a database application.

Good luck with your project.
 

Users who are viewing this thread

Top Bottom