Question:
I would like to develop an indexing system to use as a finding aid for retrieval of my department records. Do you have any suggestions?
Submitted by: a Client
Response:
This is not an uncommon question from personnel working in small to medium-sized organizations, and in particular within organizations without a formal records management program.
I also remember that this question was asked by students in the File and Database Management class that I taught at a local community college.
One of the first things you need to ask yourself is; “Do I need an index?”. The answer is; “depending on the complexity and/or the type of the records system you have developed”. Some collections may be accessed without the use of an index. A “direct access system” allows one to go directly to the files without the need of an index. Geographical and client records filed in alphabetical order are an example of a “direct access system”. An “indirect filing system” or alpha-numeric system is based on a special code. In either case, an index of some sort may be necessary locate and retrieve files. Photograph files and engineering drawing systems are examples of records that may require an index.
Over the years, I have seen a wide variety of indexing schemes using Microsoft Word, Excel, Access and formal records management programs developed by commercial firms to manage corporate records. Each (Word, Excel, Access and their counterparts in Lotus SmartSuite – Lotus 1-2-3, Word Pro, Approach) may be utilized to develop an indexing scheme. It all depends on what you expect of the index; i.e., the amount and/or complexity of detail required and the retrieval needs of the users of the records.
Let’s consider an example: you may easily develop a table in Microsoft Word that has a file code or numbering scheme, titles, authors and dates. You may use the Edit/Find button to locate records within the search parameter. Microsoft Word could be a simple solution for a small collection.
Microsoft Excel may also be used quite effectively in managing collections of files. It also has some other attributes for indexing, sorting, data entry input and printing of indexes that are lacking in Microsoft Word.
For organizations that have a need for a more complex system or multiple systems to manage their records and cannot obtain funding to purchase a full-fledged document management system I recommend evaluating Microsoft Access. In a perfect world, in my opinion, the organization would be using Microsoft Office as their company standard. If Microsoft Office is the standard and the agreement includes Microsoft Access as part of the software package, you may have hit a mini jackpot. The big jackpot; the Information Services Department also has technical staff with experience working with Access and can support your requirements!
If Information Services staff cannot support you by assisting in the development of a filing system, but is willing to administer the software (maintenance, upgrades, backups, etc., the technical stuff, but you still want to pursue the development of a system, ask the following questions:
1. Do I have the computer skills to proceed with this project?
2. Do I have the time to develop a system?
If the answer is yes to both questions, you are on your way to starting the development of a system.
If the answer to question #1 above was “no”, do you have the time and patience to learn? If yes, proceed. If not, you might want to back off and try using Word or Excel.
If the answer to question #2 above was “no”, could you take the time to develop an indexing system? If not, you might also want to back off and try using Word or Excel.
If you still want to proceed but feel your computer skills may be lacking you might consider attending a course on Microsoft Access at your local college or purchase a textbook on Access at almost any bookstore to improve your skills.
I purchased a copy of a textbook titled “Microsoft Access 2000: Illustrated Series” by Lisa Friedrichsen (is this spelled right?), published by Course Technology, Thomson Learning, which is a Illustrated Introductory course in Microsoft Access. If you are able to complete the first seven chapters of this textbook, you should understand the basics of Access and develop a database.
I also purchased “Microsoft Access 2002; Fast and Easy”, published by Prima Publishing, 2001, at a local bookstore. Although the textbook is for Access 2000, the basics are still the same and I use it this book extensively when I run into a problem.
In my next article, I will discuss the database design and data fields selected for a generic database (to create an indexing system) .
Part II
In the previous article, we reviewed some requirements that would be needed to develop a simple indexing system using Microsoft Access. The basic requirements are: (1) computer skills and (2) time to develop a system. In Part II, we will discuss designing a simple database that may be used to manage a collection of records, on-site and off-site.
Again, I highly recommend purchasing document management software if at all possible in lieu of developing one in-house. A professionally designed document management system is a very complex program, developed by experts in developing software who are employed by companies that are in the business of providing this type of service.
The first and most important goal in developing a database/finding aid is identifying the pieces of information you want to capture. It cannot be over-emphasized how critical this phase of the project can be to the successful implementation and use of the database. You need to consider the following:
What is the purpose of the database?
Determine the types of reports you want to produce.
Write out the field names and what data type the record will be (date, text, currency, number, memo, etc.,). Play particular attention and place the fields in a logical flow for data entry. Example: If your entering information from a form, you want to data entry in the same flow as the form entries.
Remember! Keep it simple and easy to enter data enter and manage the database. Do not be tempted to add additional of fields in your table just in case.
The database we will develop will be a simple table in Microsoft Access that will allow the user to develop the basic information to:
Produce reports (listings) of records in the database (by department, function, file code)
Print file folder labels (some restrictions on this ability)
Produce destruction notices.
Produce transfer notices for off-site storage facilities or your state archives by modifying or adding fields to the database
Searches by keyword
Here’s an example of a table in Access design mode with the following fields and data types.
Field Name Data Type Description
Rcd_Id AutoNumber
Rcd_FileCode Text Alpha/Numeric Field
Rcd_Title1 Text
Rcd_Title2 Text
Rcd_Title3 Text
Rcd_DateorSeqFr Date/Time Beginning Date/Sequence Range *
Rcd_DateorSeqTo Date/Time Ending Date/Sequence Range *
Rcd_Memo Memo Memo Field for expanded information
Rcd_Atch Ole Object Provide capability to attached pictures, documents, spreadsheets, etc.,
Rcd_Dept Text Department/Function Owner
Rcd_SysDt Date/Time Date entered into the system. Used for quality control and label creation.
Rcd_Status Text Records Status (Active, Inactive, Transferred, State Archives, etc.,
Rcd_TransDt Date/Time Date transferred to another location (Archives, Storage, etc.,)
Rcd_TransBC-Box Text Barcode Number or Box Number
Rcd_RevDt Date/Time Records Review Date of Inactive Records
Rcd_DestDt Date/Time Date Record Destroyed
* You may want to create a separate field for each field, i.e, one for dates and one for sequence ranges.
Again, I am assuming that you have a basic idea of the definition of a field and data type. Simply stated they are: (1) Field Name: The name assigned to each field in a table, and (2) Data Type: the type of information that will be entered into the field.
If you have completed the table, exit and save the table, then open the table and try entering data into the table to see if you have the needed fields to effectively manage your records. Experiment with some of the capabilities of Microsoft Access and create forms, reports, queries using the Wizard function.
You can also duplicate (copy/paste) and change the field names from Rcd_*** to Box_*** to manage boxes stored in the off-site and on-site storage facilities. . . If you noticed, I start the field name with a specific identifier (Rec_*) to denote the table I am working with, for example; Rec_Status could be changed to Box_Status. At a later time you can learn to create a query to join the two tables to provide information for reports, etc.
Following the guides of the books suggested in the previous article, you should be able to design a simple database as your document management system and provide you with a finding aid for search and retrieval of your business records.
Part III
In the previous articles, we reviewed some of the requirements needed to develop a simple indexing system and created a table in Access design mode with the fields and data types to manage a collection of records, on-site and off-site using Microsoft Access.
Again, I highly recommend purchasing document management software if at all possible in lieu of developing one in-house. A professionally designed document management system is a very complex program, developed by experts in developing software who are employed by companies that are in the business of providing this type of service.
In this article, we will show an example of simple data entry form and reports that utilized the query programs in Microsoft Access. If you are not a familiar with Microsoft Access, recommend you use purchase the books suggested in the previous article. They should provide you with the tools necessary to develop the examples below and other more complex queries, forms and reports.
The example below is a data entry form developed to manage stored boxes. It has three additional icons at the bottom for specific functions to enhance the usability of the form. The first icon at the bottom left of the form is a duplication icon that allows the user to add a record, duplicate it, and only change entries that are different. The second icon to the right of the duplication icon is for search, and the STOP icon is used to exit the form.
Example 1
It contains the same data as used by the client’s commercial vendor for transfer of boxes to their storage facility. You are probably asking your self, why not use the vendors form? There is a simple answer. I hate to have to re-key anything. Once you have entered the box data into your system, you can use the information to create and print the information in a format acceptable to the vendor.
Using Microsoft Access query wizard the user should be able extract specific data that will provide the information necessary for creation of reports for management of the box collection. Some examples are:
Extract and print the box database in barcode number (shelf location) or alpha order.
Extract and print the box database information by Department or File Code.
Extract and print reports for annual reviews for destruction.
Create search and print queries for different type of records within the database for clients.
Extract and print annual projection of storage usage based on current box storage requirements.
Charts can be designed from the data in Access or exported to Microsoft Excel.
All reports can be exported to Microsoft Word and/or the Microsoft Word merge program.
Creation of Certificates of Destruction for boxes destroyed.
Advanced user can design macros to send reports via e-mail.
The number of reports that can be designed and published is limited to the imagination and experience of the system user.
Part III.
Response:
In the previous articles, we reviewed some of the requirements needed to develop a simple indexing system and created a table in Access design mode with the fields and data types to manage a collection of records, on-site and off-site using Microsoft Access.
Again, I highly recommend purchasing document management software if at all possible in lieu of developing one in-house. A professionally designed document management system is a very complex program, developed by experts in developing software who are employed by companies that are in the business of providing this type of service.
In this article, we will show an example of simple data entry form and reports that utilized the query programs in Microsoft Access. If you are not a familiar with Microsoft Access, recommend you use purchase the books suggested in the previous article. They should provide you with the tools necessary to develop the examples below and other more complex queries, forms and reports.
The example below is a data entry form developed to manage stored boxes. It has three additional icons at the bottom for specific functions to enhance the usability of the form. The first icon at the bottom left of the form is a duplication icon, that allows the user to add a record, duplicate it, and only change entries that are different. The second icon to the right of the duplication icon is for search, and the STOP icon is used to exit the form.
Example 1
It contains the same data as used by the client’s commercial vendor for transfer of boxes to their storage facility. You are probably asking your self, why not use the vendors form? There is a simple answer. I hate to have to re-key anything. Once you have entered the box data into your system, you can use the information to create and print the information in a format acceptable to the vendor.
Using Microsoft Access query wizard the user should be able extract specific data that will provide the information necessary for creation of reports for management of the box collection. Some examples are:
Extract and print the box database in barcode number (shelf location) or alpha order.
Extract and print the box database information by Department or File Code.
Extract and print reports for annual reviews for destruction.
Create search and print queries for different type of records within the database for clients.
Extract and print annual projection of storage usage based on current box storage requirements.
Charts can be designed from the data in Access or exported to Microsoft Excel.
All reports can be exported to Microsoft Word and/or the Microsoft Word merge program.
Creation of Certificates of Destruction for boxes destroyed.
Advanced user can design macros to send reports via e-mail.
The number of reports that can be designed and published is limited to the imagination and experience of the system user.
