Friday, February 11, 2011

Creating a Database in Microsoft Access to Management Records

Question:

I would like to develop a database to manage my department records. Do you have any suggestions?




Submitted by: a Client




Response:




This is a question that I have been asked over the years by clients and by students in a class that I taught at a local college. This isn’t an uncommon question from people working in small to medium-sized organizations that do not have a formal records management program. This article will be in three parts.





Part I





One of the questions I would ask would be “Do you need an index? My answer is; “depending on the complexity and/or the type of the records system you have developed”. There are two types of collections:



1. A “direct access system” that 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”.



2. An “indirect filing system” or alpha-numeric system is based on a special filing 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.


I have observed a wide variety of indexing schemes using simple tools like Microsoft Word, Excel, Access, SharePoint and the more formal records management programs developed by commercial firms to manage corporate records. Each (Microsoft Word, Excel, Access, SharePoint, etc.) 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.

For those interested in a more robust document management program the following government web site “http://jitc.fhu.disa.mil/recmgt/index.htm” contains a listing of commercial vendors who have developed document management programs and have become certified under DoD Standard 5015-02. The site is the Joint Interoperability Test Command (JITC) Records Management Application (RMA) organization. The JITC performs testing of RMA products for compliance with Department of Defense (DoD) 5015.02-STD. It is (DoD) policy that only compliant products be acquired by DoD organizations. Products that have been successfully tested are listed in the compliant Product Register. It is noted that there are other products on the market that are not certified that may be acquired to management an organizations business records.

For organizations without the funding resources:
• 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 may have the need for a more complex system or multiple systems to manage their records and are unable to secure funding for one of the professionally developed vendor document management system I recommend evaluating Microsoft Access. If Microsoft Office is the organizations software standard and the agreement includes Microsoft Access as part of the software package, you may have hit a mini jackpot The bigger jackpot would be if the Information Services/Technology Department also had the technical staff with experience working with Access and provide support in developing the program and on-going support for the maintenance of the software.

If the Information Services/Technology staff cannot support you by assisting in the development of a filing system, but is willing to manage the software (maintenance, upgrades, backups, etc.), and you still want to pursue the development of a system, ask yourself 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 or training on Microsoft Access at your local college, your organizations in-house training or purchase a textbook on Access at almost any bookstore to improve your skills.

I purchased a copy of a textbook titled “Microsoft Office Access 2007 Illustrated Complete” by Lisa Friedrichsen, published by Course Technology, Thomson Learning. Being a visual learner I found the illustrations in her books very useful in the development of my program. I also keep a copy of “Microsoft Access 2002; Fast and Easy”, published by Prima Publishing, in my library and use the books extensively when I run into a problem.

In Part II, I will discuss the database design and data fields that I have used for a generic database to manage the business records of an organization.