Creating a Personal Database
Shirley Martin wrote to ask what program I’d recommend for creating a "database" of her CD collection that could be broken down by song title, artist, genre, performance date, etc.
I suggested Microsoft Works for two reasons: it contains a very easy-to-use database utility, and at $39.95 it is less expensive than most similar programs. (Tips for creating a database in Excel can be seen near the bottom of this page.)
A "database" is a table of rows and columns whose intersecting "cells" contain various types of information. The columns are called Fields and the rows are called Records. The header cells of Shirley’s columns would have labels such as "Title," "Artist," "Genre," "Where Performed," "When Performed," and so on. A typical Record might contain Field entries such as "Billie Holliday," "Solitude," "Blues," "unknown," "1952," etc.
To create a such a table, launch MSWorks and choose New Database, whereupon you will be invited to create "Field" headings. The first Field (column) will be displayed with "Field1" as its default name." Type over "Field1" with, say, "Song Title" and click Add. Overtype "Field2" with "Artist" and repeat these steps until all fields are named. Then click Done.
A table will appear with all the Field headers displayed. Finally, fill in the table’s cells with the appropriate data.
To save the table, go to File>Save As to give it a name. MSWorks will add the extension .wdb to the name.
Data can be typed in randomly, but later you might want to display it alphabetized by, say, Artist. This is done by clicking Record>Sort Records>Sort By, and choosing the "Artist" field.
A collector with a very large database might even include fields for, say, "Gender" and "Orchestra/Band."
At some point this collector might want to generate a list of all recordings done by, say, female vocalists who sang jazz at Carnegie Hall before 1972. This would be done by going to Tools>Filter, and typing in a name for the filtering process about to performed. Next, look at the various "Boolean" filtering options, such as: "Greater Than," "Less Than," "Equal To," "Same As or Less Than," "Contains," or "Does Not Contain."
In this example you would click the "Gender" field, choose "Is Equal To:," and type F (assuming records in this column are marked F or M).
"Is Equal To:" would also be the option for "Genre" and "Where Performed," whereupon you would type "Jazz" and "Carnegie Hall" respectively. In the "When Performed" column you would choose "Is Lesser Than:" and type "1972."
Click OK and your filtered list will appear, ready to be copied and pasted into an e-mail, a word processing document, or whatever. Go to File>Save As to give the filtered table a name.Creating a Database with Excel
If you don’t have MSWorks, all the above can accomplished in Excel. Technically, Excel is a spreadsheet program, but it can be used to create databases similar to those created with MSWorks. However, some of the procedures, such as sorting and filtering, are done differently in Excel.Filtering Data in Excel
The above described filtering options are also available in Excel, but arrived at a little differently. In Works your Fields (columns) have "Headings" that you inserted, such as "FirstName," "LastName," etc. In Excel your Fields (columns) are pre-designated as A, B, C, etc. However, you can insert column headings into Row 1 cells, and later choose "My Sheet Has a Header Row."
Next, go to Data>Filter>AutoFilter, whereupon a little down-arrow will appear in each of your Header cells. Click any of these arrows and you'll see choices of All, Top Ten, and Custom.
For now, choose Custom and you will see a selection of Boolean options like the ones in MSWorks. (We'll discuss "All" and "Top Ten" at another time.) WordPerfect Quattro users will find these options under Tools>QuickFilter.Weeding Out Duplicate Entries
Getting back to Excel's Data>Filter, if you choose "Advanced Filter," you can click on "Unique Records Only," which will automatically weed out any duplicated Records.
For those to whom worksheets are new, Excel, Quattro, and MSWorks Spreadsheet users can alphabetize items in any column by going to Tools>Sort. MSWorks DataBase users would go to Records>Sort Records.
In case you're wondering about the differences between the MSWorks DataBase program and its Spreadsheet application, the latter is designed more for handling complex math calculations, while the former is for cross-referencing various data.
If you would like to receive our newsletter just send an email
This page is supported by advertisers who create and post ads here via Google Adsense.