Personnel "kits" in microsoft access or excel

Katie Scott 1 Reputation point

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?


Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
776 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,563 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Dillon Silzer 49,671 Reputation points

    Hi @Katie Scott

    I have developed a solution for you which can be downloaded at!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.