I can’t believe it’s the end of November and it’s been 3 1/2 months since we returned from Bike Trip 4.0.
You may have noticed I still haven’t finished the bike trip time lapse videos yet. I’m… still working on them. I always have grand plans on how I want to do them, but realizing I don’t know how to do half of what I want to do I end up procrastinating until I end up just rushing it to finish. I’ve also been fairly busy with a mix of fatigue and some depression mixed in.
I spent a good portion of September replacing a roof at my day job, while doing my normal maintenance work at the day job. I had some long days that wore me the hell out.
I’m currently also working on a new Warehouse Paper Inventory database for our warehouse. When I originally started working for this company in 2004 I started in the warehouse. There was no real way to know what stock paper and envelopes were in the warehouse. I should mention the the warehouse that I worked was in our Commercial Building, where sheet goods (brochures, books, anything that goes through our sheet fed presses) and envelopes are printed. The warehouse was mainly intended to house finished product to be sent out to our customers. Our main building houses web presses (paper comes in rolls) and our wide format department. Rolls of paper are kept in an old DOS based inventory program by weight. Wide format you have to manually look at what you have, but it’s usually right there so it’s not too difficult.
The sheet paper is difficult. We have standard house stocks that we keep on hand, mainly 50lb and 60lb offsets and Gloss Text and Cover in 80lb and 100lb. Anything other that that we order per job. Depending on what is ordered there may be left over stock. So if you need 300 sheets, the packs are in 250, so you order 500 and end up having 200 left over. Multiply that over many jobs and many types of paper and you have stacks upon stacks of various papers.
When I started the Inventory system consisted of notebooks sheets of paper with the inventoried stocks written on it… from about a year previous. So it wasn’t completely helpful. I was brand new to the company and had someone asking if I had a particular paper. I hadn’t a fucking clue what it was let alone if we had it. I asked the girl that was training me if we had it, she didn’t know, but then asked who was looking for it. The person that was looking for it just happened to have the same first name as one of the company’s owners, so she thought I meant the owner. She said “OH! If he’s looking for it you better do everything you can to try and find it!”
Like I said, I was brand new to this job and that put the fear of god into me, and I certainly didn’t want to lose my job because I didn’t know if we have a particular kind of paper that may or may not be hidden somewhere among 20 skids. The easiest thing I could think of at the time was to create a spreadsheet. I was pretty well versed in Excel at the time, and just need to create an updatable list of what we had. I eventually split up the list into multiple sheets by paper type. I also gave each package a unique code. My code was the “bin location”-“serial number”. So if the bin location was D24C the first package was D24C-001. It seemed like a good system when I made it. I had long been wanting to create an actual database at the time, and since, but I didn’t know how for a good long while.
I personally used that iteration of the database for 5 years. I was then laid off for a month and came back to a different position, multiple actually, since then. That same inventory is still in use. Although it technically still works, it’s not without it’s issues.
When I first created it, it was solely meant to be a list of paper in the warehouse for warehouse use. As stated above, we have 2 buildings. And it’s a spreadsheet. Originally it was in Excel, but at some point the computer was changed and they installed LibreOffice. I don’t really know LibreOffice, so I’m not sure of it’s capabilities. One of the issues with it being a spreadsheet it that if you open it, and someone else opens it, that second one will be read only. I was recently told you could make them shareable, but it still won’t update the info unless you close and reopen the file. If no one closes and reopens the file, well then the information is never updated. On top of that, for whatever reason there are now multiple versions of the same file saved. Most likely because it was open in Read Only mode and then someone tried to save it, which prompts for a new file name. As such now none of the files match each other so no one really knows which is correct.
It was much later from when I created the spreadsheet that I actually learned how to make a database and how it should work. Sure, I only know Access, but it’s a step up from the plain Jane spreadsheets I was originally working with. Some day I’ll learn whatever the next step up is.
Anyway, I got Access because, at the time I was baking on the side and wanted to keep track of orders, products, purchases, invoices, all that good stuff. I had made gigantic spreadsheets with drop down boxes and all kind of formulas. It worked, but it was a little clunky. I tried using OpenOffice Base, but I had zero experience in anything database and so had to learn everything from the ground up. EVERYTHING. I want to do this, how do I do this. *look it up* Someone else of an OpenOffice Base discussion forum also wants to do that too. Something pretty basic, that it’s pretty standard Database fare. Reply: “Well why would you want to do that!? Do this that is the complete opposite of what you wanted to do”
I gave up on Base after that. I’m sure it’s good, probably, but as a complete n00b it was hard to learn. I broke down and got Office 365, which was new for me. I had been using free, extremely cheap, or 15 year old versions of programs up to this point because I couldn’t afford to update or was just too cheap. At least now I’d always be updated.
So I tried to learn Access. Same thing, ground up, had to learn the fundamentals. But I was also trying to do this at peak baking season, so it wasn’t going well. I ended up keeping it in Excel, but made a VBA form to help with input. It worked, definitely didn’t work on normal database fundamentals, but it got me through. Until Office 365 updated and something broke in my VBA code. I never did fix it, I didn’t know enough how the code worked to know what the issue would be. I stopped baking a couple seasons after that anyway.
I kept Office 365, and always intended to learn Access, if for no other reason than to make a baking database in case I ever got back into it.
Fast forward a few years and now I’m Head of Maintenance. I have 2 buildings full of equipment, randomly placed spare parts, and generic parts that could theoretically fit multiple types of machines but each machine having different part numbers for the same part that itself has it’s own part number. For example: Take a 1/4-20 x 1″ socket head screw. I have those in stock. Equipment manufacturer 1 called that Part# ABC1234, but Equipment Manufacturer 2 calls it ZYX9876, but the vendors that I actually get the screws from have their own part numbers, and if I order then from multiple places depending on availability or where I happen to already be ordering from, that could mean 4 different part number for the same screw.
That’s when I learned how Relational Databases work. I am by NO MEANS an expert in this stuff, and I still have to look up anything new I want to do in a database, but I learned a lot learning how to keep track of my parts. I started very basic and added features as I needed them. I actually had to do a complete re-write about 2 years in on how I kept track of my vendors.
For those interested my solution for keeping track of everything was as follows:
I have multiple tabs, with the first being a General Part Description. I have that Split in Category and Sub-Category Combo Boxes. For Example Category: Fasteners, Sub-Catagory: Socket Head Screws.
There’s a listbox showing all the Socket Head Screw sizes, and when I click on one it opens a recordset that fills in textboxes with the item information. I can also fill out the textboxes and add a new item. at the botton I have another listbox that shows all the part numbers for that item. That comes from the second tab.
Tab 2 is Part Information. This is also where I had to rewrite because I initially didn’t think far enough ahead. Originally I had Manufacturer, Vendor, Part Number Description all listed as the main part information. That linked the ID of a part description on Tab 1. The thinking was I only ever order certain parts from certain vendors. So sticking with the 1/4-20×1 Socket head screw example:
I have an OEM/Aftermarket combobox (which is mainly pointless at this point) that populates a Manufacturer combobox, which fills a Part Number Combobox. So if I order the screw from Bolt Depot (which I just list as the manufacturer) and their part number of 5027, Manufacturer: Bolt Depot, Part Number: 5027. I could then at that to a report that eventually would get emailed to Purchasing. Or if I decided to get it from McMaster-Carr: (I don’t know the manufacturer, so I’d put it under Generic until I found out the manufacturer, or usually leave it there) Manufacturer: Generic, Part Number 91251A542. Both of those would then show as an Associated Part on the Part Description tab.
That worked well, because the manufacturer and the part number were different. But what happens when you order something else that’s not vendor specific? Say I want to order an ABB Contactor part number A63-30-11-84 (which actually looks like it’s discontinued now. Oh well). Manufacturer: ABB, Part Number: A63-30-11-84. But I had the vendor on the main Part Number screen. If I get it from Galco they use A63-30-11-84 as their Item Number, so everything matches up. But what if Galco doesn’t have it, but Allied Electronics does (or, well, did). They have an Item number of 70094569. True, you could also search by the Manufacturer part number, but normally you’d want to used the SKU of the vendor. Also Now I have to enter the same information for the same part with the only thing being different is the vendor.
It was a fairly easy fix, technically speaking, I took out the vendor information from the main part of the tab and added a separate list box for the vendors of that particular item. So now I could then list the Manufacturer Part Number, and list as many vendors as I wanted, each with their own Item Number for the same particular item. I say it was easy technically speaking because there wasn’t much to it, but I did have to add new tables, split up Vendor and Manufacturer part numbers, and rewrite code for adding to the Orders tab and for the reports, for 1000’s of items. So it pays to think ahead.
I’ve thought of doing a YouTube series on Access work because I’ve had to spend so much time looking up how to do everything from multiple sources. I think either videos are made either too basic (just drop a datasheet on a form with record navigators. Ta Da! Database) or too confusing (Assuming you know everything about coding right off the bat. Like I want to know WHY I’m typing that like that not just “Type this”). Someday I’ll have my office set up for videos.
Anyway, I finally learned Access and was going to make a warehouse inventory. You can open Access forms on computers that don’t have Access itself by using an Access Runtime Environment. Though it only works on Windows 7 and newer. Of course most of our computers are running XP. It does seem now that we have enough newer computers in enough locations to run a better, actual database for our paper inventory. Another hiccup though has been that in the 15 years since I made the original spreadsheet, multiple people have started using it for various other purposes, like cost accounting, and paper ordering, which it was not designed for. So, now I’m trying to incorporate those features in also.
I was just about to wrap this up when I forgot to talk about the package bin-location codes again. I had it as Bin Location-Serial Number. We also have separate codes for house stock skids, which were just numbered numerically. However, each type of paper started at 1. So 50lb Offset 19×25 had a Skid 1, 60 lb Offset 19×25 had a skid 1, 80# Gloss Text 19×25 had a skid 1, etc. So that required another spreadsheet to keep track of bin location serial numbers (for each bin location) and the skid numbers for each kind of paper. It’s… It’s a mess honestly.
The new iteration is just going to have an AutoNumber ID for each package (I’m calling it a VSN). The plan is to automatically assign a VSN to each package as ordered (or at least each listing on a PO since we won’t necessarily know how many packages we’ll actually get) or each Skid of House stock. So if we get 1000 sheets of paper for job 123456 and it has a VSN of 9876, the inventory for VSN 9876 with be updated to 1000 when it’s checked in. When the press operators take it out of the warehouse the inventory can be updated with -1000. If they only use 800 sheets, the inventory can then be updated with 200. That VSN number will follow that package of paper through it’s life. When it goes on the shelf the only thing that needs updated then is a Bin Location.
Of course I also need a way to add VSN’s to packages that weren’t ordered within the system, say, the stuff that’s currently sitting on the shelf. Or when it is ordered in the system but it comes as multiple packages.
It all sounds good, as long as there’s cooperation and everyone uses it. Last year I was working at my first employer, helping with warehousing. They had zero system in keeping track of what was in stock. They were working on a system, and had been for years, but it wasn’t done, nor great. Only parts of it worked, for end of year inventory, and that was prone to issues. I ended up writing a database for them as a step between the nothing they currently had and the greatness they were working on. All I needed was for the workers to write down that they took out of the inventory. You come in and take a bundle of 25 boxes, write down I took 25 of these boxes. I tried to make it easy by giving everything a code number. So they could write “Code 10, -25), or “took 25 of Chocolate Box,” whatever, as long as I knew what it was. I had ZERO cooperation. The owner thought it was great, and asked everyone to do it, but alas, everyone fault tooth and nail, which also not being able to find where anything was, nor knowing how much was in stock (I would print them daily reports as to bin locations and inventories for each location). I ended up leaving shortly thereafter since I felt like I was wasting my time.
