Excelling at data Access

Well, it's been a bit. Since the last post, I've been doing a lot of thinking about music- my music collection specifically, and how to best quantify it. I had decided that I would use either Access or Excel for this project.  After much thought, and several trial-and-error tests, I came to the conclusion that the best solution for my particular application would be to use both.

Why? After all, it seems to be a bit counter-intuitive to create multiple copies of the same dataset and have it in several places. However, in terms of music, when quantifying, I not only mean artists and albums and similar facts (which belong in 42, my database), but I also want to create a sort of "Top 42" group of lists which will track usage, rather than ownership. In other words, Access will store data on each album- title, artist, format (CD, mp3, etc.), UPC (if applicable) and related inventory type data. The flat database (spreadsheets) will be used to store the usage data, such as number of plays, last play, plays per artist... and any other minutiae that pretty much only I would be interested in.

As for duplicated data, I expect there will only be two datasets that are duplicated- one will be the artist/album dataset, and the other will be the album/song dataset. The former will be my master list of albums, and the latter will be a list of those songs that are a part of a collection, compilation or soundtrack, or individual mp3's that were not part of an album when acquired.

I'm very much looking forward to the results of course, but before this grand Top 42 launches, I've got a great deal of data entry and design to do. The data entry is proceeding slowly but steadily, with twenty-one albums and their tracks. Track (song title) and total times still need to be added.

A corollary project is the cleaning of the music players on two computers. Over the years, several standards were partially applied to these databases, and as a result, the organization is... well, it isn't very organized. As these will be major sources of quantitative input for the Top 42 flat database, they need to be cleaned up, standardized and partially deduped.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

Data before year's end

I'm going to apologize right at the start- this is not a Christmas blog, nor is it a year-in-review blog. It's merely a near end-of-year blog about the state of my data.

So, what, exactly, does the author wish to communicate? Well, for starters, I ran into an interesting enigma in my data....

Long ago, I purchased some CD's that had sound effects. Today, the internet has made them fairly irrelevant. So, the not-so-big data question is this: do I include them in in my inventory, even though I may never use them? Honestly, even though this database IS at least in part intended for insurance purposes, I don't think I'll miss those two disks. How about videos? I have a DVD produced by the Chicago Tribune which commemorates 9/11. It was free with my newspaper subscription, and is probably irreplaceable. But- I'm guessing it's hardly collectible. So, that one will also NOT make the cut. Books- both physical and e-books... I'm nowhere NEAR starting on those, but I think you can see the trend.

Owning the data gives the DBA a huge advantage, as is shown in the previous paragraph. The problem, though, is that if you're a small-time operator (as I am), there's no one to fact-check, proofread or cover your (data) back. So, I'm hoping to correct at least one of these deficiencies in 2019, by enlisting someone else to do some of the data entry (we'll see how that goes!).

*****Happy New Year*****

Well, I want to publish this, so I may as well post a few New Year's thoughts. For starters, it's officially 2019 in my neck of the woods, so Happy New Year. I'm enjoying what is probably the best egg nog I've ever had (from Southern Comfort- but no alcohol in it) in a vintage 1960's vintage tumbler. The tumbler is tall and narrow, with dusty rose screening of different cities around the world- rather appropriate for New Year's Day, n'est-ce pas?

I don't follow many other blogs, but I do follow some blogging "experts" on twitter. I generally agree with the "experts"-  one of the secrets to blogging is frequency; the other is content. If you've been following this blog for any length of  time, firstly, thank you. And secondly, thank you for putting up with my subject matter- I'm hoping that this is a reflection on YOUR eclectic reading tastes.

And FINALLY, the state of the database, December 31. 2018. As of the last entries, the music database stands thusly:

Number of groups: 156

Number of albums: 79

Number of songs: 1035

And that is all for 2018. I wish every reader a peaceful, joyous and profitable 2019.

As always, I am hochspeyer, blogging data management and and analysis so you don't have to.

Graphically speaking....

The SUL (Secret Underground Lair) has been in a bit of turmoil. This past year, in the space of a month, two of my computers have died. In your world, perhaps, this might not be a disaster. Then again, in your world, you might be off to the Apple Store, weeping inconsolably. If the latter is you, this blog is not for you.

So, a PC died several months ago. No biggie-- I put a spare in as a replacement (note to Apple fans: how many of you have 2-3 in spares?)

Unfortunately, my spare died as well.

To be honest, it didn't really die- it just lost internet connectivity.  So. I was living off of my laptop, and Mr. T. and I have decided we're going to build a new desktop. For me.

I started writing this several months ago, and not long after that, my laptop battery gave in. Not a big deal- just another kick in the shins.

In the interim, I pulled another PC out and swapped out the hard drive. In short order, it, too, gave up. Mind you, both of these desktops were originally XP boxes which had been upgraded to Win7; that should give a hint as to their age.

Fast forward several months and I am running on Great White, a massive box with a tiny non-Ryzen AMD mobo. How tiny is the mobo? Well, the case is so large and the mobo is so tiny that the front case usb cable does not reach the usb header on the mobo. The specs of the board unsurprisingly are fairly underwhelming, as one might expect: 16GB (2 sticks) of DDR3, for starters- and this is maxed out.. Well, it does have a 550 watt 80+ EVGA platinum certified modular PSU, 120GB flash boot drive, 2TB internal HDD (with room for five more HDD's) and a 5 channel fan controller. Other than that, 3 external USB HDD's and... well, the graphics are on the CPU (AMD APU). On the bright side, the APU graphics are much better than I experienced on the previous APU-supported mobo.

Fast forward....

So, I was on amazon.com Wednesday and saw a graphics card that seemed pretty decent for the price. I mentioned it to Mr.T, and he was at first unimpressed. After a few cups of tea, though, it looked like a better deal, and then he looked into some cards. Now, truth be told, I do not absolutely need a graphics card right now, but the graphics card will probably be the next upgrade to this system as it is the only other stand-alone component which can be added before upgrading to a Ryzen.

Interestingly enough, it's now the wee hours of Tuesday morning and I'm *finally* getting around to finishing this. Among other things, I got some work done on 42 (in case you are new to this blog or have forgotten, 42 is my personal database project). Progress has been predictably slow, as my keyboarding skills are not really skills; I think I'd say I'm more familiar with a keyboard than skilled. J and I got new keyboards in November, and I'm loving my new mechanical keyboard. Cherry MX blue switches are absolutely to wonderful to type on, and for some reason I find myself making fewer mistakes on this keyboard.

As always, I am hochspeyer, blogging data analysis and management so you  don't have to.

The Lego database, reboot

The "reboot" seems to be something of a thing in Hollywood these days, and so it only follows that Life would imitate "art" as I reboot my Lego database.

For those who have been following this blog for some time, I think you may be aware of my Lego database project. It is one component of "42", my personal, ultimate repository of those possessions of mine which I have chosen to catalog.

I've just started using Microsoft Office 365, and the Lego data came from rebrickable.com. Their dataset is very comprehensive, but I don't think I've ever downloaded ANY Lego dataset that was usable by me as downloaded... and this data is no exception.

I mentioned that the data is quite comprehensive, which means it contains things which I don't necessarily need or want. For example, I do not care about any decals, paper or cardboard items, books, or even certain parts of the Lego product line. So, these need to be removed. The data also appears to come from more than one source, and formatting is necessary. Punctuation needs to be removed from many entries, and part names need to be standardized. Some data needs spelling changes- there are cases of the Queen's English being used, so "windscreen" and "tyre" need to be changed to "windshield" and "tire". These are the major changes that need to happen before I can even think about exporting to an Access database. And these are just a few examples.

But, I digress. Here are the numbers as of December 4th.

When I first started out, there were approximately 29,000 records. As of last night, after culling out items which I knew I would not be inventorying, I had 27,164 unique items. When I deduped the group to include ONLY the base part numbers- excluding all decoration variants, I ended up with a working list of 8,747 unique base part numbers. As I copy the part descriptions to the new inventory list, they are being further culled.

I am now at the point where everything must be done by hand. I find myself going back and forth between rebrickable and my flat database, verifying that the part number in the list is a part that I (may) actually own... and want to count. At a certain point, some of the data is subjective, and even though it is valid, I will count a complete assembly rather than, say, a special tile, wheels and tires as separate parts.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

A very rare Friday night

As I think most readers have surmised by now, I'm an American. And, like it or not, each and every one of us- consciously or not- probably shares some stereotypical "national" behavior with a great number of their kinsmen, whether they choose to admit it or not.

Now, it's Friday night, and normally I would be busy at work doing something job-related. .. but, not tonight, because this is my first official sick day of the year. I say "official" because I had a sick day in October, but as I am eligible for overtime, I made the day up later in the week and was able to score some overtime.

 And this is where "national traits" comes into play.

According to studies I've seen, Americans- even when they are genuinely ill- do not always take sick days. I'm not sure exactly why this is, but apparently it's an "American thing". Many of these same studies suggest that American are loathe to take their full vacation.

I, for one, do my best to avoid taking sick days, unless they are truly necessary- and tonight is one of those nights. But vacation time- it's part of the employment agreement with my employer, and per that agreement, I am entitled to "x" amount of days off per year based upon my tenure with the company. And even though I may not do anything blogworthy on my vacation, I've earned this time off and to not take it would be cheating not just myself, but my family as well.

However...

There is a certain stigma attached to taking certain days off as sick, even if they are legitimate. For example, if one is a Major League Baseball fans (Chicago Cubs or Chicago White Sox where I live), if you were to take a Monday or Friday off as a sick day, and this day just HAPPENED to be your favorite team's home opener- even if you were really sick, the talk at the water cooler would, of course, infer that you weren't really sick, but attending the game. Likewise, the #2 reason for taking a Friday or Monday off is a big weekend.

Fortunately for me, none of these factors were applicable. I had taken Monday and Tuesday off this past week pretty much because I have vacation days left. But, when I came in Wednesday night, I almost immediately started feeling out of sorts with a major head cold building.  Thursday is our busiest night of the week, and I had a simple plan: take care of the important business, and then go home early.

That was the plan. It was brilliant- except for one thing: there were two other jobs that required MY attention, as we would be short-handed on Friday. So, instead of leaving early, I left late. And when I got home, I went straight to bed.

But before I left work, I spoke to a senior coworker about my situation. Generally speaking, one does not know at the end of a shift if one will need to take the following day off as a sick day. However, she encouraged me to take the sick day, especially as I had taken on two projects that needed to be done before the weekend.

Now, a treat.

Before I discovered ramen, this was my goto Friday night bachelor treat. If you can't make it out, it mac 'n' cheese, tuna, corn and mushrooms. This was my bachelor Friday night treat, and I made it tonight! Apart from the sodium, I'd guess it's a pretty healthy meal. The only modification I do to the mac 'n' cheese recipe is to double the milk from 1/4 cup to 1/2 cup. You can also add garlic powder, onion powder and celery powder to taste. Yum!

As always, I am hochspeyer,blogging data analysis and management so you don't have to.

...and I THOUGHT I was a basement A+ man....

It seems that my Dad and I, even though he passed away several years ago, share an affinity for hardware. The difference is that while his hardware was measured in cubic inches, MPH and RPMs, mine is measured CPU, GHz and FLOPS.

He was a back alley mechanic that learned it on the fly, and became a master of the craft. Me? Not so much of an expert, although I've built enough computers, spreadsheets and databases from scratch to be quite confident when opening something up, and I couldn't say how many PCs I've upgraded, rebuilt, etc.

Last Sunday night, though, I ran into something I'd never experienced before.

I'm quite familiar with the care and feeding (and installing) of hard drives (HDD's), and I had one that was way overdue for installing in Great White- my computer, not the 80's hair band. The project  started, though, with speakers. I had decided a while ago to do a peripheral upgrade to the PC prior to the (ouch) MOBO + processor + memory upgrade, so a few peripherals trickled in over the course of several months- HDD, mechanical keyboard, and finally speakers. The keyboard went on the weekend before last, although I still need drivers. I untangled all of the wires, prepared a place and installed the speakers; and cut some antiskid foam to keep everything from sliding all over the place. Mr. T had actually installed the HDD a few weeks ago, so I was all set to visit R-Stats and download the latest version of R, and then download Processing for the Arduino, and maybe start copying a clean- no dupes- version of my music library.

But, first I had to initialize my HDD... initialize? Yup, that was a new one on me. I've wiped, partitioned, formatted, repartitioned, reformatted and even used a hacksaw to remove a HDD from an oddly-configured case. But, I'm not certain that I've ever initialized a drive.

So on a Sunday night, I initialized a drive. Mind you, it wasn't difficult. Just new. Next, the printer. I finally got around to getting a fairly old HP all-in one tested and running again. Well- it prints every color EXCEPT cyan, and wouldn't you JUST know that I'm fresh out of cyan. I empathize with Rowdy Roddy Piper, as I came to print, and chew bubblegum, but I'm all out of ink... yes, yes the reference is pretty weak, but this is a mostly G-rated blog. I also got my social media accounts which had been in hibernation for some time back up and running, but the really big news is...

I found a Lego Master database. It's quite good, but not 100% complete. Then again, even Lego does not have a complete database of their own parts. However, I can get back to the inventory and database.

Now all I have to do is find and organize all of my Lego elements.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

Going on 31

I used to be quite active on twitter, but a hard drive crash put an end to that. And, I'm sorry to say, a new phone just added to my woes.

So, in lieu of twitter, I've somehow managed to stay active on pinterest....

I know, I know, pinterest is generally considered by outsiders to be the "stay-at-home moms" and "cat lovers" site, and not something "manly" or "professional".

Well, that's completely inaccurate. Pinterest is full of good stuff- programming, Lego, makerspace... the list goes on and on.

But-

This October will be Jennifer's and my 31st marriage anniversary. We've beaten a few of the "odds", I suppose, but at this point you're probably wondering where all of this is going.

Pinterest likes to send suggestions based upon your likes and post and who knows what else, but a few days ago, one of the suggested pins went something like this, "A king should not search for a princess in distress, but rather for a queen who will fight by his side."

I suppose I could have said this from Day 1, but after nearly 31 years, I would not choose to live without this woman.

Happy anniversary to my best friend, my lover and my confidant.