The purpose of this database is two-fold. First, it is designed as a way to keep a quick reference for every Joe figure ever produced in the world on your hard drive. Second, it provides a quick and easy way for collectors to keep track of their collections and quickly see what they have, what they are missing and what is available.

Download the Database - The file is ~200k and is a .zip file. Inside are Microsoft Word instructions and .mdb that is the database itself. The database is current through all figures released to retail as of 5/30/08. Currently, there are over 1800 figures listed in the database.

The system assumes you have at least some knowledge of Access and how it works. You input data through forms as those help regulate the keys and keep information properly related. You search using the Access search function. I left some sample data in the Other_Material table and the Character and Figure tables are populated with some data. I have entered (I think!) every American and foreign figure produced through 2008. You will also see about 100 unreleased figures listed. That is by no means comprehensive, but offers a start. If you want to make improvements to the system please do so. Most of the coding was taken from various free coding sites and Access help sites out there. As such, it is simplistic and clunky in places. But, it seems to work (for the most part) and helped me keep a handle on my collection for a long time.

HIERARCHY

The System is designed with a set hierarchy that allows for easy access to information. The database is normalized to reduce file size and relies mostly on numerical keys to relate information. The standard structure is as follows:

Character – The Character is the highest level of sorting. The Character is defined by the Master Line in which he was introduced. The Master Line concept was designed to group G.I. Joe figures from all over the world as well as combine vintage and new Star Wars figures. This is the highest level that allows for more detailed breakouts (e.g. by country for Joes) at lower levels of information. The Character is the descriptive value that differentiates one character from another. (Under the Character of BAT, you would see figures that include all versions of American BATs, the Brazilian Roboid and the Funskool BAT.)

Figures – The Figure level is the second highest method of sorting. The Figure relates to the Character by the character_key. The Figure level contains information such as: Figure Name, Version Number (I have used a combination of YoJoe.com and theJoeIndex.com figure version numbers in these fields, but they can be changed.), a description (for Funskool, Estrela, color differentiations, etc.), the year in which the figure was 1st released, a Line (This is a drop down that contains a number of lines and provides the second level of detail. Reports that show all figures from Brazil, for example, would use this field.), Type (this shows if the figure was Full-Production, a prototype, etc.) and a Variant field that marks if the figure has any variants (These would be stored in the variant table, though.).

Under the Figure tab, you will see several tabs marked: Figure, Accessories, Carded, Variants and My Collection. We will discuss Variants and My Collection a bit later. The Figure and Accessories tab, though, only have one visible field. This is the Image Location field. In this field, you should store the local path to pictures stored on your hard drive. Once you put this path in, the image will appear inline on the screen. This gives you a visual reference to the figure and its accessories. (Note, my intention was for the accessory table to be a many to one relationship with the figure table. This would have allowed you to store pictures of each individual accessory. However, setting that up was just too daunting for the limited return it would have gotten in this set up. As such, the field that is there is designed to hold one picture of all of a figure’s accessories. It’s a lazy way out, but I really didn’t want to catalog each of the thousands of individual accessories in the Joe line.) The idea behind these fields is that the user can then download all the images from various online sources and store them on their hard drive and then link those photos to the records in the database. That way, you always have a visual reference to a figure and his accessories without having to be online.

The final tab is the Additional Info tab. This stores information such as the figure’s affiliation, whether or not it’s an army builder, the figure’s specialty and whether the figure is old or new sculpt. With these fields, you could run reports that show the number of new sculpt army builders in your collection vs. old sculpt army builders. Or, you could find all the Joe’s telecommunications specialists for a mission you are putting together.

Currently, there are over 1800 figures listed in the database. This includes all figures through Wave 4 2008 of the 25th Anniversary collection.

The other information that is stored at the same level as the Figure is the Related Material. You will see that on the 2nd tab. Related material is a many to one relationship with the character. It is designed to hold images and information about a character that does not pertain to a specific figure version. As an example, you can purchase the unproduced Wal Mart exclusive parachute figures from Asia. As these aren’t official versions of a character, they really don’t fit in the figure table. (You are free to add whatever you want to the figure table, though.) So, they would be listed under related material. Related Material has a field for Name, Description, Source of the item, a Cross Reference field (this is intended to mark other items that were found at the same time, etc. At this time, this field doesn’t do much!) and an Image Location field. As with other image location fields in the database, you put in the path to the image on your local hard drive and it appears in the system on the screen.

Notes – Notes can be attached to related material, purchases, sales or trades. The system categorizes the notes based on the form used to enter them. Notes can be up to 64,000 characters and can be used to hold large amounts of information that relates to the material at hand.

Attachments – Attachments can be attached to related material, purchases, sales or trades. The system categorizes the attachments based on the form used to enter them. Attachments are outside files like Word documents, PDFs, email files, screen prints of Ebay auctions, or any other binary format file. They are attached and saved in the database so if you delete them from your hard drive, the attachments will be saved. Be careful as large attachments will slow down your database and could crash it if the size of the database exceeds 2GB.

The lowest level of information stored in the database is the Variants section. This is a place where you can store information about minor variants in the system. It contains a Description field, a Notes field, and Image Location field and a Variant Type radio box that helps you denote the type of variant. This section is intended to be used for minor paint or accessory variants that have become common in the newly released Joe figures.

The Most important tab in this section is the My Collection tab. This is where you store information about your collection. To add a figure, you simply click the Add To My Collection button. From there, you tab through the rest of the fields to input your information. The fields are as follows:

Accessories – type in all the accessories you have for this version of the figure

Condition – type in the condition of the figure

Missing Accessories – type in which accessories you are missing for the figure (will be useful in later reports)

Date Acquired – type in date you acquired figure

Price – type in price you paid for figure

Complete – check box if figure is complete (will generate a report that will tell you how many complete figures you have versus how many incomplete)

Filecard – check box if you have the filecard for this figure

Carded – check box if the figure is carded (will use this field to generate report showing all your carded figs and all your loose figs but also give you a total number of figures)

Code – The code field is a way for you to track purchases. Long ago, I devised this also as an inventory system for a dealer. As such, you would be able to record the price of a lot you purchased and then use this code field to record that. Then, down the road, you would be able to run reports against a sales table (that has yet to be built) that would tell you if you made money on particular purchases. At this point, though, the field is most useful as a way to track items purchased at the same time.

Image Location – type in the local path to your image of the figure and it will appear on the screen

Archived – This is a field for my personal use. I keep want to know if I have at least one, mint, complete with filecard version of a figure. When I get one, I mark it archived which means it is bagged and boxed away and not for use. You may or may not have a need for this field depending upon your collection strategy.

Available for Trade – Check this field is the figure is available for trade (a report will then quickly generate a trade list and you won’t have to keep a separate list with your trade fodder.)

Removed – Check this box if you’ve removed the figure from your collection. If you sell or trade off a figure, you can check this box. The reports that tell you information about your current collection will skip any figures that are checked here. But, if you ever wanted to see how many of a certain figure you have sold or traded away, you could use this. The philosophy behind this is that you don’t delete any data, you simply remove it from view.

Trade – This is a field that goes with the Code field. Originally, the intent was that dealers who trade items would not be able to account for them on the sales side. This field would mark removed figures as traded and would help track the trades and relate them to items in the sales table. As that functionality is not yet created, this field is of little use.

Collection Transactions You may keep detailed records of your collection transactions in this table. This will keep track of how much you’ve spent, how much you’re sold and what trades you have going. You can keep track of what items you are waiting to receive, who’s paid, when you paid and a multitude of other information.

Purchases – keeps track of purchases

Sales – keeps track of sales

Trades – keeps track of trades

QUERIES

There are a number of queries already in the system. They are labeled in a manner that makes it intuitive to figure out their purpose. There are also a few reports in the system that can be accessed from the main menu. Feel free to create more reports as you need them.

FORMS

The main form is the Character form. It opens automatically when you enter the system. All other forms are subforms of the Character form.

REPORTS

There are a few canned reports in the system that are accessible from the reports menu. These show some basic information. You can create much more detailed reports using the report wizard and advanced queries.

Download the Database - The file is ~200k and is a .zip file. Inside are Microsoft Word instructions and .mdb that is the database itself.

This document is © 2008 by Michael R. Taber and may not be reproduced in any form or capacity without the consent of the author. The author is not affiliated with Hasbro, Inc. or Microsoft, Inc. in any capacity. This document is provided for informational purposes only. Deep linking to this document from another web site, message board, newsgroup posting, or other means is permitted and all excerpts or quotes from this document must be accompanied by a link to the original source.