Part II –
Creating a Database with Access
In
the previous article, we reviewed some of the 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, in office and off-site.
However,
I still recommend the purchase of document management software in lieu of
developing an in-house system. While
your organization may have a qualified information management professional with
a Microsoft Access background, a document management system is a very complex
program, developed by experts employed by companies that are in the business of
providing this type of service.
If
you still want to proceed, the first and most important goal in developing a
database/finding aid is identifying the information you want to capture. I cannot over-emphasize how critical this
phase of the project is if it is to be successfully implemented. You need to consider the following:
§ What is the purpose of
the database?
§ What information do you
want to capture?
§ Make a list of the
field names and what data type the record will be (date, text, currency,
number, memo, etc.). Pay particular
attention and place the fields in a logical flow for data entry. Example:
If you are entering information from a form, you want to design the
fields for data entry in the same flow as the form entries.
§ Determine the types of
reports you want to produce.
The books on Access mentioned in Part I at the end of this
article will help you in the development of the program with examples of data
fields and data types (Numbers, Text, Data/Time, Memos, etc.)
Remember: I try to limit and/or establish only
the basic information needed to manage the document. Do not be tempted to add additional fields in
your table just in case. Keep it simple
if at all possible. I have found in the
past the tendency to capture too much information.
Creating a Table in a
Database
A database
is a collection of information associated with a topic (for example, sales of
products for customers). The smallest
piece of information in a database is called a field, or category of information, such as a customer’s name, city,
state, or telephone number. A key field is a field that contains
unique information for each record. A
group of related fields, such as all demographic information for one customer,
is called a record. In Access, collection of records for a single
subject, such as all the customer records, is called a table 1
The following tables will be created:
·
Table
for file management.
·
Table
to manage boxes in storage
·
Table
to manage retention
File Management Table
The
database table below developed in Microsoft Access will allow the user to
develop the basic information to manage their files to:
§ Produce reports
(listings) of records in the database (by department, function, file code).
§ Print file folder labels.
§ 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.
§ Create
indices.
An
example of a table in Access design mode with fields and data types to capture a
single record of information:
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
|
Text
|
Beginning Date/Sequence Range *
|
Rcd_DateorSeqTo
|
Text
|
Ending Date/Sequence Range *
|
Rcd_Memo
|
Memo
|
Memo Field for expanded information
|
Rcd_Atch
|
Hyperlink
|
Provide capability to attach 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)
|
Rcd_TransDt
|
Date/Time
|
Date transferred to another location
(Archives, Storage)
|
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
|
Figure 1
* You may want to create a separate field for
each field, i.e, one for dates and one for sequence ranges.
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.
While in the Design mode for your record table
you may specify default entries to speed up data entry. Examples:
·
How
you want the date to be entered – 1/25/2011, Jan 25, 2011, 1-25-11.
·
How
you want the telephone number to be entered:
(253)-229-4xxx
·
How
you want the Social Security Number to be entered: 111111111 or 111-11-1111.
If you noticed, I start the field name with a
specific identifier (Rec_*) to denote the table I am working with, for
example: At a later time you can learn
to create a query to join the two tables to provide information for reports,
etc.
You can add additional fields to the table for
specific types of records being created.
For example, you could add a field to denote the record being retained
is a tax record or held under a litigation hold. This could be useful when you are conducting
queries.
Again, I am assuming that your organization has
an established records management program and your records have been
inventoried and a file classification system established as the Rec-FileCode
reflected in the File Management Table (Figure 1) and utilized in both the Box
Management Table (Figure 2) and Retention Management Table (Figure 3) for the
creation of the destruction notice. If
you are going to use the full potential of the three tables, then the file code
is required. Some resources on
conducting an inventory can be found on my blog at askthecrm.blogspot.com.
Note: As
I am not a professional programmer I do not write computer code for the programs
I develop. I utilize only the existing
program wizards provided in Microsoft Access to develop the queries, forms, and
reports.
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 necessary fields to effectively manage your records. Experiment with some of the capabilities of
Microsoft Access and create forms, reports, queries using the Wizard functions
in MS Access. Do the same for each of
the tables that are being developed.
Box Management Table
The box management database table below will
allow the user to data enter information about boxes to be sent to storage:
·
Created
transfer notices for internal and/or external commercial records storage.
·
Create
indices of boxes in storage (all, by department or function).
·
Listings
to be used for records retention reviews.
·
Number
of boxes in storage by user, for possible chargeback storage costs.
·
For
budgeting purposes the number of boxes being sent to storage and destroyed
(annually).
.
Field Name
|
Data Type
|
Description
|
Box_Id
|
Auto Number
|
System number
|
Box_BC
|
Text
|
Barcode or Shelf Number
|
Rec_FileCode
|
Text
|
Alpha/Numeric Field
|
Box_Dept
|
Text
|
Department/Function Owner
|
Box_DateFr
|
Date/Time
|
Beginning Date of Records within box
|
Box_DateTo
|
Date/Time
|
Ending Date of Records within box
|
Box_SeqFr
|
Text
|
Alpha/Numeric Field
|
Box_SeqTo
|
Text
|
Alpha/Numeric Field
|
Box_Desc1
|
Text
|
What records are within the box
|
Box_Desc2
|
Text
|
What records are within the box
|
Box_TransDate
|
Date/Time
|
Date Transferred to storage
|
Box_Status
|
Text
|
Active, Inactive, Out, Destroyed,
etc.,
|
Box-RvwYr
|
Text
|
Year box to be reviewed
|
Box_DestDate
|
Date/Time
|
Date Box Destroyed
|
Figure 2
Retention Management
Table.
The retention management database table below
will allow the user to enter data on the retention periods and legal information
for each record type entered in your file and box management tables. If created, it will provide the information
to:
· Create destruction
notices by creating queries on fields within different tables (Rec & Box),
then creating a report that will produce a destruction notice..
· Create library of legal
citations that reflects the minimum legal requirement for the retention of the record
series.
· Using a hyperlink to
the legal citation instead of a paper copy.
A click of the hyperlink will provide direct access to the source
document (citation) where it is officially maintained. I highly recommend a review of the link on a
yearly basis to insure the citation is current.
Field Name
|
Data Type
|
Description
|
Ret_Id
|
Auto Number
|
System number
|
Rec_FileCode
|
Text
|
Alpha/Numeric Field
|
Rec_Title1
|
Text
|
Alpha/Numeric Field
|
Ret_Jur
|
Text
|
Jurisdiction: Fed, State, Local
|
Ret_Cit
|
Text
|
Enter Citation Number
|
Ret_Title
|
Text
|
Enter Citation Title
|
Ret_Ret
|
Text
|
Enter Citation Retention
|
Ret_OrgRet
|
Text
|
Enter Organization’s Retention Period
|
Ret_Atch
|
Hyperlink
|
Enter a hyperlink to citation
|
Ret_SpecType
|
Text
|
Enter type of record: (T)ax,
(A)rchival, (O)ther
|
Ret_Other
|
Memo
|
Enter any other special information
|
Figure 3
Following
the guides in the books suggested in the previous article, you should be able
to design a simple database as your document management system that will
provide you with a finding aid for search and retrieval of your business
records. Additional resources by Lisa
Friedrichsen listed below:
Course Guide:
Microsoft Office Access 2003 Intermediate
Microsoft Office Access 2007: Illustrated Course Guide, Basic
Microsoft Access 2010: Basic
In Part III I will discuss queries and creation
of data entry forms from the data being captured in the tables above.
Bob Dalton, CRM of Dalton Consulting....................
NOTE: This Blog and the 3rd article are not viewing properly on the blog. Please feel free to request a copy of Part II and III by sending an email message to daltonconsulting@harbornet.com.
Note: My thanks to editor, Barbara Werelius,
for her assistance in
the development of this article. Other
articles by Mr. Dalton can be reviewed and copied at his BLOG site: askthecrm.blogspot.com.
[i] Lisa
Friedrichsen, Microsoft Access 2000 Illustrated Series Introductory, Course
Technology, 2000, page A-4
