The following is the text of a word document that explains how my music database works.  A sample of the MS Access 2.0 database and this Doc file are available for downloading. (It's about 300Kb)

This is a Music Database that I developed to keep track of my personal music collection. I think I finally decided I needed something when I bought my 2nd or 3rd copy of U2’s "War" (I kept forgetting I had it). It was never intended for anyone but me, and it is offered not as shareware, but as an example of the kinds of things that can be done on Access 2.0 by someone who is basically "code averse". That being said, there is no active security or permissions, so feel free to poke around.

A few words about my taste in music, and how it drives the structure of the database. I have a rather eclectic taste in music, and enjoy variety. My CD players use those magazine dingusses that hold 6 CDs. I assign my CDs to a specific slot in a specific magazine. I try to mix up the music so that when I play a given magazine on random, all different kinds of stuff are on there. The mix will jump from Bach to White Zombie to Sinatra to Bonnie Raitt to Van Halen to The Pursuit of Happiness. This sample database has been cut down to 25 albums; my version of it has about 800 albums with 50,000 annotations (roughly 15 Mbyte of data, with lyrics).

The Basic Structure of the Database:

tblAlbums The table where the album-level data is held. It includes purchase details, what Mag it’s assigned to, whether it’s Classical, Jazz, Rock, etc.

tblTrack Track-level info. Contains the AlbumID, foreign key from tblAlbums. Track titles, number, time, etc. Track Num "0" for a given album is used for many album level annotations; e.g., "produced by Daniel Lanois"

tblArtist Table where all the Artist Names appear. Note that the artist name appears "in english" first, and there’s a separate field for alphabatizing; e.g "Stevie Wonder" has an alpha field of "WONDER,S"

tblContrib This is the list of all the types of contributions that artists make to music. It has entries like "Vocal" and "Written By".

tblArtistContrib This is the annotation table. It consists of 3 foreign key fields: TrackID, ArtistID, and ContribID. It’s pretty lean in structure, but very deep. This is the table that has 50K records in my version.

tblHistory is an admin history table I use in most of my databases.

tblLibraryMag was a separate table I kept to keep track of music that I borrowed from the library; there’s a union query in here somewhere that combines the two.

tblListenHabits I keep track of which magazine of music I’ve listened to when (at home and in the car) so I can figure out which music is "out of rotation".

tblLyrics Has foreign key of TrackID and a memo field for the Lyrics. (Most if not all of these I’ve snipped off the WWW over the years).

tblMembersGroups is a table to associate artists with groups; e.g. Paul McCartney is a member of The Beatles and a member of Wings.

tblVendors is a table of the places I buy music.

Oddities:

The Album Name appears in two places, tblAlbum and also as the Track 0 name in tblTrack. I’ve taken a run at normalizing that a few times over the years, and always end up with big performance penalties. I’ve decided to live with it.

The track time is of course a formatted date/time field, and is therefore storing an elapsed time of 3:32 as "three and one half minutes after midnight, January 1, 1900" or somesuch. Once again, I’ve tried a few times to streamline this into various versions of integer minutes and seconds (like the knowledge base articles) but always paid a performance penalty.

I often end up documenting medleys as fractional track numbers. Example: if track four is Jingle Bells / White Christmas / Silent Night, it will show up as:

4 Jingle Bells

4.1 White Christmas

4.2 Silent Night.

This is messy, but just when you think you’ve got a rule that works (like one track, one title), someone rains on your parade.

Due to the various relationships and validation rules, it can get dicey to add data at the table level. That’s the purpose of the "Add New Album" and "Add New Track" buttons on the start menu.

How I Use It:

When I buy a new or used album, the first thing I do is add the artist to tblArtist, if I know they’re not in there already. Next, I press the "Add New Album" button on the start menu, which invokes frmNewAlbum. There, I fill in the Album Title, the Magazine assignment, the Album Title again in the TrkTtl field, "0" in the track field, and select the Artist from the combo box. I’m sure I could find a dandy way to "auto paste" the album title in the trkttl field, but I never got around to it. Ditto with defaulting to Track 0.

Next, I select "Add New Tracks" on the start menu, which invokes frmNewTracks. This form defaults to the most recently added album. The macro button on the form invokes a "copy - paste append" bit of code that also auto-increments the track number. I enter the track titles in sequence. If there is something non-sequential about the tracks (see "medleys" under "oddities" above), I fill in the number I’d like by hand (overtype).

If I want to note the track times (I generally do, is this some sick compulsion, or what?) I next open tblTrack from the database window and manually enter the track times. Note that the three leading zeros are required in the time field (2 leading zeros in the case of a song 10 minutes or longer, your actual mileage may vary). I kept this as a separate operation from entering the track titles, because sometimes the track times are a closely guarded secret, whereas the titles are not. If all is right with the world, invoking "Time Sum" off of the start menu will cause the track times to be added together, and the sum deposited in the track 0 record for that album. Note that in some views of this data, 63 minutes will look like 3 minutes. Where it looks like that, it’s generally not something I use. It looks fine on the reports it was designed for.

Next, I’ll press the "Album" button on the start menu (invoking frmAlbumInfoBigLyric) and fill out detailed information about the album itself. This includes info on new/used, price, date purchased, part of a multi-set, a subjective 1-5 rating (default of 3), the vendor, whether it was a gift (from/to whom), the label name, the year it was released, and a short "Why?" (What possessed me to buy this?) field. It’s on this form that I set genres for that album. Each button corresponds to an Yes/No field in the underlying tblAlbums. The Genres I track: Jazz, Classical, Rock, Alternative, Metal, Pop, Rom (as in enhanced CD-ROM), R&B (Which is called Motown in the underlying table; I now realize there is more to R&B than just Motown), Soundtrack, Comedy, A Capella, Country, Greatest Hits, Female, Box Set, Tribute (like the Encomium Led Zeppelin album), Compilation (like a Billboard Hits of 1969 CD), Live, Christmas, International (Gypsy, Shankar, Brazil, Mambo), and Standard (Show tunes and the like). There’s a lot of overlap in genre, and I’m not sure why I’m still tracking Box Set and Multi, for example.

Next, I’ll press "Contribution" on the start menu (invoking frmContribEntryBig) and find the album I want to annotate. When I get there, if these things have been done in sequence, I’ll find the album and track have been entered with the selected artist as being the "Prime Artist". By selecting Track 0 of the selected album, I can use the Contribution Entry sub form to assign different artists to that album according to their contributions. For a Beatles album, this is where I might assign John, Paul, George & Ringo as Guitar, Bass, Guitar & Drums. If the contribution is "homogenous" throughout the album, I’ll assign it at track 0. If I want to note that George Harrison wrote "Within You Without You", I’d note that on track 8 for that album.

A few notes about the Contribution Entry sub form: The "Artist Name" and "Contribution:" combo boxes are set to "limit to list". There is a sub form for "New Artist Name"; this is not limited. The underlying tblArtist has the name field as a unique index, and will reject duplicate names. The alpha field (in dark blue) is not unique. When you’ve completed a new artist record (1 or many) and you return to the Contribution Entry area (via mouse, I don’t think it’s set up do it any other way), a bit of code is invoked to have the Artist Name combo box requery, so the new artist is available. Incidentally, new "Contribution" categories (e.g. "Soprano Soloist") must be entered in the underlying tblContribMaster, and the form re-opened (I find I don’t need to do that very often). The alpha field (in white) is not linked to anything, it just serves to jog your memory if you can’t remember if you filed an artist as "Frank Sinatra" or "Francis Albert Sinatra". Using the white alpha field, you can type "sinatra" and see what’s in the table.

On the main Contribution form, you’ll note that your fresh entries don’t appear in real time. If you want to check your work, position the cursor in the main form, hit <shift-f9> to re-query, and find your place again. If a record in the main form is in error, you can highlight the record selector and delete it. All you’re deleting is the specific annotation, not the whole track. Conversely, when in the main form, if you see an artist name that bugs you, you can edit it. Caveat Editor: change it once, and it changes everywhere; e.g. changing "Francis Albert Sinatra" to "Frank Sinatra" in one record changes the underlying tblArtist, and therefore all of the sinatra records.

"Contribution Alpha/Song" on the start menu is basically the same thing with a filter. It allows you to cross-check if you have "Jingle Bells" attributed to "James S. Pierpont" on one album and "Public Domain" on another. Some of the code doesn’t work right on this one, as it’s pretty much a crude cut and paste job. Best not add any new artists on this one.

That’s about it for entering data.

Other Features, or How I Amuse Myself and Annoy My Wife:

The "Artists Tool" button on the start menu fires off a chain of events I use to periodically "Normalize" my tblArtists. It sorts the Artists in Alpha order and highlights perceived irregularities. It highlights "Mono" names like "Madonna". Often this is a sign that the entry is a partial (as in last name only); alternatively it can highlight an inflated ego. The "Near" field compares the first five characters of the Alpha, and if there’s a match from one record to the next, it highlights it. The "Duplicate" field is similar, but highlights an exact match in the Alpha field. I’ll typically normalize my artists after I’ve assimilated a particularly well annotated chunk of music like "Ella Fitzgerald Songbooks" or "Ray Charles 50th Anniversary Box Set".

The "Search Contributions" button on the start menu invokes frmArtistContrib. This is a fairly complex little form. Type the Artist Name you’re interested in, and the main form will display all annotated records where he or she appears. The sub form in the footer displays all other annotations for the highlighted record in the main form. Type in Paul McCartney, and you’ll see that he wrote this and that, played bass here and there, etc. Examine the sub-form for any one of those records, and you’ll see that this John Lennon character co-wrote much of the best stuff, and George Martin shows up often, too. In the header, if there are entries for this artist in tblMembersGroups, the group affiliations will show up as well.

The "Multi-Contributions" button on the start menu brings up qryContribSearch in design view. If you know the ArtistID of 2 or more artists you want to compare at the same time, you can enter them under ArtistiD and look at a datasheet view. Thus, you can look at the records associated with "Little Richard" and "R. Penniman" and note the startling coincidence that they often show up on the same song titles. (been there, done that. Oh, the epiphany!)

The "Wildcard Artist Name Search" button calls qryArtistNameSearch. This is a parameter query that asks the user to enter the artist name they’re searching for. It supports wild cards; e.g. "*johnson*". It returns all annotations that contained "johnson" anywhere in the artist name field.

The "Album From" button calls qryArtistAlbumFrom (which in turn calls qryAlbumFrom). This lists all of the "from" contributions in the database. (For a discussion of the "from" contribution, see the paragraph below under "Excuses, etc.")

The "What’s Next" button on the start menu kicks off a chain of events that essentially returns a list of music I haven’t listened to in a while. The definition of "in a while" is a date field in qrymListenHabitsHistory. It assumes I’ve rigorously noted what music I am listening to (via "Listening Habits" button).

The top section of the start menu is all benign stuff. I designed it so that my wife, who is not an Access user, would have some hope of finding her beloved Patsy Cline CD. The 21 Limited Sort buttons return lists that conform to the button. She could hit "Fem"(ale) or "Ctry" to find Patsy. Alternatively, she could hit the Albums By Artist Name button and scroll away. Don’t forget to go to Form view if you want to view details. The "Album View" presented by these buttons also gives an overview of the annotations associated with that Albums’ Tracks.

There are four printed reports that can be had from that top section, as well. One is Albums By Magazine Assignment (could probably be modified easily to look like a 100 CD changer instead of a magazine, if you had the urge). Another is Albums by Artist Name. The third is "Genre", which is a fairly crude report that gives me the genre balance in each magazine. I mentioned eclectic tastes; this is how I manage it. Too much "Metal" makes my head explode, too much "Country" makes me suicidal. The fourth report is a summary data sheet. If you’ve been totally anal and filled in all the blanks accurately, this summary gives a snapshot of where you’ve been buying things, for how much, new vs. used, and the subjective quality numbers for the collection. Seek professional help.

Excuses, etc.

The full-up version of this database exists in six separate .MDB file with each one being less than the size of a floppy disc, except the Lyrics MDB. I made some effort to simplify as I was "trimming weight" to demo this; it’s all in one database now. I also took the opportunity to excise some of the more egregious examples of dead code. Obviously, I also slashed the Album count down to 25 to try and keep the overall demo under 1 Mb.

For all of the above reasons, and general incompetence (incontinence?) some "features" don’t work the way they used to. Press "Ctry" and get a null set (I didn’t have that much country to begin with). Ask for a Magazine report or a Genre report and you’ll wonder why I went to all that trouble. They look pretty goofy without a full set of albums.

There’s still plenty of useless objects in there (Hey kids! Can you find all the dead code?).

I didn’t really discuss lyrics, ‘cause they don’t work real well. You have to append records to tblLyric based on tblTrack. There’s probably a query in there somewhere that does that. There’s an frmLyric you can use to type in lyrics or paste them from another source, if the underlying record exists in tblLyric first. You can examine the lyrics in many of the "Album View" forms (like the limited sorts. Rightmost data in Track area). Highlight the Lyric field and hit <shift-f2> to invoke the zoom window. (is there a deeper meaning to "four thousand holes in Blackburn Lancashire,"?) As I said earlier, nearly all lyrics are poached off the WWW, don’t blame me for accuracy. I’ve sort of lost my taste for hunting down a good lyric, particularly with the demise of the U Wisconsin Parkside Lyric Server (Damn, the Stupid Lawyers).

I recently began corrupting the Artist and Contribution portion of my Database with some "Bad Fits". I wanted to be able to tell where a piece was "From". This could be "Anything Goes 1934 (S)" (the 1934 show of that name) or it could be "Come Dance With Me 1959 (A)" (the 1959 Sinatra Album of that name, annotating a box set collection). In some cases, I also wanted to know where a piece was "Recorded At". This could be "Live at Budokan" or "Bad Animals Studio, Seattle". The resulting data, while interesting, no longer really fits the pure definition of "Artist". We all make compromises.

What did you expect for free, anyway? If you’d like to contact me, try me at work todd.s.peach@boeing.com or at home Todd & Sharon.  If you’re scratching your head over something, I’ll try and point you in the right direction, though chances are if you found this in it’s intended location at the Boeing Access Developers Web Site, you’re way beyond me. If you’d like to recommend a decent therapist to deal with this obsessive / compulsive thing, I’m listening.

Back to the Music Page

Back to the Home Page

Send e-mail to: Todd & Sharon <tpeach@gte.net>