How do you manage "on stock" component on your personal lab?

I use a MS Access database. I started with an Excel database but it just doesn’t scale - not that I have loads and loads of parts on hand, it just became cumbersome and I had great plans to set up projects with part ordering, accepting into inventory etc..  it works well enough without that though.

I admire the commitment of those who maintain a documented list of stock part numbers and quantities. I have so much stock, just doing an inventory would involve a scary amount of work. I do have a system though, which more or less works. I standardize on common parts and and when designing assume they are in stock. After designing a PCB and sending it out to get printed, I collect the parts kit into a project box and order anything I don't have in stock. Resistors and capacitors don't go in the kit box, because they are organized in dispensing systems that make it easy to grab the correct value during assembly. When I use unique or non-standard parts, I often order more than I need and end up with a few extra parts. I leave these in the project box where I hopefully can remember to find them. My CAD system helps, because I often reuse "known good" portions of schematics so I know which project box to look in for the parts. However knowing whether I have the parts is still the easy part of the system, Knowing where they are located is the harder part. Good labels are important. This year I will be reorganizing, purging and consolidating, mostly to free up some space.

How do you store the parts and know where to find them? That is the bigger issue for me.

For personal stock and projects, I tried using all of the various online part-database services. I disliked all of them. Most are highly focused on pulling Octopart data on active ICs. The problem is the majority of my stock is actually passives. I only buy the actives I need for a project (plus 1 or 2 extra). However, I always buy a lot of extra passives (when I buy them.)

What really drove me crazy with all of the options is SO. MANY. FREAKING. CLICKS.to do anything. And in the end, they ALL have LOUSY search engines. Personally, I think my ideal solution would be if Octopart let me upload an inventory, and then their search showed my results as one of the distributors. Anyway.

So, after trying them all, I settled on Google Sheets. I briefly started setting up a SQLite database. But even that had far more overhead than I needed. I just wanted a list of the parts I have with a few key details and a hint on where I put them. And like all organizational tools, it could NOT be cumbersome to use.

I preferred an online spreadsheet for two reasons: 1) I can easily access it from any of my devices, and 2) Sheets has the magic "query" command. The cool thing about "query" is that you can select a data range and then perform sql-like queries on it.

Despite the fantastic functionality, I found I didn't need it. The search I found myself doing most often was: "what is the largest capacitance I have in the smallest available package." So for capacitors, I added columns for the farads as farads. And then two additional columns for numeric + units. That way, I can sort on capacitance but have James-readable labels like "10 uF". I did the same for voltage. On resistors, I did something similar.  With those columns simple filtering is all I need!

On actives, I have found a column to describe the part (Linear Reg, LDO, Op Amp), and then two columns for characteristics is all I needed. (Along with part number.)

While I know others want a way more complicated database, I didn't. By breaking up type of part into their own tabs and adding a tiny bit of metadata, this lightweight spreadsheet has the critical information I need and is quick to use.

Whenever I receive parts, I add them to my spreadsheet while handling them. It's a very incremental effort and has drastically reduced my re-order of existing parts.

If anyone is interested, here is a read-only version of my parts inventory.

At the moment I don't have a lot of components so finding a specific one is pretty easy. I've mainly two "big boxes", one for passives and one for "active devices". Passive devices are further divided into smaller boxes by type ( resistor smd, resistor th, capacitor smd, heat sink etc... ) and each one is labelled. Once I have the "small box" on hand I have to find the bag with the right component value.
Active devices instead are just stored with their own labelled bags inside a separate box, without additional indexing (I don't have that many different ICs)

Andrew J said:

I started with an Excel database but it just doesn’t scale

Yes exactly, same for me

dougw said:

Knowing where they are located is the harder part. Good labels are important

That's a good point. I don't have this problem at the moment due to limited number of components, but it's definitely something I will keep in mind

Hi!
In a design lab where I worked, there were about four main ways to get parts into your hands to build projects! And two important ways were:

(a) Generic parts like connectors, resistors, capacitors, etc., were not tracked, there were just boxes of them, so you could take as many as you like. For a home lab, resistor kit books, capacitor kits and so on are great. I get through loads of pin headers and sockets, so I don't track those either, and have a little plastic container full of pin headers, and a little component drawer full of those too, so I always have them available one way or another. I think I've got every E24 value of resistor, in 0805, and a smaller range in 0603 and wire-ended, and they are not tracked, I just make sure I have many of those since they are low-cost. I've got a few resistor books and capacitor kits, and quite a lot of Coilcraft kits (I like those for RF projects), one or two Wurth kits, and don't track the parts in those.

(b) Semiconductors and all non-generic parts in a warehouse/store, and there was a database to use to check out parts. I've got an Excel spreadsheet that is now inaccurate, but since I usually order from Farnell, I just use e-mail search to see if I have ever ordered the part, to know if I have it or have had it in the past. I have about four boxes (about the size of shoe-boxes) for semiconductors, one containing analog and data conversion parts(in their original Farnell bags), one containing discrete parts, one containing digital parts, and one containing microcontrollers. I don't organize them any deeper than that, and just tip the relevant box out to search for what I want. Some semiconductors come in trays or long plastic tubes that won't fit in the boxes, so they are in effectively a small wastepaper bin type container with no organization, I'll just search through that.

I have them in labelled containers and identified shelves which is recorded against the inventory.

I think that in the end I'm doing something similar to your spreadsheet: I find myself mainly using filters too and nothing more. I actually like the additional column with the location of the component and the "sort and label" functionality with numeric value and "human readable" value.