|
Woodworking is an expensive hobby. It is an expensive business to get into for that matter. Tools and materials cost money, and good tools and materials cost more. Out of a sense of curiosity, spurred on by the desire to inventory my shop for insurance purposes, I decided to come up with a method for keeping track of all those dollars I have spent doing what I love. Perhaps you have thought of doing this too.
When I first began this, there were no nifty programs available on the internet for keeping track of your tools. In fact, the internet was so new that there weren't even many web sites on woodworking at all. My wife, a database administrator, suggested we purchase some database software and build our own. We purchased a copy of Paradox and proceeded to try it out - I hated it. In 1993 we moved to MS Access 1.0 because it had much more functionality. By 1994 I started moving all the records I had collected to the computer, a process that took two years leaving me to catch up by entering all the sales receipts collected since then. In 1997, we migrated to a newer version of Access after which, for a time, the project languished until I took up the cause once again in 2000. I hate to admit it but I never became officially caught up until the Spring of 2002. I have been able to successfully develop and implement a system for keeping the database current since then but, as you can see, this project has been nearly ten years in the making.
In the meantime, there have been attempts at writing inventory software for tools, such as the EasyShop Tool Journal you can download off my own software page, but none of these comes close to a proper database of information over which you have complete control of the format and type of output - as you have with my database. The result has been a complete record of almost every dollar I have spent on tools, lumber, hardware, and other materials. The focus of this article is to explain to you what I have done, and provide you with a skeleton copy of this database, which you can download for free for your own personal use.
Before delving into the less interesting technical details of the project, I am sure you are asking yourself why I am doing this. It is important to me to keep track of all the acquisitions I have made over the years for insurance purposes primarily. Heaven forbid I ever have to make a claim, I have a record of everything I have in the woodshop, when I bought it, and how much it is worth. Furthermore it is a downright interesting thing to look at after you have been keeping records as long as I have. I have been working wood for over twenty years, but it wasn't until 1987 that I decided to start keeping and storing every sales receipt that pertained to woodworking. As of the time of writing, that means that I have 16 complete years of data, in addition to the few surviving records I have from before 1987. I have to tell you, looking back at the money I spent over the years can be extremely interesting. Now I have piece of mind, and a database of information that provides me with a certain amount of entertainment too. These are reason enough aside from the day to day assistance my records give me in deciding when to reorder sandpaper, hardware and other consumable items that I keep on hand in the shop. In keeping with the ideals and purpose of this web site it seems only natural that any of my good readers who might be interested in this database for their own use should get a chance to download it, use it, and modify it for their own use. I only ask that if you come up with some really excellent idea you let me know as I want to try it out too!
So what I am actually providing here? Along side this article explaining the database, its history and format, you get a copy of the database itself - not a copy of MS Access - but a copy of the database file I have created. It was originally written in an older release of MS Access, but the current version is in Access 97, as that is what I am presently running on my machine. Migrating the database to other software types or versions shouldn't be that hard as the original migration from Access 1.0 to Access 97 was not at all that difficult. Included in the database is a selection of a few records from my own database so that you will have example of my data entry format, which you will find later is important. Please keep in mind that this is not really a tutorial. I don't consider myself equipped to teach you how to use Access - there are a plethora of web sites and books available that can teach you more than I can. In fact, the best advice in that area I can give is to find one book that words the basics of Access the way you best understand things, then experiment. If you mess it all up you can always go to your backup (which I strongly recommend you make on a regular basis) or download the original again from this site. Having said all that, please don't inundate me with email on databases. I am providing this for your use and experimentation - without support.
In addition to the database itself I use a few other things to keep track of everything. First, I have developed a form, which you can also download here or on the software page, that I use to keep track of new arrivals. I realized early on that I wasn't going to be able to, or even want to, go to the computer and type in new acquisitions every time I went tool or hardware shopping. I developed an update form on which I write the particulars, then I update the database once I have filled up the form. Second I keep a handbook, as I call it. It is basically a thin binder in which I keep a hard copy of my latest list from my main equipment table. I also keep a list of my books in it from my library table, a list of suppliers' addresses and phone numbers from my supplier table, and some shop layouts on which I sketch layout changes I make from time to time. Third, I video tape a walk through of my shop where I open every door and drawer, and check out every nook and cranny so there is a pictorial record that can be matched up with the list in my database. It doesn't have to be an Oscar winning performance, just enough to let the insurance people get a good look so there is no doubt as to whether you actually owned that torque wrench or not. Perhaps one day, I will add a field to my main equipment table in which I can place digital photographs, eliminating the need for a video walk though. I have been at this so long however that, for now, I believe I will just enjoy things as they are. Maybe you will be the one to take this project that one step further?
Before I get into the specifics of the software, I need to cover the options available for opening it and using it. If you are unfamiliar with the use of Access 97, I have devised a system of menus that take you through the features I have built into the database file. These menus are relatively easy to use and I have tried to name everything so that no confusion can result over what the options will do for you. If you are familiar with the operation of Access 97, or are an expert and want to make your own changes, you will probably want to turn off the menus and use it as is. I personally do not use the menus - I made them for you. I just use the standard Access windows to move around and do what I need to do. If you are new to Access please don’t be afraid to try things out. It is a relatively easy piece of software to use and once you can free yourself of the menus the possibilities, in terms of performing your own queries on the data and creating reports, are endless. There is a great deal to be learned from your records and spending habits. That's half of the fun.
A temporary bypass of the switchboard menus can be accomplished in one of two ways. First, you could minimize the switchboard by clicking the minimize button in the upper right corner of the window, then click on the "woodshop database: database" window and continue. Second, You can go to the Window menu at the top and select from it the "woodshop database: database" selection near the bottom of the popup. Please remember that the next time you open the program you will default back to the menus, and that if you change the name of the database, the names I have provided may also change.
You can also deactivate or reactivate the menus, meaning that the next time you start up the software you will either not have access to the menus, or reactivate access to the menus, respectively. Those of you who are more adept at the use of Access will probably choose this option to shut down the menu system. To change the menus for the next startup you select Tools then "Startup…" from the menu bar, then under "Display Form" you select either "none" to shut down the menus, or "Switchboard" to reactivate the menus.
As you will discover shortly, I have only one field for describing each tool in the main equipment table, other than it's category. I did this on purpose. In the beginning I experimented with a number of ways to separate the attributes of a tool type and name into various fields, all without success. The result was to use only one field and to adopt a standard method of describing tools so that sorting, searching, and querying would still work effectively. The system I adopted was to describe the tool from general to specific, separating each attribute with a comma, and capitalizing each word. It is highly important to the success of your database, and your satisfaction with it, that you adhere to some standard of data entry. It needn't be mine, but you have to enter the data for every item the same way every time, all the time. Here is an example.
|
Description |
|
Blade, Circular Saw, 10", 1/8" Kerf, 40T, Carbide, Forrest Woodworker II |
|
Paint, Spray, Enamel, Gloss, Antique White, Rust-Oleum |
|
Sandpaper, 0120X, Al-Ox, Stearated, 9x12, Klingspor |
|
Plane, Shoulder, Large, No. 73, Lie-Nielsen |
In the above description, for each item, there is a progression evident in the description from the most general attribute to the most specific. I usually place the manufacturer last, which brings me to another point. Information, like manufacturer's names, should be written the same every time. If you have a number of power tools made by Porter-Cable, then every time Porter-Cable is written in the description line it should be identical, whether you choose to write Porter-Cable, porter cable, PC, P-C, or what have you. The most important attribute, when you actually try to find something on the list, will be the first, most general tool description. Again, be consistent and try to go with the best, most standard name for the tool you can think of.
The actual structure of the database file is comprised of four types of objects - table, queries, forms, and reports. Access also supports macros and modules but I like to keep things simple so I don’t use any of them. The tables are the objects that store the information in the format outlined by the table design - more on this in a minute. Queries are little snippets of SQL that search through the tables, extract certain information, and generate a temporary table containing the information you requested. Queries are the true power of the database. They allow you to come up with a means of asking questions of the data and extracting the answer in a format that is used in either forms or reports. Forms are templates that are defined to allow you to look at the data without looking at the raw table. Actually I prefer to look at the raw table, but this can be confusing sometimes, especially for someone who is completely new to databases. The forms also provide a template for entering new data and editing the data you have. Reports are the printed documents you can produce based on the tables themselves, or queries. They are the interface by which most people understand and interpret the data in the database file.
The woodshop database consists of four tables and a handful of queries, forms and reports based on those tables. The queries select out certain data based on whether or not it is consumable, the category it belongs in, or for some other reason. There are forms to allow you to look at the raw data, edit the tables, and enter new data. The reports provide you with print or screen output listing what is available in the inventory, with some totals to show you where your money has been spent. More detail than that would turn this page into a tome and bore you to tears, as if you aren't already. I should, however, tell you a little about the tables involved as they are the principal component of the database file.
Of the four tables included, the equipment table is the primary table. This is the table that stores all of the information about your tools. The library table stores all of the information about your books and magazines while the suppliers table is a smaller table that is there to help you keep track of contact information for your favorite stores. The switchboard table is only there to run the menu system and has nothing to do with your personal records. I should point out that I don’t use the library table. I use a variant of it that is specifically designed to place the data in a format that can be used by the CGI scripts that run my online library database and search engine. What you have in this file is a stripped down version and I can't really vouch for it as I have never used it in this form. The form I do use would be of absolutely no use to you as the output from it is only readable by the Perl script that runs my online library pages.
The equipment table is made up of twelve fields. There is an ID field that holds a automatically generated number that is of use only to the computer. If you use the database without the menus, those numbers do become familiar to you and are of help finding records when scrolling through the raw table data. Next is the consumable field which is just a check box field to let you know whether you have decided the item is consumable or not. This helps when printing out lists of screws and things if you are looking to see what you need to order soon. The categories in the category field are those that have worked best for me. You are welcome to change them to suit your own situation but I should warn you that embedded in the table design for the equipment table is the code that drives the popup list you see when entering data. You should be fairly familiar with Access before you start making those kinds of changes. The next field is the description, which I have already covered. The quantity field is a number field and following it is the field with the units of measure. The units field is also driven by code embedded in the equipment table which populates the popup list you see when entering data. The purchase price field, for my use, is the price I paid for the item, or the price it sold for regularly at the time it was given to me. This is followed by the purchased date, indicating when I acquired the item, and the consumed date - the date by which I had used it up, sold it, or declared it written off and no longer part of my inventory. The serial number is a text field to allow for all variations of serial numbers you encounter. The gift field is a check box field indicating 'yes' the item was a gift, or 'no' it was not. I use this to help when I calculate things like how much I have spent versus how much others have spent on me. I do that for fun and it is interesting. Finally, the entry date field was a recent addition. It is automatically filled by the computer and allows you to sort out the tools and materials purchased since a particular date. I added this field because it is costly, in terms of both paper and ink, to reprint the entire equipment table report every time you go on a shopping spree. Instead, I print out a fresh copy of everything every 6 months to a year. In the meantime I just search the table for the records I added since the last printing, and print out the results to complement the full list in my handbook.
The queries included are used to run the reports mainly so you won't have to change them. Feel free to create your own however, but if you rename or change the queries that came with the file, you have to realize there will be repercussions when you start viewing the reports. The "Printout for Handbook - List entries since" query is meant to be run from the queries window and the resulting table simply printed and entered into the handbook. It was a last minute realization that led me to this oversight as there are no menu entries, I believe, that run this query.
The forms can all be run with or without the menus and their function should be fairly self explanatory. Remember that you have to adhere to some kind of data entry standards and it helps to write the standard out and put it in your handbook. That way, if you leave it go for a few months, you can start entering data again without inconsistencies.
There are a number of reports that do provide various types of output. I haven't fixed all of the formatting issues in them as I really only use the "View Equipment Table - List for Handbook", the supplier list, and the unconsumed consumables report (yes, I invented the term "unconsumed" - I couldn't think of another word). The other reports I have used in the past, but no longer. I left them just in case you might get use out of them.
I have rambled on long enough about the history , structure and function of this little project. I cannot emphasize enough the importance of this kind of data to any insurance claim or the entertainment value of the finished project, but you may want to keep it from your significant other. If the results become general knowledge you may have some explaining to do. I'm grinning when I say that. I am fortunate that my wife is very understanding, and has hobbies more expensive than mine. Don’t suggest this project to a weaver/spinner. You are in for a shock! In any event, I hope you benefit from the ten years I spent tinkering with this and have as much fun with it as I did. Happy woodworking!
|
