- 255 Skidoo
- Of Anarchy, Hierarchy, and Monarchy
- Word: Reporting for Duty
- Next Time...
- Tip Sheet
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Of Anarchy, Hierarchy, and Monarchy
Another problem we encountered is that the conversion from Excel to Access created a distinct record for each Who Saw name and the corresponding Scale. I wanted to be able to capture all this information in two fields. I either need to create a hierarchical database, in which each record has subrecords, or keep the database flat (which I call monarchical) and create a separate field for each reviewer.
I choose monarchy. In my case, it means a net addition of only one field because my family has three reviewers. It also means upsetting Access experts, which is how I satisfy my personal appetite for anarchy.
First, I’ll get rid of the extra rows the old fashioned way: I’ll delete them. Select the row you want to zap, and tap the Delete key. Or to delete without selecting, click in the row you want to delete and choose Edit, Delete Record from the main menu. In either case, click Yes to confirm. Repeat this for any other extraneous rows (rows is normal-speak for records). In my case, this leaves just a single data row.
Now, click the Design tool on the Database dialog box’s toolbar. First, I select Who Saw and Scale, shown in Figure 6, and tap the Delete key, saying Yes to the prompt asking me if I’m a complete idiot, er, I mean, asking if I really want to permanently delete the selected fields and any data they contain.
)
Next, I want to add three fields for my family members. New rows are inserted above the current selection. So, I click on the last row and choose Insert, Rows. After doing this two more times, I’m left with the view shown in Figure 7.
)
Into each of the blank Field Name cells, I type the names of my family members, politely putting myself last, of course. Access defaults to Text as the Data Type. Originally, I used asterisks. Using Text would let me do that here, as well. Or, I could have a five-point scale and make it numerical instead. I like the visual feedback of asterisks, so I’ll leave the data type as Text.
However, the General tab shows the Field Size as 50. No movie is that good. So, I change 50 to 5 for each of the three movie watchers. This lets the resulting database file be smaller.
While you’re here, take a look at each of the other fields. Are 255 characters enough for the cast list? If you don’t think so, you might consider changing it to a Memo field. If you do that, don’t forget to zap the @ sign under Format in the General tab so that the whole list will display.
When you’re done, you’ll have something like what’s shown in Figure 8. Click the X to close the Table design form, saying Yes to saving changes.
)
Finally, double-click on the table in the Database dialog box to display the data table. If we really wanted to do this totally correctly, we could design an Access form for entering data (we’ll look at that in part four of this three part series). But it’s not necessary. You can use the data table, just as you’d use an Excel spreadsheet. Enter the reviewer ratings for the movie and then close Access, which conveniently automatically saved the data changes for you.