Sunday, March 1, 2009

Science Fiction Book Database

I developed a database to track my books, so that I would not end-up buying duplicate books. Eventually, I expanded it to list all books/stories/magazines that an author appeared in my database. Currently the database has just over 600 entries in it.

This is one of those perpetual projects that you dabble in every once in a while. Given the emergence of the Internet and reference sites such as Wikipedia , The Internet Speculative Fiction Database, and SCIFIPEDIA there is nothing magical about the database other than as a continued Access learning experience. As time permits, I will continue to dabble with it.

Below is the opening screen. (Click on the image, you will get a better quality image.)
Below is the screen shot for all the stories appearing in "Space Opera".

Below is a screen shot of the June 1953 edition of Astounding.

Here is the screen shot of the "secret ingredient", a display of all stories/books that the author appears in. Actually just the stories entered into the database since not all stories have been entered. Here you can see that Bertram Chandler appeared in November 1978 issue of "Amazing" and the August 1975 issue of "Galaxy". This is a feature that does not seem to exist in other databases, which I find quite surprising. I also have found this to be frustrating in terms of being able to locate stories when visiting bookstores.

For the the technological groupies - below is the SQL code that makes the screen shot above possible to display all the books/stories/magazines that an author appears in. I hope that others can use this as a means of expanding the capabilities of any existing database. The lack of this capability is a major shortcoming. This is what computers and databases are supposed to do!!!
----------------------------------------------------------------------------------------
SELECT Null AS [STORY],[TITLE] AS [BOOK],[SFAUTHORID] AS [AUTHORID],[BOOKID], NULL as [TYPE1]
FROM QALPHA WHERE SFAUTHORID=FIELD34

UNION ALL SELECT [STORY],[TITLE] AS [BOOK],[SFAUTHORID] AS [AUTHORID],[BOOKID], NULL as [TYPE1]
FROM QCOMPILATION WHERE SFAUTHORID=FIELD34
ORDER BY [AUTHORID], [STORY],[BOOK]

UNION ALL SELECT [STORY],[TITLE] AS [BOOK],[STAUTHORID] AS [AUTHORID],[BOOKID], NULL as [TYPE1]
FROM QANTHOLOGY WHERE STAUTHORID=FIELD34
ORDER BY [AUTHORID], [STORY], [BOOK]

UNION ALL SELECT [MSTORY],[MAGAZINE]&": "&[MMONTH]&" " & [MYEAR] AS [BOOK],[MSAUTHORID] AS [AUTHORID],[ISSUEID], "M" as [TYPE1]
FROM QMAGSTORYLIST WHERE MSAUTHORID=FIELD34
ORDER BY [AUTHORID], [STORY], [BOOK];
------------------------------------------------------------------------------------------------
If anyone is interested in receiving a copy, you can let me know by sending your mailing address to ortaias@hotmail.com. The database runs under Microsoft Access 2000. It is also too big to fit on a CD so it has to be sent on a DVD. The database currently contains just over 600 books. Its free and if there are not too many requests (such as less than 10) I wouldn't mind footing the bill for sending it out in the US. Of course, I would encourage a postage paid return envelope that is padded and can hold a DVD.

Now for the disclaimer, it ain't perfect! If you don't know Access, you may have some problems. I've tried to make it idiot proof, but as one saying goes idiots are very ingenious.

As a closing note, the ability to integrate books and magazines in a manner that allows one to see all entries for an author would be a major accomplishment. It would be interesting to pursue such a project and to have it posted on the web.

5 comments:

Sonya said...

I've come across a couple databases on the internet for cataloging books, but most either seem way too simple (and static) or way too complicated (made for archivers or something). I haven't found anything in the happy medium genre like yours. There's an interesting product where you can scan books in and it catalogs them for you but it looked very internet based. The benefit was that it pulled up ISBN info.
Nothing so far that I can rip off and make my own. *sigh*

Steve R. said...

The ability to display what is inside a book is there. I find it surprising that, in this computerized world that, this concept has not caught on. Especially for those who are collectors/followers of specific genre such as Science Fiction, Mystery, Horror, Romance etc.

Simple Peasant Girl said...

You have always fascinated me with your knowledge of Access. Maybe one day I will learn it myself. Along with Sonya, I *sigh*.

Steve R. said...

Compared to the people who really know Access, I know nothing. If you seek to learn Access, it proves that you are a masochist.

If you are starting from scratch, it would be best to learn a unix/linux based database. Eventually, I hope to move in that direction myself.

Jaycephus said...

I've been thinking about a SciFi or SpecFi 'database' or an encylopaedia for a long time, but one primarily targeting concepts, their origin, and usages and not the stories, books, or authors. BUT since the database or wiki should contain related listings of as many of the stories and their authors as possible, it would also serve as a story, publication, and author database if built correctly.