Personnel "kits" in microsoft access or excel

Question

Hello, I have created an inventory of items that get issued to people within our department. I was wondering whether and how it would be possible to query an excel or microsoft access inventory (I have both applications, which would be better for this?) to see how many more people could be fully kitted out. For a simple example, say I want to issue everyone 2 tools, 1 backpack pump, and 9 batteries, and in my inventory I have 25 tools, 9 backpack pumps, and 58 batteries, and I want to know how many people I can fully issue gear to. Obviously, this problem is much more scaled up for us so while the example could be done by hand, having a human try to solve that problem at a larger scale would be prone to error. Additionally, is there a way to query what the shortfall would be (and with which items) if I wanted to add a certain number of people. For example, I would want to query if 7 people were to be hired with the same inventory and the same kit requirements, how much more of each item would I have to procure?

Thanks!

Answer 1

Hi @Katie Scott

I have developed a solution for you which can be downloaded at https://1drv.ms/x/s!AhGuERp4qV0Rhf1nio5YcbgWagpwfQ?e=FTzsBN - You can use the template or learn from it to build your Inventory system. (This is only an example and hopefully it helps lead you where you want to go).

To download the file go to the link > File > Download a Copy

1) On the Inventory tab you can edit things colored in Orange (if you want to remove the lock, unlock the worksheet with the password: password)

You can change the per person requirement as stated above.

You can change the Wanted Kits as your requirement stated above.

2) Go to In tab

You can insert a row with a date and what you purchased. This will update the Green fields on the Inventory tab using the Totals row.

3) Go to Out tab

You can insert a row with a date and what you gave to people for inventory. This will also update the Green fields on the Inventory tab using the Totals row.

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

If this is helpful please accept answer.