Satisfaction

[stoutfellow]

I'm in the process of subcataloging my library. All my books - at least, the ones at home - are in the database; now I'm recording the discrete units within each book - individual stories, poems, articles, etc. I'm attaching each one to the book (or books!) it is to be found in and to the author (or authors!), sorting them into broad types (encyclopedia article, letter, sermon, short poem, long poem, etc.), and adding keywords and other notes.

There is something very satisfying about putting together an Access form that automates or at least eases work that, at the table level, is extremely tedious. I'm still not quite happy with the forms I've designed, but my remaining complaints are largely aesthetic - they get the job done, and that's the main thing.

Good forms or not, though, this is going to involve months of intermittent labor.

Comments

[jrittenhouse.livejournal.com]

So describe your data system, please...

[stoutfellow.livejournal.com]

You want details? OK. All of this is done in Microsoft Access, the 2002 version I think.

[Note: All tables have an AutoNumber field named "ID", which serves to index that table.]

At present there are three fundamental tables. The Authors table has an ID field and a "Name" field. The Volumes table has fields "ID", "Title", "Creator", "Type", "Reference", and "Details". "Creator" identifies the author (or comma-separated authors) or principal editor; "Type" is one of "Author", "Multiauthor", or "Editor"; "Reference" is a yes/no field. (In my Britannica, for instance, the Propedia and the Micropedia volumes are "yes", and the Macropedia volumes are "no". Anything consisting mostly of tables or charts, or lots and lots of very short articles, or the like gets "yes".) "Details" is also yes/no, indicating whether I've broken the volume into its component parts. (I'll probably discard that field.) The Works table has fields "ID", "Title", "Type", "Keywords", "Notes", and "Read?". "Type" is, as I mentioned, a rough classification.

There are two auxiliary tables, Authorship and Locations. An Authorship record gives the IDs of an author and a work; a Locations record gives the IDs of a work and a volume. I use these so that I can group the works of a single author, or note the presence of the same work in more than one place.

I'm thinking of adding another fundamental table of keywords, with "ID", "Title", and "Parent" fields; if I do that, I'll also add an auxiliary table linking works with keywords.

I have two queries, "Authorship Query" and "Locations Query"; each links the corresponding auxiliary table with the appropriate fundamental tables, adding in names and/or titles.

The Volumes form displays all of the fields from the table, plus a "Contents" subform, pulling in information from the Locations Query. The Works form displays the fields from the table except "Read" (I should add that) plus a Listbox with the options for "Type" (selecting from the Listbox sets the value in "Type") and "Author" and "Location" subforms. The Authors form shows "ID", "Name", and a "Works" subform. I also have an Authorship form, which lets me link works to authors. [At the moment, I can't get the various subforms to allow me to create new fundamental records directly.] I will probably add a Locations form and perhaps a Keywords form, with similar functions.

[threeringedmoon.livejournal.com]

But think how much fun you will have revisiting old favorites.