Records Management 2004
[pic]
[pic]
Developed By:
University of Idaho
Division of Adult, Counselor and Technology Education
Spring 1999
Revised Spring 2004
Dr. Marty Yopp, Project Director
Funded by a grant from the Idaho State Division of Professional-Technical Education
WHY A RECORDS MANAGEMENT CURRICULUM GUIDE? 5
INFORMATION RECORDS MANAGEMENT 8
Necessity of Records Management 8
Business Records and Information Management 8
The Information Records Management Cycle 9
The Profession of Information Records Management (IRM) 10
Certified Records Manager Requirements 12
The Elements of a Records Management Program 13
Knowledge Management 14
Filing Systems 15
Color Coding 15
Electronic Media Policies 18
VARIOUS TYPES OF FILES 23
CONVENTIONAL DRAWER FILE 23
FIXED SHELF (or Lateral File) 23
HANGING POCKET FILE 24
DOUBLE DEPTH MOVABLE 25
HANGING BOX FILE 25
LATERAL (OR ROLLOUT OR BROADSIDE) 26
MOBILE FILE 27
ROTARY FILE 27
TRANSFER DRAWER FILE 28
TRANSFER BOXES ON SHELVING 29
TIMES-TWO 29
HORIZONTAL CONVEYOR 30
TOP TAB 32
STRAIGHT CUT 32
SIDE (OR END) TAB FOLDERS 33
FULL END TAB 33
NOTCHED END TAB 33
RECORDS AND INFORMATION 35
MANAGEMENT RESOURCES 35
Alphabetic Indexing Rules and Procedures 36
The 12 Rules of Filing 38
Exercise #1: 38
Exercise #3: 40
Exercise #4: 40
Exercise #5: 42
Exercise #6: 43
Exercise #7: 43
Exercise #8: 44
Exercise #9: 45
Exercise #10: 46
Exercise #11A 47
Exercise #11B 48
Exercise #11C 48
Exercise #12: 51
Database Management Systems 54
SECTION ONE 54
Planning an Access Database 54
Special Note: 55
A Database Design Checklist 55
How are Tables Related? 56
Database Objects 57
Tables 57
Queries 58
Forms 58
Reports 58
Data Access Pages 59
Macros and Modules 59
Choosing the Right File Format 59
Working with Database Objects 60
Creating new Objects 61
Managing Database Objects 62
Modifying Object Properties 62
Using Expressions in Database Objects 63
Using Wizards to Create Databases and Objects 65
Exporting and Importing Data 67
Importing external Text Files 68
To import a delimit text file 69
Viewing Your Data 70
Creating a New Table 71
Saving a Table Design 73
Entering Data into Your Table 73
Printing a Table 74
Closing a Database 74
SECTION II 75
Enhancing a Table 75
Creating a Form from an Existing Table 75
Form Design View and Other Form Options 76
Adding, Deleting, Moving, and Modifying Fields 77
Using Shortcut Menus 78
Hiding/Unhiding Fields 78
SECTION III 80
Working with date in the Datasheet view of a table: 80
Options related to the Datasheet 80
Datasheet 81
Changes to a Specific Column within the Datasheet 82
SECTION IV 86
Query Introduction 86
Using Queries to Extract Data from a Database 86
Choosing the Right Query Type 87
SELECT QUERIES 88
CROSSTAB QUERIES 90
PARAMETER QUERIES 90
ACTION QUERIES 91
DEFINING QUERY PROPERTIES 94
Saving a Query 95
Using all Fields of a Table for a Query 95
Moving a Column in a Query 95
Renaming a Query 96
Printing a Query 96
SECTION V 97
Building Great Forms and Reports 97
DESIGN 101: WORKING WITH CONTROLS 97
ADDING A NEW CONTROL TO A FORM OR REPORT 99
POSITION CONTROL AND LABLES 100
MAKING FORMS EASIER TO USE 101
MAKING REPORTS EASIER TO UNDERSTAND 102
Creating a Report with the Report Wizard 104
EXERCISE 1A - ACCESS 105
EXERCISE 1B - ACCESS 107
EXERCISE 2 - ACCESS 109
There are No exercises associated with Section III. 110
EXERCISE 4 – ACCESS 111
EXERCISE 5 - ACCESS 112
Record Management Slide Presentations 113
WHY A RECORDS MANAGEMENT CURRICULUM GUIDE?
(That's so boring!)
Rationale
Filing and records management courses and units have been disappearing from business education programs and classes and from business teacher education programs. At the University of Idaho we have not required Records Management for our teacher education students even though we offer such a course for the office administration majors. Records management principles and procedures are buried somewhere in "Information Technology" units or are not covered at all.
As a result, otherwise well-qualified candidates for office administration positions are entering the workplace ill-prepared to meet the challenges of effectively managing records and information but also with the attitude that filing is menial work that anyone can do and that it is somewhat demeaning. Consequently, many organizations are discovering too late that their records are a mess and are not in any condition to comply with federal and legal requirements or to sustain them in a comprehensive audit.
Records are not very glamorous or important until you don't have what you need to back up a financial transaction, employment and payroll records, sales taxes collected, records of invoices paid and accounts collected. These pieces of paper, vital source documents, are paper trails which back up and support important decisions. They are the life blood for the accounting system and management decision making. And, like it or not, they are still largely generated on paper even if they are later microfilmed or scanned into an imaging system.
ARMA Filing Rules
It is absolutely essential that business educators require all of their students to learn the ARMA Filing Rules which were revised and adopted in 1986. The filing rules I learned in my business education preparation classes are no longer valid. Students must learn these rules and have an opportunity to practice and reinforce them as frequently as possible. The indexing order for business names, numbers, addresses, government agencies, and foreign government agencies require some study and application practice. These rules apply whether they are indexing paper documents or inputting data into a spreadsheet or database. The rules for dealing with punctuation and symbols must also be addressed.
Once Office Procedures courses started giving way to computer application courses the filing classes and modules were dropped. This was a mistake. They should have been re-engineered. Resent studies to determine what skills businesses need their office professionals to possess emphasize record keeping and records management and they even link them together.
Data and Information Management
The effective management of essential business documents must be integrated throughout an organization and given a high priority status. Many businesses that lose their essential business records for any reason lose their momentum and competitive advantage and may fail or close as a result. They don't know where they are or where they are going and the recreating of records is costly and time consuming if even possible. Some records, once lost, are virtually impossible to recreate.
Records management principles, filing rules, and electronic database management design and maintenance belong in the curriculum for virtually every business class. It should be introduced in keyboarding, reinforced in computer courses, and it should be one of the first topics of discussion in an accounting class. The accounting books list the vital information for transactions as a line on a page. "Issued Check # 529 to Idaho Power for $150 for Utility Expense." That is all neat and clean. Unfortunately that does not teach students anything about what is backing up the issuing of the check and what it stands for. The original source document can tell us much more:
• What period of time was included in the statement?
• When is the payment due date?
• Is the amount consistent with prior statements? Is it about what was expected or projected? Is it increasing rapidly?
• Does it include any unexpected charges?
• Has an overdue amount already been paid? Is this a second notice?
• Is there anything unusual about the statement? Is this our account number?
Is the billing address correct? Has it been approved for payment? Do we need to file and keep the source document? Have it imaged? How long do we need to keep it? Should it be backed up?
Every transaction tells a story about the internal or external operations of the organization. Every source document helps document what happened, when, and possibly why. This is an essential step in the problem solving and decision-making process. It is an opportunity to make students think about what they are doing before they journalize a transaction. It is very important.
I urge you to look at the material in this manual and make a conscious effort to incorporate it into your classes in meaningful ways. As technology continues to dominate
our curriculum it is necessary for us to occasionally stand back and think about what students need to know in order to make effectively use of the technology. Effective Record Keeping and Records Management is absolute must.
This document is far from perfect. I give it to you today as something to use now to expose students to Records and Information Management. The content will be converted to a web site which will be improved and expanded over time. If you come across good web sites or other activities or articles which would be useful to other teachers, please share it with me and I will put it on the web page.
Eventually this information will be compiled into an Internet course on Records
Management (BuEd 490, Records Management, 3 credits). If some of you need a course for certification or n a graduate plan consider this option. It should be available by Spring, 2000, and will include:
• Records Management Concepts and Procedures
• ARMA Alphabetic Filing Rules and Exercises
• A Database Management Simulation Using Access
Good luck with your Records Management effort - doing something now is better than nothing at all.
Marty Yopp
myopp@uidaho.edu
INFORMATION RECORDS MANAGEMENT
Necessity of Records Management
Records management is vital to every business organization. Whether it is a small family business, a corporation, a school, or a state or federal agency, the proper maintenance of information is essential. Businesses are realizing the importance of not only accurate records, but also the importance of an information management system that allows for the retrieval of information for use to provide good customer service, legal defensibility and an improved bottom line.
Organizations also need a records management program to reduce their records volume, identify and protect vital records, improve storage and retrieval systems, integrate records management and information systems, and improve workflow and productivity. Ultimately, a good records management system results in cost cutting and an increase in profits.
Aside from monetary profits, there are other benefits of investing in a quality records management system. Some benefits are measurable, while others are not. Providing a higher quality of service to customers, increasing managerial effectiveness and increases in staff productivity are “people” benefits. People are what run the business, whether customers or staff, and when they are better served and are productive, and the company benefits both non-tangibly as well as monetarily. More tangible benefits can be seen when there are reductions in the volume of records, papers and information. It is also advantageous when technology is used in a more efficient and helpful manner. Included in this is the task of integrating systems and keeping them up-to-date. In the end, however, it comes down to the need to prevent major problems and costs associated with improper, incomplete or disorganized records.
Additional risks associated with ineffective records management include legal problems, natural disasters, human-created disasters, criminal activities and sabotage. Each of these risks hinders the ability of the organization to conduct business and earn money.
Business Records and Information Management
Business records take many forms, with the most common still being paper. The information age is here, but it has taken on a different form than what was expected.
Because of the ability to put everything onto computers and disks, it was thought that paper records would disappear. Not so. Paper is still the most common form of records. Though much information is stored on electronic media of one type or another, it is said that paper records are still about 70-90% of records maintained by businesses. It is because of the massive amount of paper records that there is such a strong need by businesses to have a system in place for organization and retrieval of the information. This system of organization is called records management.
The Technology Age has its own complexities with information storage and retrieval. The volume produced with help of the Internet and other electronic media makes it necessary to sort, store and retrieve great amounts of information on electronic storage devices. This task is time consuming and involves condensing the information into manageable and organized pieces. E-mail is a major source of such information.
Information Records Management (IRM) is the management of information in the form of records from the time they are received or created through their processing, distribution and use followed by storage and eventually elimination or identification for permanent archival retention. The records management cycle has four basic steps:
• Creation of the document
• Distribution of the document
• Storage of the document for retrieval purposes
• Removal and disposal of the document.
The Information Records Management Cycle
An example of the IRM cycle is a letter you write to your grandmother. The written form of the letter is the creation process. Then you mail it to Grandma, which is the distribution of the letter. After reading the letter, Grandma places it in a special bin of letters from her grandchildren. She has stored the letter. At some point, grandma wants to remember you or recall something you said in your letter, so she retrieves the letter. Years later Grandma decides to get rid of all of her letters that are more than five years old, so she retrieves the letter and destroys it in one manner or another. Your letter has gone through the records management cycle.
There are other steps that are part of the cycle, but may not always be necessary. At times there are a need to maintain the records. This would include editing or updating the information in the records. There are also situations that require the information to be secured and protected from perils that might damage or destroy the records. Fires, floods, earthquakes, robbery, vandalism and sabotage are some examples. Control is another element that is very important in the records management process. Control provides for a method to keep track of the information that is coming and going. In involves checking records in and out and requesting their return in a timely fashion.
Because a record is information that is necessary and valuable to the management of an organization, a decision must be made as to what information must be kept and managed and what information can be discarded. All documents do not need to be kept. To become a record, the information must be essential information for the management of the business. Records can take many forms other than paper. Records include books, microfilm, computer disks, readable tapes, sound recordings, videos, CDs, etc.
The legal definition of a record as defined by Congress is:
…all books, papers, maps, photographs, machine readable materials, or other documentary materials, regardless of physical form or characteristics, made or received by an agency of the United States government under Federal Law or in connection with the transaction of public business and preserved or appropriate for preservation by that agency or its legitimate successor as evidence of the organization, functions, policies, decisions, procedures, operations, or other activities of the government or because of the informational value of data in them. 44 U.C.C. 3301
The Profession of Information Records Management (IRM)
The Information Records Management (IRM) field is quickly developing into a solid profession, commanding a high level of skill and pay for those who can do the job well.
There are several specialized areas including legal records, medical records, financial records, archiving records, records consulting, and records centers where information is stored confidentially for a fee. Within the specialty areas, there are a variety of job titles.
The following information includes positions posted, samples of job titles, qualifications/skills required, and/or potential pay.
Conflicts Analysis Level II Searchers for law firm with excellent written and spoken
communications skills, ability to work well under pressure, familiarity with7
Windows95/NT, database searching, Microsoft Access, LegalKey, Internet. Experience
as a law librarian or legal assistants.
Document Imaging Team Leader (Program Administrator I) for a state agency with a
four-year college degree in Records Management or related field plus 4 years experience
with records management, information systems and supervisory experience. Salary
$31,800-$41,000.
Document/Records Analyst for a consulting firm with a four-year college degree in
information management or equivalent experience.
Records Analyst for a public school district with a two-year degree in records
management or equivalent, experience with Microsoft Office, good communication
skills, leadership and writing skills. Salary of $27,923-$46,715.
Records Clerk II for a law firm who is accurate, detail-oriented, and highly organized
with 2-3 years of records management experience, database systems, customer service
oriented and 40 wpm typing. Salary $22,500.
Records Manager for a real estate investment advisory firm with a Bachelor's degree in
Business with emphasis in Management Information Systems, Certified Records
Manager designation preferred with a Master's of Library Science degree a plus,
Minimum of 5 years experience with records management or database management
systems.
As you can see, the necessary knowledge varies depending on the type of organization, but each needs some type of records management experience or education. The field is very competitive and will continue to become so. This is because the pay for IRM jobs are increasing faster than other administrative assistant type jobs. Some other job titles you may see in the field of records management are:
• Management Analysts/Consultants
• Manager, Corporate Records Management
• Records/Information Management
• Records Retention Analyst (Program Specialist I)
• Records Retention Center Supervisor and Information Analyst
• Records Specialist
• Senior Technical Consultant
• Supervisor, Clinical Information
• University Records Manager
Though the qualifications vary for each position in records management, there are several skills that stand out as requirements. It is essential to have a good understanding of records management practices, database management software, general computer skills and communication skills for both speaking and writing. Many companies require applicants to have a strong educational background in records management, which may consist of a two or four-year degree, a master's degree or certification.
Some degrees that help prepare people for a career in records management are Library Science, Business Administration, Office Administration, Information Systems, Management Information Systems (MIS), Public Administration, Archival Studies, or Information Technology. In addition, there are degrees which are specific to an IRM specialty such as medical or legal records specialists.
In addition to having experience or education, there are several records management designations which are beneficial for a career in IRM. The most valuable and well known is the Certified Records Manager (CRM) certificate provided through the Institute of Certified Records Managers (ICRM). The ICRM is a non-profit, certifying organization of professional records managers and administrative officers who specialize in the field of records and information management programs. There are also other certifications that directly or indirectly relate to records management. These include programs for archivists, document imaging specialists, form systems professionals, document consultants, information technologists, management consultants, certified restorers, genealogical records specialists, disaster recovery specialists, internet managers, electronic document printing professionals, medical records managers, legal records administrators, fraud examiners and professional secretaries. Each of the programs have specific requirements that need to be met in order to take the examination and receive the certificate.
In the United States there are several certificate programs available for archival records management or information technology. For specific information on these programs or the specific certification exams, go to . This site provides many links to the various organizations and provides some basic information about each type of examination and program.
Certified Records Manager Requirements
Organizations worldwide are recognizing the need for professionals to manage their information and records systems. New technologies and administrative technicalities are complicating the measurement of job-seeker competence. Because of this, the Certified
Records Manager (CRM) designation is now being required or strongly recommended by
employers and it is beneficial to learn about the specific requirements established by the Institute of Certified Records Managers. ICRM designation is very difficult to attain.
ICRM membership requires that all members have met certification and have received the Certified Records Manager (CRM) designation.
The specific requirements that must be met in order to take the exam for CRM certification is comprehensive and requires a baccalaureate degree from a four-year accredited college and three years full-time or equivalent professional experience in records management. Professional experience denotes one who conducts studies and surveys or develops, designs, and implements records management systems in three or more specific records management categories.
The two-day exam requires 70% for a passing score for each part of the examination.
A test mark of 63 or above is automatically re-graded by a different person for possible adjustment. Test results are made available to examinees within three months. All parts of the examination must be passed within a period of five consecutive years. The Institute (ICRM) serves as the official certifying body for the Association of Records Managers and Administrators (ARMA), though it is a separate and independent organization administered by its own Board of Regents. The Institute’s objects are to:
• Develop and administer a program for the professional certification of records
managers including the granting of appropriate recognition.
• Promoting the value of certification of records managers to the various national,
state, and local governments and the private sector.
• Developing and administering certification examinations in records management.
• Working with educational institutions in the development and improvement of
records management courses.
• To define, promote, and advance records management concepts.
• Support of the professional recognition of records managers.
Members also receive the ICRM Newsletter, a Directory of Members and may
attend the Institute's Annual Meeting.
ARMA International, the Association of Records Managers and Administrators, Inc.,
is an international organization with 135 chapters and nearly 11,000 members worldwide.
ARMA's responsibilities include:
• Giving guidance to members who wish to become Certified Records Managers.
• Presenting the premier educational conference in the field, with more than 80
sessions on a wide range of topics such as legislative trends, management
development and vital records protection.
• Enhancing professionalism in the industry.
• Providing a forum for the exchange of information and innovations in the
industry.
• Promoting a scientific approach to solving records and information management
problems.
• Providing file educational opportunities necessary to help industry professionals
stay current on the latest advances in records technology.
• Building, for the future of the industry with a scholarship program that assists
students headed for professional or managerial positions in records and
information management who are attending an institution with a program
recognized by ARMA International.
• Providing placement services as a resource to qualified individuals and
companies.
• Producing technical publications and support materials to assist records
managers and corporations.
More information on ARMA can be obtained by writing, calling or faxing them at:
ARMA International Association of Records Managers and Administrators, Inc.
4200 Somerset Drive, Suite 215
Prairie Village, KS 66208
PH# 913-341-3808
800-422-2762
FAX# 913-341-3742
You can also obtain ARMA information by visiting the following web sites:
International:
Boise:
The Elements of a Records Management Program
The elements and design of a records management program vary dependent on an
organizations framework and size. Public and private organizations differ greatly in the
needs, requirements, methods and development of their records management system. In
general, the establishment of a successful organization wide records management program will include the following common elements and strategies:
• Establish a program that includes support from all levels including management
and professional staff. Delegate authority to the records manager to implement
an organization wide program.
• Integrate the records management program throughout the organization including
a records coordinator for each operational unit.
• Maintain a comprehensive records inventory.
• Develop a records retention schedule that meets the needs of those within the
organization and applicable laws and regulations. Stick to it!
• Develop a quality records retrieval system for active records and another for inactive records.
• Develop suitable forms and adopt a forms management plan.
• Purge the files to remove all duplicate copies, rough-drafts, and unnecessary
envelopes or bulky attachments.
• Establish a policy for the copying and reproduction of records. Avoid
unnecessary copies or duplicate copies.
• Investigate the use of imaging systems and develop procedures and policies for
the use of the system.
• Determine the archival needs of the organization and develop a plan to achieve
these goals.
• Develop and design a straight-forward records management procedures manual.
Keep it up-to-date. Publish it on a web-page.
• Make records management orientation and training a high priority. Quality
records and information management requires a "team effort."
• Identify and protect vital records in a manner which meets or exceeds legal and
organizational needs.
• Establish a records management committee that analyzes the system and makes
recommendations for improvement on a regular basis. This group should include
both records management professionals and policy makers within the
organization who must utilize the information.
Knowledge Management
“Knowledge management refers to the practice and technologies that facilitate the efficient creation and exchange of knowledge on an organization-wide level to enhance the quality of decision making.” (The Delphi Group, 1998)
Businesses, government agencies, and other organizations depend on knowledge as a basis for providing essential services and to be competitive. Knowledge management includes many written forms of documentation, as well as what a person knows in relation to the organization. Whether it is knowledge (intellectual capital) or information, both are essential to the company. Knowledge management is concerned with the systematic, effective management and utilization of an organization's knowledge resources rather than just being concerned with the company’s physical records. It encompasses the creation, storage, arrangement, retrieval and distribution of an organization's knowledge base.
A close relationship exists between knowledge management and records management. The two fields have the common objective of improving the organization's effectiveness through the systematic management of specific information-oriented resources. Records and record keeping systems are valuable knowledge resources. Recorded information, whether a hardcopy or electronic form, is an important part of an organization's knowledge and intellectual capital.
Records management concepts and activities complement and promote knowledge management. Drawing on their experience and expertise, records managers can make significant contributions to their organization's knowledge management initiatives. A well-implemented records management program should pave the way for knowledge management, while a successful knowledge management initiative assumes and includes the existence of a successful organization-wide records and information management program.
A knowledge management program has similarities to a records management program and typically includes:
• Developing and managing stores of reusable resources such as case studies, best
practices, marketing documents, and training materials.
• Developing and managing intellectual capital.
• Having information available to answer questions without questioners'
necessarily knowing what information is needed or where it is located. In other
words, anticipate questions and requests.
• A combination of expertise, document management technology, and high-quality
records management.
• A basis for cooperative thinking, consensus building, and teamwork within
organizations.
Filing Systems
The best filing systems are those that require consistency. The selection of filing equipment is important and requires planning. The type of filing cabinets used will depend on the available space, the volume of records stored and the size of the company. The accessibility of records should also be taken into consideration. Will there be one person accessing the records, or will there be many people handling the records?
An effective type of filing equipment is the open-shelf lateral filing system. This style provides for accessibility, organization, visibility, space conservation (floor space and upper room space) and is less expensive than traditional filing cabinets. In addition, this type of system lends itself to color-coding. Other types of filing systems include traditional pull-drawer files, lateral rollout cabinets, lateral suspension cabinets, mechanical files and mobile files. The suspension cabinets, mechanical files and mobile files each have advantages that might be beneficial to the specific needs of the organization.
Color Coding
Records can be accessed and managed more efficiently if they are color-coded.
Human eyes can distinguish over 11,000 color variations. Color is an important tool which helps us identify, classify, and recognize objects around us. Think what it would be like if we didn’t use color to help us find a car in a large parking lot, know when to stop or go at intersections, see what electrical wires to connect, locate areas on a map, pick fruit out of a bowl, or find seats at a ballgame? The same logic applies to the use of color for filing systems. With today's computer-generated and laser-printed color adhesive folder strip labeling systems, the preparation of folders for quality computer indexed and color-coded systems are easier than ever.
In filing systems, the assignment of color for each number and letter aids in filing and retrieval of records. The assignment of colors to letters, numbers, or other identifiers in a particular position on a file folder results in the formation of a color-block pattern. It is easy to see, when these patterns of color are broken, that a misfile has occurred.
The benefits of color-coding the filing system are that:
• It almost eliminates hidden misfiles.
• It confirms accuracy in filing.
• It offers fast filing and retrieval.
• It works with any filing sequence.
• It allows rapid presorting by color recognition.
• It guides users to within an inch or two of records they seek before even touching
cabinets or records.
• It substantially speeds important file management functions.
Use of color-coded label printing systems makes label generation possible the minute a loan is approved, a patient walks in the door or when a policy is issued. The labels can include color-codes, bar codes, year bands, location or department codes, customer numbers, and a line of printed information. Sophisticated on-demand systems include a multi-use printer that may also be used for other office functions such as producing full color graphs, transparencies, charts or other documents.
A bar coded file tracking system is a records management tool allowing the file folders or other physical records to be scanned in and out. Use of bar coding makes tracking the records easier. Since they are connected directly to a computer system, immediate information on the whereabouts of a record is maintained.
By using a computer based system for the automated printing of labels with bar codes or machine-readable characters in addition to the colors and digits or letters, folder identification information can be made machine readable by a handheld bar-coder or by a computer-based bar-coder. These developments make it easier than ever to convert existing systems to color-coding and to quickly and efficiently develop bar-coded systems making possible computer-based management and tracking of files.
Electronic Records Management and Filing Systems
Electronic records management includes many of the same elements as a paper-base system and the same information management cycle. Though electronic records are more flexible than paper records, they do have some qualities that make them more vulnerable.
Ensuring proper storage and making backups is the key to maintaining electronic records.
Electronic records also need some system of filing. The strategy developed for naming electronic files will be partially dependent on the structure of the organization or department. Filenames have two parts, the actual file name and the extension. Extensions
are important to the running of the file and creating your own extension is not advisable. It is important, however, to understand what the various extensions mean. Some common extensions and their descriptions are:
|Extension |Description |
|BAK |Backup of a text file |
|BAT |Batch file |
|COM |Executable Program |
|DOC |Word Processing Document |
|EXE |Executable Program |
|MDB |Database Program (Access) |
|PPT |Presentation Program (PowerPoint) |
|TXT |Text file |
|XLS |Spreadsheet (Excel) |
If an unknown extension is used, the file may not be accessible again because the computer system will not recognize it as a word processing, spreadsheet or any other type of program. If it does open the file, it may be jumbled.
The actual naming of a file will vary, but the methodology should be similar. For instance, you may have a combination of letters or numbers to indicate the description of the type of file and its contents. If you named a file RP0199JP, this could be interpreted in many different ways unless you actually had a legend to follow in understanding the code. RP0199JP could mean Records, Paper, January (01), 1999, Jane (author/secretary), for Peter (Manager). This could also be interpreted as Report, Presentation, January (01),1999, to John Preston (client or company president). As you can see, coding a filename is important and needs consistency.
Using dates in filenames is tricky. There is a numeric complication with the turn
over to the year 2000. A solution to this is to place the year ahead of the month and day in a YY-MM-DD format, but instead of using "00", use "2K". As shown in the chart that
follows, the year 2000 files will come after the subsequent years (97, 98, 99, 2K). Generally speaking, the computer "files" the documents in a numeric-alpha filing system. In examining the filenames, you will see that numeric characters are placed before the
alphabetic characters regardless of where the number is placed in the filename.
It is difficult to identify documents that should belong together, such as tg021599.doc and wr021599.doc. This is because different methods of naming the documents were used. You can see why it is essential that a specific code should be used when naming electronic files.
Depending on what software program or operating system you are using, electronic
file names may be placed in order based on the column or the row. The list of filenames is in order by column. In more recent software packages file names can be lengthy and more specific to what the document contains. If it is important for the files to be accessible by a number, make sure that a number is used first, and that the most essential number is at the beginning of the filename. If an alphabetic character or word is the most important, make sure that it is placed at the beginning. Again, consistency is essential.
When using both alpha and numeric characters, determine which is the most important, placing them in the correct order for easy retrieval. If there are two numeric characters that must be used, consider breaking them up with the underscore. Extensions do not sort alphabetically. Not all file types will show up. If you are in a word processing document and you open up a file, only your word processing filenames will show unless you tell the system to show all of the files on the disk.
FILENAME EXAMPLES
|011500wr.doc |2K0515wr.doc |Proposal 02.doc |
|01ltr_GB.doc |Alphabetic Filing.doc |rpt0599a.doc |
|01ltr_RZ.doc |dd021599.doc |rpt0599g.doc |
|021599dd.doc |ltr_01PJ.doc |rpt0599J.doc |
|021599tg. |doc ltr01_EB.doc |Text Document.doc |
|031000wr.doc |ltr01_TG.doc |tg021599.doc |
|121599wr.doc |ltr01_TR.doc |Transfer Info.doc |
|2K0101.doc |ltr01PBJ.doc |wr021599.doc |
|2K0415.doc |Numeric Filing.doc |YM_0730R.doc |
|2K0418.doc |Proposal 01.doc |ZT04212K.doc |
Naming documents is confusing. A way to prevent the confusion is to use subdirectories or file folders. The documents can be managed just as you would manage a paper filing system. With the list above, categorized into subdirectories for letters or proposals, by year or by individual (i.e., “wr”, “tg”, and “dd”). There are many variations and the direction taken will depend on the organization's desires and needs. Determine a method that is easy, structured and consistent.
Electronic Media Policies
Establishing policies for electronic records, e-mail and Internet policies are essential to the structure of an electronic records system. Samples policies follow this section:
A SAMPLE ELECTRONIC RECORDS POLICY
(Adapt as Needed for Your Organization)
PURPOSE
This policy is established as part of (your organization's) process for managing the life cycle of
electronic records created by employees using desktop applications.
SCOPE
The scope of this policy includes any and all desktop electronic records created or stored on (your organization's) computer equipment. Some examples of desktop electronic records are: e-mail messages, word processing documents, spreadsheets, and presentations that are stored on PC hard drives or network drives.
COMPLIANCE
Failure to observe the requirements set forth in the procedure may result in disciplinary action up to and including termination.
DEFINITIONS
"Desktop Electronic Record" = "A machine-readable record created using desktop software applications that is stored on electronic storage media."
POLICY
(Your organization) owns and maintains a computer network, and all hardware and software associated with the network. Any documents received, sent, created, or saved on the organization's equipment are considered property of (your organization). Because these records are the organization's property, it is necessary to adopt a policy-which complies with records retention policies and laws-for maintaining documents stored on the network while ensuring proper performance of the network. To ensure both of these things, personnel within (your organization) must maintain electronic records properly. This includes both short- and long-term storage as well as the deletion of electronic records.
The Records and Information Management-information Systems Department of (your organization) will create sub-directories allowing employees to save electronic records and ensure that they will be stored and deleted as required by retention periods. The archiving and deletion of electronic records under these sub-directories will be conducted by the Records and Information Management-information Systems Department.
E-mail messages will have a different method of storage and deletion than other desktop created electronic records. The retention for e-mail messages will be as follows:
... All inbox messages automatically deleted after 30 days. ... All message log entries (copies of sent messages) deleted after 30 days. ... All private folder messages deleted after 180 days. ... All bulletin board messages deleted when no longer required or appropriate.
Any e-mail that is required to be maintained beyond this period should be saved using software tools provided for the organization's administration.
A SAMPLE ELECTRONIC MAIL (E-MAIL) POLICY
(Adapt as Needed for Your Organization)
PURPOSE
This policy applies to e-mail messages created, sent or received by employees using (your organization's) electronic mail system.
OWNERSHIP
(Your organization) maintains an e-mail system. This system is provided to assist in conducting business within the organization. The e-mail system hardware and software is (your organization's) property. Additionally, all messages created, sent, or received on the e-mail system are and remain the property of (your organization). They are not the private property of the employee.
The e-mail system should be used primarily for business purposes. E-mail communication should follow the same standards that are expected in written business communications. The e-mail system may not be used for non-job-related solicitations except as has been deemed appropriate in (your organization's) general policy manual.
PRIVACY
The e-mail system should not be used to send or receive privileged or confidential information or similar materials without prior authorization. The confidentiality of any
message should not be assumed. Even when a message is erased, it is possible to retrieve and read the message. The use of passwords for security does not guarantee confidentiality.
USAGE
Employee’s shall I not use a code, access a file, or retrieve any stored information unless authorized to do so. Employees should not attempt to gain access to another employee's messages without permission.
The e-mail system is not to be used to create offensive or disruptive messages. Among those which are considered offensive are any messages which contain sexual implications, racial slurs, gender-specific comments, or other comments that offensively address someone's age, sexual orientation, religious or political beliefs, national origin, or disability.
Any employee who discovers a violation of this policy should immediately notify their supervisor. Any employee who violates this policy may be subject to disciplinary action up to and including termination.
A SAMPLE INTERNET POLICY
(Adapt as Needed for Your Organization)
PURPOSE
This policy establishes guidelines for (your organization's) employees' access to the Internet as part of business functions on behalf and/or as representatives of (your organization).
ACCESS TO THE INTERNET
The Internet is provided by (your organization) through a tested firewall. This firewall allow authorized employees to access the Internet from our internal network but will not allow anyone from the outside access to our internal network unless approved by (your organization’s) Information Systems Department.
Access to the Internet from our internal network will be through the firewall and will be through approved browser(s). Access to specialized features will be limited to those available on the approved browser(s). Any access requiring the installation of additional software, such as chat room access, will require approval of the Information Systems Department. Any access requirements for laptops and stand-alone units will be evaluated on a case-by-case basis. Requests for specialized access should be sent to the Information Systems Department.
Data classified as GENERAL may be made available through the Internet if it is placed outside the firewall so that access will not necessitate entry into our internal network.
Exceptions to these conditions or constraints will require a new security evaluation and approval through the Information Systems Department.
(YOUR ORGANIZATION) PROPERTY AND PRIVACY
All information stored or transmitted on equipment or a network operated by (your organization) is the property of (your organization). (Your organization) will comply with any contractual relationships involving this information.
Management reserves the rights to view, inspect, or otherwise monitor anything downloaded or accessed by employees from the Internet and employee use of the Internet.
Information transmitted over the Internet is never private! It must be treated as available to the public unless it is confidential information and properly encrypted. If you require encryption capability, contact the Information Systems Department.
RETENTION
All information downloaded from the Internet must be retained in accordance with (your organization's) records retention policy and records retention schedule.
SECURITY OF DOCUMENTS UPLOADED THROUGH THE INTERNET
Employees shall not upload classified and/or proprietary information on the Internet without management approval and then only if encrypted by means approved by the Information Systems Department.
Virus scans are extremely important! Before viewing, executing, or otherwise opening any document, file, etc., downloaded from the Internet, a virus scan must be performed against these documents or files.
USE OF THE INTERNET
It is expected that employees will use the Internet for business reasons and will conduct themselves on the Internet in a manner consistent with our organization's philosophy of treating others with respect and dignity. Communications with others on the Internet, such as through news groups, should follow the same standards that are expected in other written business communications.
The Internet shall not be used for any illegal activities; collection and/or transmission of materials in violation of any federal, state, or local laws; conducting the business affairs of another business entity, including a business operated by an employee; messages or information used for personal advertising or solicitation; receiving or transmitting information or messages which are defamatory, abusive, profane, sexually oriented, threatening, or racially offensive.
Inappropriate use of the Internet is a Violation of our organization's policy and may result in disciplinary action, up to and including termination of employment.
VALIDITY AND INTEGRITY OF INFORMATION ON THE INTERNET
The validity and integrity of information received or sent over the Internet cannot be guaranteed. If additional validity or integrity protection is necessary, additional procedures such as encryption and/or signature verification must be used.
VARIOUS TYPES OF FILES
CONVENTIONAL DRAWER FILE
This is the classic file, sometimes called the “Victorian.” For many years it was the major container of records. Now it is a standard of measure in calculating space savings and efficiency for other types of filing. Hanging pockets in a frame are sometimes added. Folders will stay upright in the pockets without the usual compressor.
[pic]
FIXED SHELF (or Lateral File)
Many companies have justified the cost of changing from “Victorian” files to fixed shelf cabinets for active filing – especially in large installations. This mode combines space savings with increased efficiency, but necessitates switching from top to side tab folders. Good color coding makes this system dramatically effective. Recessing doors double as work shelves. The cabinets can be locked.
[pic]
HANGING POCKET FILE
The slogan of hanging pocket file devotees is that “Every Folder Has a Home.” When it’s out, and it comes back, there’s always a specific place to put it. Pockets are labeled the same as the folders. An empty pocket tells users that the file is out. Correspondence received when the file is out is placed in the pocket and united with the folder when the latter is returned. Though not especially economical of space, or dollars, this method is very satisfactory whenever having a “home” for every folder is paramount.
[pic]
DOUBLE DEPTH MOVABLE
When cabinets with casters are placed before a bank of like cabinets, important space savings are accomplished. A solid, level base is essential. Less active folders are placed in the back row. This is not for a super-active department, where constant pushing and pulling mobile cabinets could become tedious.
[pic]
HANGING BOX FILE
For side-tab color-coded files, hanging boxes are the most flexible. Systems are easily expanded, contracted, moved or rearranged. With color coding, this style leads directly to faster filing and finding. It can be tailored to fit dimensions of any room. The open visibility and accessibility speed operations and enhance accuracy. Economy is an important feature of the hanging box filing system.
[pic]
LATERAL (OR ROLLOUT OR BROADSIDE)
With lateral files, drawers pull out side-ways instead of back to front. Top-tab folders are used. Drawers can accommodate hanging pockets. All folders in a drawer are readily available. There are two variations: drawers with fixed fronts, and pull-out shelves with recessing doors. One drawback – color coding is not very effective with this style.
[pic]
MOBILE FILE
Maximum space is saved when a whole bank of files is placed on a movable carriage, which runs on tracks. Short banks are operated by hand lever; longer, heavier ones automatically by electric power. Tracks are solidly mounted on a level base. Floor loading specified for the building is not to be exceeded. Since access is limited to one opening in the banks of files, a careful study of activity should be made before committing to this system. It’s not for maximum activity offices.
[pic]
ROTARY FILE
The picture tells it all. Files are on shelves that rotate on a conveyor. The desired shelf is automatically brought to the operator, who can rapidly select or refile any folder in the system. Since there is only one operator per vertical conveyor, this is not recommended for hyperactive file rooms. Naturally, color coding makes this system easier and faster. By using small lights positioned along the shelf edge, a properly programmed computer can select the right shelf and indicate the position of desired folder on that shelf.
[pic]
TRANSFER DRAWER FILE
This inexpensive drawer file is for storing less active files. Banks of drawers can go ten high. Economical of space and money, drawers store top-tab folders transferred from active files.
[pic]
TRANSFER BOXES ON SHELVING
Cardboard containers on steel shelving are a standard in record storage centers. Boxes and shelves are designed for compact, waste-free, dormant filing. Boxes and bays are assigned numbers, which, together with a list of box contents, are recorded, usually in a computer base. Thus, specific folders are quickly located and box contents readily scheduled for destruction.
[pic]
TIMES-TWO
It is hard to understand how it works until you have seen it. Fixed shelves, twenty-four inches wide, are back to back. They rotate in a thirty-six inch circle. When rotated, back files become front files. If rotated half way, sides of the shelf ends become the front, thereby closing the file. An ingenious device – well suited to smaller offices.
[pic]
HORIZONTAL CONVEYOR
File or find a folder without walking. This unit brings a two-foot section of folders right to the operator. Files rotate either forward or backward, and when the right section comes in front of the operator, it stops. If electricity fails, operator can walk to the wanted folder.
[pic]
TOP TAB
The illustrations show various top-tab folders used with all types of drawer filing. They are classics and are popular because drawer filing is in general use. Sometimes drawer filing is called “blind filing” – folders cannot be seen until the drawer is opened. Hence color coding for top-tab folders is less effective than with lateral filing.
STRAIGHT CUT
The most widely used folder; in general use with conventional drawer files.
[pic]
THIRD CUT
“Cut” refers to the top of the folder. As illustrated, the top is divided into thirds.
[pic]
FIFTH CUT
As shown, when the top is divided into fifths, this folder is called a “fifth cut folder.”
[pic]
SIDE (OR END) TAB FOLDERS
Side-tab folders are coming into ever wider use because of the trend to color-coded filing systems. It is easier and faster to file and to find them if color-coded. This saves labor – the costliest element of office costs. Illustrated are three popular types of side-tab folders.
FULL END TAB
Illustrated is an end-tab folder that is reinforced with a double folder thickness. This is the most widely used folder for “shelf” filing.
[pic]
NOTCHED END TAB
Here the bottom of the end tab is notched. It is mostly used with “roll-out” cabinets having a lip on the rollout shelf.
[pic]
FOUR INCH BOTTOM TAB
A variation in common use is an end-tab folder with only the bottom four inches made into a tab. This leaves the upper part free for the full view of index guides.
[pic]
RECORDS AND INFORMATION
MANAGEMENT RESOURCES
Web Sites
▪ ARMA
▪ ARMA, Boise Chapter
▪ National Association of Government Archives and Records Administrators
▪ Association for Information and Image Management
▪ National Archives and Records Administration
▪ Information Requirements Clearinghouse
Newsletter
▪ Records and Information Management Report, Greenwood Publishing Group, Inc., .
LISTSERVS
▪ RECMGMTLISTSERV
Listserv@listserv.syr.edu
Message: subscribe recmgmt (your name)
▪ ELECTRONIC RECORDS LIST
Listserv@aucsc2.albany.edu
Message: subscribe erecs-L (your name)
▪ ARCHIVES LIST
Listserv@miamiu/muohio.edu
Message: subscribe archives (your name)
▪ DISASTER PREVENTION AND RECOVERY ALLIANCE
Majordomo@
Subscribe listDPRA
Alphabetic Indexing Rules and Procedures
Learning Objectives:
Define purpose of filing, and the importance of following filing rules
Identify terms such as unit, indexing, alphabetizing, and case
Index and alphabetize data according to established ARMA rules
Every business must develop and maintain an organized way to store written communication, such as reports, letters, memorandums, order forms, invoices, and other such information so that it is available for efficient retrieval or reference. This method of storing records is called filing. While there are a number of different methods for storing or filing information – alphabetic, subject, numeric, and geographic - the most common method is the alphabetic filing system.
Procedures for storing records alphabetically will vary among organizations and even among departments within an organization. Therefore, the filing procedures to be used in any one office needs to be determined, recorded, approved, and followed, without exception. Without written rules for storing records, procedures will vary with time, changes in personnel, etc. These changes could cause difficulty in future retrieval of records or even in the loss of records.
The Association of Records Managers and Administrators, Inc. (ARMA) is an organization designed to help professionals in records management perform their jobs easier and better. ARMA has published a list of Alphabetic Filing Rules, containing standard rules for storing records alphabetically. The 12 rules you will learn in this chapter follow the same principles as the ARMA rules.
Basic Filing Terms
Before learning the 12 filing rules, an understanding of filing terms is necessary.
|Unit. Each part of a name is a unit. Names are alphabetized unit by unit. If there are two parts in a name, the name has |
|two units. |
| |
|Indexing. Indexing is determining the order and format of the units in a name. Is a person’s record filed by first or last |
|name? Is a business record filed under T if the name begins with The? Is punctuation considered with alphabetizing a |
|name? Indexing is deciding which name to file a record under and then arranging the units in that order. |
| |
|Alphabetizing. When you arrange names in alphabetical order, you are alphabetizing them. There are 3 basic categories for |
|alphabetizing names: Personal Names, Business or Company Names, and Government Names. |
| |
• Alphabetizing Unit by Unit. The first step in alphabetizing is to alphabetize Unit by Unit. If the names in Unit 1 are exactly the same, then continue to alphabetize by Unit 2. If the first and second units are the same, the next step is to alphabetize Unit 3, and so on.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |
|Jessica Marie Adams |ADAMS |JESSICA |MARIE |
|Susan K. Adams |ADAMS |SUSAN |K |
|Susan P. Adams |ADAMS |SUSAN |P |
• Nothing Comes Before Something. In alphabetizing, it is important to remember that nothing comes before something.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |
|Ann B. Shoemaker |SHOEMAKER |ANN |B |
|Anne B. Shoemaker |SHOEMAKER |ANNE |B |
|J. Tilden |TILDEN |J | |
|John Tilden |TILDEN |JOHN | |
Case. The case of a letter refers to whether the letter is written as a capital letter (A), called uppercase, or written as a small letter (a), called lowercase. In alphabetizing, uppercase and lowercase letters are considered the same. For example, McAdams and Mcadams are considered to be exactly the same when alphabetizing
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |
|Ashley Mcadams |MCADAMS |ASHLEY | |
|Ashley K. McAdams |MCADAMS |ASHLEY |K |
|Patrick McDonald |MCDONALD |PATRICK | |
|Phillip Mcdonald |MCDONALD |PHILLIP | |
|A.B. Stillworth |STILLWORTH |AB | |
|Abe Stillworth |STILLWORTH |ABE | |
The 12 Rules of Filing
(Based on Established Rules of ARMA)
Rule 1 Names of Individuals. When indexing the name of an individual, arrange the units in this order: last name as Unit 1, first name or initial as Unit 2, and middle name or initial as Unit 3. When two names in Unit 1 begin with the same letter, you consider the next or second letter in arranging for alphabetical order. If both the first and second letters are the same, consider the third letter, and so on until the letters are different.
A unit consisting of just an initial precedes a unit that consists of a complete name beginning with the same letter. Punctuation, such as a period or apostrophe, is omitted.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |
|Rebecca P. Adams |ADAMS |REBECCA |P |
|Susan B. Anderson |ANDERSON |SUSAN |B |
|Terri Anderson |ANDERSON |Terri | |
|William Ken Jackson |JACKSON |WILLIAM |KEN |
|William Johnson |JOHNSON |WILLIAM | |
|Wilma Johnson |JOHNSON |WILMA | |
|Frank Shields |SHIELDS |FRANK | |
|Frank B. Shields |SHIELDS |FRANK |B |
|Debbie Shirley |SHIRLEY |DEBBIE | |
|Ann Marie Williams |WILLIAMS |ANN |MARIE |
|Anna Williams |WILLIAMS |ANNA | |
|David Williamson |WILLIAMSON |DAVID | |
Exercise #1: Index and alphabetize the following names. Present your work in the same format as in previous examples (table layout with unit headings). Write names in all capital letters; do not include any punctuation.
Jamie Jackson
Frank Allen Billings
Anna Smith
Victory Anderson
Debbie Southern
Harry J. Williams
Della Jack
Teresa Ross
William Southerly
Hank Williams
Vicki A. Anderson
Ann Marie Smith
[pic]
Rule 2 Personal Names with Prefixes – Articles and Particles. Prefixes, such as Mc in McAdams is considered as part of the name it precedes. Ignore any apostrophe or space that may appear within or after the prefix. Commonly used prefixes are a, la, d’ D’ de, De, Del, De la, Di, Du, El, Fitz, La, Le, Lo, Los, M’, Mac, Mc, O’, Saint, St., Ste., Te, Ter, Van, Van de, Van der, Von, and Von der.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |
|Olivia DuBerry |DUBERRY |OLIVIA | |
|Paul Duberry |DUBERRY |PAUL | |
|Anna L’Aubourne |LAUBORNE |ANNA | |
|Chuck B. Launders |LAUNDERS |CHUCK |B |
|Jerry A. Mcdonald |MCDONALD |JERRY |A |
|Terri C. McDonald |MCDONALD |TERRI |C |
|Celeste Van Ivan |VANIVAN |CELESTE | |
|John Vanivan |VANIVAN |JOHN | |
Exercise #2: Index and alphabetize the following names. Present your work in the same format as in previous examples (table layout with unit headings). Write names in all capital letters; do not include any punctuation.
Elise Van Der Hoff
Kelli O’Neal
Jack TenClay
Paul Mcdouglas
George Von Lowell
Charles Van Metry
Sylvia D’Champ
Lisa McVey
Wilma LaVoy
Cynthia VanMetry
Vanessa McAnderson
Elli McDougal
Rule 3 Hyphenated Personal Names. Consider a hyphenated first, middle, or last name as one unit. Do not include the hyphen in the unit name.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |
|Valerie Anderson-Smith |ANDERSONSMITH |VALERIE | |
|Jason DeTemple |DETEMPLE |JASON | |
|Tammy DeTemple-Jones |DETEMPLEJONES |TAMMY | |
|Gary Shawn Lee |LEE |GARY |SHAWN |
|Alison Shawn-Lee |SHAWNLEE |ALISON | |
|Kay-Lu S. Shuttle |SHUTTLE |KAYLU |S |
Exercise #3: Index and alphabetize the following names. Present your work in the same format as in previous examples (table layout with unit headings). Write names in all capital letters; do not include any punctuation.
Linda Harper-Hoff
Patrick Wang
Shelly-Lee Ackerman
Holly Jamison
Tracy H. Hoff
Gina James-Ackerman
Luke DeJoy
Jackie Wang-Lu
Yvette Duke-Williamson
Angela-Marie D’Everson
Rule 4 Single Letters and Abbreviations of Personal Names. Initials in personal names (J.D., A.J.) are considered separate indexing units. Abbreviations of personal names (Wm., Jos.) and nicknames (Bill, Rick, Ali) are indexed as they are written.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |
|A. J. Anderson |ANDERSON |A |J |
|Liz Billings |BILLINGS |LIZ | |
|Lou Chandler |CHANDLER |LOU | |
|Wm. Danielson |DANIELSON |WM | |
|T. J. Sampson |SAMPSON |T |J |
|Geo. T. Vickory |VICKORY |GEO |T |
Exercise #4: Index and alphabetize the following names. Present your work in the same format as in previous examples (table layout with unit headings). Write names in all capital letters; do not include any punctuation.
B.J. Tassleman
Vic Albright
J.O. McVey
K.T. Banks
Geo. M. Macinley
Ray J. Allwine
Wm. Sanderson
Lou Vickers
V.C. Wilson
Mary-Liz Jackson
Rule 5 Personal Names With Titles and Suffixes. When used with a person’s name, a title or a suffix is the last indexing unit when needed to distinguish between two or more identical names. A title appears before a name (Capt., Dr., Miss, Mr., Mrs., Ms., Prof., Sgt.). Suffixes appear after a name and include seniority terms (II, III, Jr., Sr.) and professional designations (CPA, CRM, CMA, MD, Ph.D.). Some terms may appear either before or after the name (Senator, Mayor). If a name contains both a title and a suffix, the title is the last unit.
Royal and religious titles (King, Queen, Prince, Princess, Father, Sister) are considered professional designation suffixes unless they are followed by either a given name or a surname only (Father John, Princess Anna) in which case, they are indexed as written.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |Unit 4 |
|Susan Bilderson, CPA |BILDERSON |SUSAN |CPA | |
|Father John |FATHER |JOHN | | |
|Mrs. Anna Jones |JONES |ANNA |MRS | |
|King Abdula |KING |ABDULA | | |
|Mrs. Judy Lenderman |LENDERMAN |JUDY |MRS | |
|Ms. Judy Lenderman |LENDERMAN |JUDY |MS | |
|Sister Mary Smith |SMITH |MARY |SISTER | |
|Peter K Teasdale III |TEASDALE |PETER |K |III |
|Mr. Joshua Wade, Jr. |WADE |JOSHUA |JR |MR |
|Dr. Frank Williams |WILLIAMS |FRANK |DR | |
Exercise #5: Index and alphabetize the following names. Present your work in the same format as in previous examples (table layout with unit headings). Write names in all capital letters; do not include any punctuation.
Sgt. William Trundle
Rose Nuggard, MD
Mrs. Phyllis Asker, Ph.D.
Queen Anne
Jeffrey A. Quinley, III
Ms. Katrina Chance
Dr. Elizabeth Peterson
Senator Jack Rigley
Prince Phillip III
Mrs. Katrina Chance, CPA
Rule 6 Names of Businesses and Organizations. Business names are indexed as written using the letterhead or trademark as a guide. If the letterhead is not available, use sources such as directories (phone, Internet) and advertisements.
Each word in a business name is a separate unit. Exception: When The is the first word of the business name, it is treated as the last unit. Business names containing personal names are indexed as written. Hyphenated names and names with prefixes are considered one unit.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |UNIT 4 |
|Betty’s Boutique |BETTYS |BOUTIQUE | | |
|The Bottom Dollar Store |BOTTOM |DOLLAR |STORE |THE |
|Computers and Such |COMPUTERS |AND |SUCH | |
|Computers Are Us |COMPUTERS |ARE |US | |
|Cpt. John’s Seafood House |CPT |JOHNS |SEAFOOD |HOUSE |
|Doug Tevor Hauling |DOUG |TREVOR |HAULING | |
|Dr. Allen’s Tree Repair |DR |ALLENS |TREE |REPAIR |
|El Amigo Mexican Restaurant |ELAMIGO |MEXICAN |RESTAURANT | |
|EZ Travel Agency |EZ |TRAVEL |AGENCY | |
|St. Paul Lawn Care |STPAUL |LAWN |CARE | |
|The Worthington Coat Factory |WORTHINGTON |COAT |FACTORY |THE |
|Zachery Grey Daily News |ZACHERY |GREY |DAILY |NEWS |
Exercise #6: Index and alphabetize the following names. Present your work in the same format as in previous examples (table layout with unit headings). Write names in all capital letters; do not include any punctuation.
The Paris Daily News
Bank of London
South West Lawn Care
Sgt. Pepper Clothing Accessories
The Bank of Loylds
El Camino Horse Club
Betty Johnson Elementary School
Rule 7 Single Letters and Abbreviations in Business and Organization Names. Single letters in business and organization names are indexed as written. If single letters are separated by spaces, index each letter as a separate unit. An acronym (word formed from the first few letters of several words, such as ARMA and F.I.C.A,) is indexed as one unit regardless of punctuation or spacing.
Abbreviated words (Mfg, Co., Corp., Inc.) and names (IBM, GE) are indexed as written and as one unit regardless of punctuation or spacing. Radio and television station call letters (WBCO, ABC) are also indexed as written and as one unit.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |UNIT 4 |
|A K Electric |A |K |ELECTRIC | |
|A OK Restaurant |A |OK |RESTAURANT | |
|ACE Repair Co. |ACE |REPAIR |CO | |
|KKRS Radio Station |KKRS |RADIO |STATION | |
|L A N Industries |L |A |N |INDUSTRIES |
|LAN, Inc |LAN |INC | | |
|Regal Mfg. Corp. |REGAL |MFG |CORP | |
|US Bank |US |BANK | | |
|USA Today |USA |TODAY | | |
Exercise #7: Index and alphabetize the following names. Present your work in the same format as in previous examples (table layout with unit headings). Write names in all capital letters; do not include any punctuation.
V. J. Golf Course
Carpets Ltd. of CA
OK Tire Center
Garner and Garner, Attys.
KHTY Radio Station
Kellie Hiring Agy.
Dr. Pepper Bottling Co.
C. K. W. Towing
Fashion Corner, Ltd.
GTE Electric Co.
Rule 8 Punctuation and Possessives in Business and Organization Names. All punctuation is ignored when indexing business and organization names. Commas, periods, hyphens, apostrophes, dashes, exclamation points, question marks, quotation marks, and diagonals (/) are disregarded and names are indexed as written.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |UNIT 4 |
|All-in-One Pawn Shop |ALLINONE |PAWN |SHOP | |
|Bob’s Rent-a-Car |BOBS |RENTACAR | | |
|The Crow’s Nest |CROWS |NEST |THE | |
|How Much? Thrift Store |HOW |MUCH |THRIFT |STORE |
|Inside/Outside Glass |INSIDEOUTSIDE |GLASS | | |
|Jack-N-Jerry Catering |JACKNJERRY |CATERING | | |
|The Pepper! |PEPPER |THE | | |
Exercise #8: Index and alphabetize the following names. Present your work in the same format as in previous examples (table layout with unit headings). Write names in all capital letters; do not include any punctuation.
The Rod-N-Reel Shop
Nor-East Import’s
Do-Rite Medical Supplies
Sam’s Hot-Dog Café
Lu-Lu’s Clothing Outlet
Debbie’s Lil’ Tots Daycare
How’s That? Hotel
The On/Off Freeway Café
Nell-Carter Law Firm
A-OK Repair Service
Rule 9 Numbers in Business and Organization Names. Arabic numbers written in digits (1,15,189) and Roman numerals (II, IV, IX) are considered one unit and are filed in numeric order before alphabetic characters with Arabic numbers preceding Roman numerals (2, 156, III, XIV).
Numbers spelled out (ONE, TWELVE, FORTY) are filed alphabetically and appear after numbers written in digits or Roman numerals. Names with numbers included are filed in ascending order (lowest to highest number) before alphabetic names (B4 SHOP, B12 VITAMIN CLUB, BATTING A THOUSAND SPORTING GOODS). Names with numbers appearing in other than the first unit are filed alphabetically and immediately before a similar name without a number (PIER 28 IMPORTS, PIER AND PORT RESTAURANT). The letters st, d, and th following an Arabic number are ignored (1st is indexed as 1, 2nd as 2, 5th as 5 and so on).
Inclusive or hyphenated numbers (7-11 Grocery Store) are indexed according to the number before the hyphen and the number after the hyphen is ignored (7 GROCERY STORE). Hyphenated numbers that are spelled out (Thirty-one Flavors) are considered one unit and the hyphen is ignored (THIRTYONE FLAVORS). An Arabic number followed by a hyphen and a word (7-Gables) is considered one unit (7GABLES) and the hyphen is ignored.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |UNIT 4 |
|1-2-3 Easy Shopping |1 |EASY |SHOPPING | |
|1 Stop Shopping Center |1 |STOP |SHOPPING |CENTER |
|4th Street Market |4 |STREET |MARKET | |
|7-Days Extended Inn |7DAYS |EXTENDED |INN | |
|XXI Movie Theatre |XXI |MOVIE |THEATRE | |
|Annie’s Buffet |ANNIES |BUFFET | | |
|Gary’s 9-Way Service Co. |GARYS |9WAY |SERVICE |CO |
|Gary’s Auto Repair |GARYS |AUTO |REPAIR | |
|Twenty Mile Steak House |TWENTY |MILE |STEAK |HOUSE |
|Twenty-First Street Photo |TWENTYFIRST |STREET |PHOTO | |
Exercise #9: Index and alphabetize the following names. Present your work in the same format as in previous examples (table layout with unit headings). Write names in all capital letters; do not include any punctuation.
99 Flavors Yogurt Shoppe
1 More Time Clothing
The Makco Five, Inc.
7th Street Pharmacy
Henry IV Restaurant
2nd-Hand Music Equipment
Makco 5 Gas Station
Fifty-Fifty Auction Market
Sixth Dimension Insurance Co.
6th Avenue Apts.
Rule 10 Symbols in Business and Organization Names. If a symbol is part of a name, the symbol is indexed as if spelled out. When a symbol is used with a number without spacing between ($5, #1), it is considered one unit and the symbol is spelled out (5DOLLAR, NUMBER1).
|SYMBOL |INDEXED AS |
|& |AND |
|¢ |CENT |
|$ |DOLLAR or DOLLARS |
|# |NUMBER, POUND, or POUNDS |
|% |PERCENT |
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |UNIT 4 |
|50¢ Burger Den |50CENT |BURGER |DEN | |
|D & B Bargain |D |AND |B |BARGAIN |
|Dan’s Donut Shop |DANS |AUTO |REPAIR |SHOP |
|$ Days Hotel |DOLLAR |DAYS |HOTEL | |
|The Dollar Smart Shop |DOLLAR |SMART |SHOP |THE |
|Just Good ¢ Store |JUST |GOOD |CENTS |STORE |
Exercise #10: Index and alphabetize the following names. Present your work in the same format as in previous examples (table layout with unit headings). Write names in all capital letters; do not include any punctuation.
#1 Auto Repair Shop
Kasey & Anderson , Attorneys
$ Saved Used Furniture
The 99¢ Bargain Basement
High $ Real Estate Agcy.
110% Above the Rest
# One Appliances
Dollar Drug Store
Kasey & Kasey Bookstore
N & K Auctions
Rule 11 Government Names. Government names are indexed first by the name of the government unit – country, state, county, or city. Next, index the distinctive name of the department, bureau, office, or board. The words "Office of", "Department of", "Bureau of", etc. are separate indexing units when they are part of the official name.
State and Local Government
State and local government names are indexed first by the name of the state, providence, county, city, or town that has jurisdiction over that government agency. The distinctive name of the agency is considered next. For example, a city will have jurisdiction over a board of education, so the city would be indexed first, then the board of education. The words "State of", "County of", "City of’, "Department of", etc. are added only if needed for clarity and if it is in the official name.
(See examples on page 13) CHECK FOR ACCURACY
Exercise #11A Index and alphabetize the following state and local government names. Present your work in table format. Change paper orientation to Landscape in order to present UNITS all on one line. Write names in all capital letters; do not include any punctuation.
UNDER THE
NAME JURISDICTION OF
Seattle Police Dept, Seattle, Washington City
Department of Social Services, Juneau, Alaska City
Parks Department, Dallas, Texas City
Travis County Medical Examiner, Travis, Missouri County
Department of Health, Ferris County, Montpelier, Vermont County
Department of Tourism, State of Maine, Augusta, Maine State
Bureau of Weights and Measurements, Sheridan, Wyoming State
Department of Education, New York State State
United States Government Names
United States government names are indexed first under United States Government, then by the distinctive name of the department, bureau or service. The words "Department of", "Bureau of", etc. follow if it part of the official name. In each of the following examples, Unit 1 is United, Unit 2 is States, and Unit 3 is Government.
(See example on page 14) CHECK FOR ACCURACY
*Note that table begins with Unit 4; Units 1-3 are displayed above
Exercise #11B - Index and alphabetize the following U.S. government names. Present your work in table format. Change paper orientation to Landscape in order to present UNITS all on one line. Write names in all capital letters; do not include any punctuation.
Portland Field Office, Food and Drug Services, Department of Agriculture
U.S. Department of Interior, National Park Service
Bureau of Engraving and Printing, U.S. Department of Commerce
U.S. Securities and Exchange Commission
Internal Revenue Service, U.S. Department of the Treasury
U.S. Bureau of Indian Affairs
Farmers Home Administration
U.S. Division of Environmental Planning
Exercise #11C -Index and alphabetize the following foreign government names. Present your work in table format. Change paper orientation to Landscape in order to present UNITS all on one line. Write names in all capital letters; do not include any punctuation.
Ministry of Agriculture, Venezuela
Industrial Development Authority, Portugal
Department of State, Australia
Bureau of Mining, Republic of Zaire
Ministry of Education, Republic of China
Department of Education, Japan
References
Listed below are a number of available publications to help in filing government names.
State Government:
State Information Book, by Susan Lukowski
U.S. Government:
United States Government Manual , published annually
Congressional Directory, published annually
Foreign Government:
Countries, Dependencies, Areas of Special Sovereignty, and Their Principal Administrative Divisions, published by the U.S. Department of Commerce, National Bureau of Standards
World Almanac and Book of Facts, updated annually
State and Local Government Names - Listed by Unit
|NAME |JURISDICTION |INDEXED NAME |
|Court House, Evans County |County |EVANS COUNTY |
|Hazard, Kentucky | |COURT HOUSE |
| | |HAZARD KENTUCKY |
|Board of Education, |City | |
|Freemont, MO | |FREEMONT |
| | |EDUCATION BOARD OF |
| | |FREEMONT MISSOURI |
|Banking Office, |State | |
|Dept. of Commerce, | |TEXAS |
|Dallas, TX | |COMMERCE |
| | |DEPT OF |
| | |BANKING OFFICE |
| | |DALLAS TEXAS |
| |UNIT 1 |UNIT 2 |UNIT 3 |UNIT 4 |
|NAME | | | | |
| |UNITED |STATE |GOVERNMENT | |
| | | | | |
|NAME |UNIT 4 |UNIT 5 |UNIT 6 |UNIT 7 |UNIT 8 |
| | | | | | |
|U.S. Department of Labor |LABOR |DEPARTMENT |OF |EMPLOYMENT |STANDARDS |
|Employment Standards | | | | | |
| | | | | | |
|Bureau of Prisons |JUSTICE |DEPARTMENT |PRISONS |BUREAU |OF |
|Justice Department | | | | | |
| | | | | | |
|U.S. Treasury Department |TREASURY |DEPARTMENT |CUSTOMS |SERVICE | |
|Customs Service | | | | | |
Foreign Government Names
Foreign government names are indexed first by the name of the country, then by the name of the distinctive department, bureau, or board. The words "Department of", "Bureau of", "Ministry of", etc. follow if it part of the official name.
|NAME |UNIT 1 |UNIT 2 |UNIT 3 |UNIT 4 |UNIT 5 |
| | | | | | |
|Federal Republic of Germany |GERMANY |FEDERAL |REPUBLIC |OF | |
| | | | | | |
|Consulate General | | | | | |
|Peru |PERU |CONSULATE |GENERAL | | |
| | | | | | |
|Department of Human Resources |TUNISIA |HUMAN |RESOURCES |DEPARTMENT |OF |
|Tunisia | | | | | |
Rule 12 Addresses. When personal names and names of businesses and organizations are otherwise identical, the filing order is determined by the address. The elements of the address are considered in the following order: City, State (spelled out in full), Street Name, Quadrant (NE, NW, SE, SW), House or Building Number
NOTE: Because space is limited in example below, Unit 1 is displayed above and indexing begins with Unit 2.
| |UNIT 1 | |
| |COMPUTERWORLD | |
| | | |
|NAME |UNIT 2 |UNIT 3 |UNIT 4 |UNIT 5 |UNIT 6 |
|ComputerWorld |AKRON |OHIO |AVENUE |NE |12 |
|12th Avenue NE | | | | | |
|Akron, Ohio | | | | | |
|ComputerWorld |AKRON |OHIO |ELM |STREET |86 |
|86 Elm Street | | | | | |
|Akron, Ohio | | | | | |
|ComputerWorld |COLUMBUS |OHIO |WARNER |AVE |600 |
|600 Warner Ave | | | | | |
|Columbus, Ohio | | | | | |
|ComputerWorld |PORTLAND |OREGON |AND |MAIN |7 |
|7TH & Main | | | | | |
|Portland, Oregon | | | | | |
|ComputerWorld |PORTLAND |OREGON |AVENUE |SW |7 |
|7TH Avenue SW | | | | | |
|Portland, Oregon | | | | | |
|ComputerWorld |PORTLAND |TEXAS |NORRIS |DRIVE |257 |
|257 Norris Drive | | | | | |
|Portland, Texas | | | | | |
Exercise #12: Index and alphabetize the following names. Present your work in table format. Change paper orientation to Landscape in order to present UNITS all on one line. Write names in all capital letters; do not include any punctuation.
Cross Referencing
A cross-reference is a notation that a name is also filed elsewhere. Some records of people and businesses may be requested by names that are different from those by which they are stored. This is particularly true if the key unit is difficult to determine. When a record is likely to be requested by any of several names, a cross-reference is prepared. A cross-reference would be prepared in the following situations.
Personal Names
1. Changed Names. When a person has changed their name due to marriage, divorce, etc. You would file records under the new name. However, for a period of time, you would make a notation of the name change in the new file cross-referencing it to the file listed under the former name.
2. Unusual Name. When it is difficult to determine which of a person’s name is the surname. For example, a foreign name, such as Taipei Chou, would be filed under Chou and cross-referenced under Taipei. Another example would be where both names appear to resemble the first name, such as Gary David. The original file would be indexed under David and cross-referenced under Gary so if a request came in for David Gary, one could find the reference to the original file.
3. Hyphenated Name. When the surname is hyphenated. Some women, and sometimes men, use hyphenated surnames when they get married. For example, Sue Bennett-Anderson, would be filed under Bennett and cross-referenced under Anderson.
Some men use hyphenated surnames that are their family names. If this is the case, they are already known by that name and do not need to be cross-referenced under both surnames.
4. Alternate Name. When a person is known by more than one name, such as a business name, you need to make cross-references. Some examples are Mary Hasfurther-Smith doing business as Mary Hasfurther; Jenny Starwing doing business as Jenny Star; John Chandler, DDS, also known as John Chandler, III.
5. Similar Names. There may be a variety of ways to spell a name, such as Allen, Terry, Vicki, Kathy, etc. A SEE ALSO cross-reference is prepared for all possible spellings.
Business Names – The original name filed is the name appearing on the letterhead.
1. Compound Names. When a business name includes two or more surnames, a cross-reference is prepared for each surname other than the first. For example: Anderson, Johnson, and Williams Attorneys would be listed under Anderson and cross-referenced under both Johnson and Williams.
2. Abbreviations and Acronyms. When a business is commonly known by an abbreviation or an acronym, a cross-reference is prepared for the full name. Some examples are ARMA (Association of Records Managers and Administrators, Inc), MADD (Mothers Against Drunk Drivers), and IBM (International Business Machines Corporation).
3. Popular and Coined Names. A business may be known by a popular or coined name. A cross-reference will help in locating the file. Some examples are: Penny’s (J.C. Penny’s), Betty’s (Betty’s Café), and Smitty’s (Smith’s Hardware Store).
4. Hyphenated Names. Just like personal names, business surnames that are hyphenated need to be cross-referenced for each surname combination.
Database Management Systems
SECTION ONE
Planning an Access Database
Of all the Office applications, Microsoft Access is by far the most demanding and conceptually challenging. Anyone who’s ever composed an interoffice memo or put together a budget can relate to Word and Excel instantly; an Access database, on the other hand, is made up of many individual objects, each of which must be built individually. With a wizard’s help, it’s possible to put together a simple database application in a relatively short time--to handle everything from tracking the contents of a wine cellar to managing inventory. Access applications can also scale up to enormous sizes, serving the information needs of large organizations and acting as a front end to data stored on mainframes and other network database servers.
Using an Access database, you can store and manage large quantities of data for a wide variety of business and personal activities. If you’ve previously used flat-file database managers to perform simple list management tasks, you’ll discover that Access is for more powerful, with the capability to maintain and link multiple tables and create applications using a robust programming language. Before you begin laying out the structure of a database, it helps to understand the components that make up Access.
• The database engine is the (generally invisible) software that actually stores, indexes, and retrieves data. When you create a standalone database, Access uses its own engine to manage data. If you choose File, New and select one of the Project options, you can use the Microsoft Data Engine (MSDE) instead. MSDE is compatible with Microsoft’s enterprise-wide database program, SQL Server.
• Database objects provide the interface you use to view, enter, and extract information from a database. The most common database objects are tables, forms, queries, and reports.
• Access includes a full set of design tools that you use to create objects. The reports designer, for example, enables you to sort data, group by fields, and add headers and footers to each page as well as the entire report.
• Finally, Access includes a rich set of programming tools you can use to automate routine tasks. Confusingly, the Access database window includes an object type called macros; these automation tools are completely different from the Visual Basic for Applications code you can add to most database objects.
The basic file type in Access is a database, which uses the extension *.mdb. Curiously, an Access database file doesn’t have to contain any data at all; if you use Access strictly as a front end to retrieve information from a SQL Server database, the *.mdb file need contain only form, report and query objects. If you design your database to hold data, you must create one or more table object to hold the data, and those objects as well as the data itself are stored in the database file.
Special Note:
Access wizards make it relatively easy to create and work with database objects, but don’t underestimate the challenges of build a robust, easy-to-use database application. Access applications have a way of creeping up in complexity, and it’s all too easy to get in over your head. Defining database table relationships and their associated referential integrity rules, adhering to standard database normalization rules, adding error-handling routines, and setting up security for multiple users are just a few of the significant challenges you’ll face as you build an application. Don’t even consider using Access to handle mission-critical data, such as the accounting system for your business, unless you’re a skilled programmer. And even then, chances are good that a commercial program will handle the job more smoothly and safely than anything you can build on you own.
A Database Design Checklist
The biggest single reason an Access database application fails to materialize into a functional smooth working applications is the lack to planning, pre-development analyze and table layout. Before you begin creating database object, think carefully about who will use the database and what kind of reports you will need to produce from the data within it. Use the following checklist to define your design:
• Storing data:
o How much data do you plan to enter?
o Home much training and expertise will be required of those who are responsible for that data entry?
o Will you need to create systems to ensure accurate and reliable data entry?
• Retrieving data:
o What relationships do you need to establish between the different set of data that you produce?
o How can you exploit these relationships to create new combinations of dta from different sources?
o Do you need to subtotal or summarize the data in any way?
• Modifying data:
o Who will be responsible for editing data, and what level of understanding will be required of them?
o What safeguards will you need to create to ensure that changes in your database do not result in duplicate records or inconsistent data?
o If several people are using the database, how do you prevent unauthorized changes to data, while still allowing users to retrieve the information they need?
How are Tables Related?
There are many pitfalls and potential show stoppers you may come in contact with when you develop a database from scratch. Of these, the three that most often lead to failure are: Lack of planning, data analysis, and table design.
Lack of planning is often the direct result of how easy it is to open Access, generate a new database, generate a table or two, import or input some records and generate a few queries and a report or two. Several weeks later after several hundred records have been posted, things start to go wrong. Suddenly the reports no longer want to work, data you know is in the database tables is not showing up on these reports, duplication of data seems to be running amok, queries that should be returning dataset with a few records suddenly are returning thousands of records. All the praise you have been receiving for this database has turned to smoke and ashes. “How come?”, “What happened?”, “This data is garbage”, and “What are you going to do about it?” seem to be the remarks of the day. To fix the problem, you will often find yourself totally redesigning the database from scratch. You may have some luck in saving all or most of the current data but that is not a given. Planning, data analysis, and table design/definition can eliminate this.
Figure 1
[pic]
When designing a database, you should provide at least one field in each table that may be repeated in at least one of the other tables. This field, called a Primary Key, needs to be unique and once defined and saved will not allow duplicate entries within the database table. The primary key in a primary table is related to secondary table(s) as a Foreign Key within the secondary table. This relational database management system (RDMS) function allows you to create queries such as shown above to generate ad hoc or formal reports needed for data analysis or data presentation.
In comparison, a manual database, such as a library database, would use a book’s call number where you could create a report listing both the book’s title and author (first table) and the price and publisher (second table). In this case, the call number would be the primary key for the book table and a foreign key for the publisher table. There are several special rules you must adhere to when defining primary and foreign keys within Access. For the purpose of this unit, we will not go into defined these keys.
Database Objects
Figure 2 Use the database window to see a list of all database objects, arranged by type
[pic]
The basic building blocks of an Access database are objects. Although Access supports many types of object, the most common by fare are tables, queries, forms, and reports. A database can contain any number of objects. The database wind, which is visible by default when you open a new database, let you create and edit objects contained in the current database. As you can see in figure 1, the Access database window consists of two distinct regions: a larger region on the right that displays a list of object in the current database.
Tables
A table is the basic unit for storing a collection of data in an Access database. A table’s definition consists of a list of fields, each of which stores a discrete piece of information for a single record. For example, an Employees table might contain the fields EmployeeID, LastName, FirstName, Position, DateHired, and HourlyRate. Because each record consists of one complete set of fields, a single record in the Employees table contain all available fields for a single employee. Fields such as HomeAddress, HomePhone, MarrageStatus, NumberDependents, WifeName, and SocialSecurityNumber may also be part of this table or be part of an associated table due to company policy or security reasons.
The arrangement of fields and records is most obvious in Datasheet view, which displays data in rows and columns. When you open a table in Datasheet view, each record consist of a single row, and each column represents a single field.
Queries
Queries enable you to extract a subset of data from a single table, from a group of related tables, or from other queries, using criteria you define. By saving a query as a database object, you can run the query at any time, using the current contents of the database. When you display a query in Datasheet view, it looks exactly like a table; the crucial difference is that each row of the query’s result set can consist of fields drawn from several tables. A query can also contain calculated fields, which display results based on the contents of other fields.
CAUTION
Forms
Access forms enable user to enter, view, and edit information, generally one record at a time. Yu can design forms that closely resemble pages forms such as invoices and time sheets, or you can create forms that are organized for data entry, complete with data-validation rules. A form window can also include a subform that displays information from a related table. For example, a form that shows a single record from the Departments table might include a subform that displays all the employees who work in a give department, allowing you to edit information about those employees.
Reports
Reports enable you to present data from one or more tables or queries in a readable style and a professional format, generally for printed output. A report might include detailed list of specific data, with each row consisting of a single record, or it might provide a statistical summary of a large quantity of information. A report design can include grouping and sorting options; for example, you might create a weekly sales summary that runs a query, groups the query result set by salesperson, and displays details of each sale in a list beneath each name.
TIP
Data Access Pages
Pages--also known as data access pages--enable you to publish database information on a corporate intranet or (with some difficulty) on the Web. You can design pages that present data, enable users to edit the contents of a database--including changing data and appending new data to a table--or provide tools for data analysis. It’s relatively easy to create a data access page, thanks to the Page Wizards; however, editing a data access page and managing its security setting require advanced skills. Unlike all other database objects, a data access page is stored on disk as a separate HTML file; icons in the database window are shortcuts to the actual file.
Macros and Modules
The final two selections in the database window allow you to automate actions in an Access database:
• Macros enable you to define a sequence of actions in an Access database. Macros are generally easy to create, even for users who have no programming background. You select each action by name, fill in the appropriate action arguments, and optionally supply a condition under which the action will be performed. For example, you can specify that a particular macro is to run every time you open a specific for, or you can attach the macro to a command button in a form. To run a macron, select the object in the Macros list and click the Run button on the Access toolbar.
• Modules are collections of Visual Basic procedures and declarations, designed to perform specific tasks in the context of your database. Unlike Word, Excel, and PowerPoint, Access does not have a macro recorder that can generate VBA code automatically.
Choosing the Right File Format
Access 2003 and Access 2002 share a common file format, making it easy to share database files between the two versions. If you expect that you will want to use your database with earlier version of Access, you need to pay special attention to your file format options.
To guarantee backward compatibility with older version, any new databases you create using Access w003 or Access 2003 use the older Access 2000 format. This option ensures that your database will work properly in a large organization where some users are still running Access 2000. Using this format allows all Access 2000/2002/2003 users (with correct permissions) to modify the design and content of the database.
The Access 2002-2003 format include a handful of new functions, methods, and properties, and its method for sorting objects within the database file is more efficient. If you’re certain you will use a database only on systems running Access 2002 or later, you can convert it to the new format by choosing Tools, Database Utilities, Convert Database, To Access 2002-2003 Format. (If you choose this menu option with no database open, you will be prompted to supply the name of the database to convert.)
Working with Database Objects
As noted earlier all the objects in a given database are available for browsing in the database window. You can visit this location and create a new object with a single click.
The default action for database object is Open--if you double-click a query icon, Access executes that query and returns its result set in Datasheet view. Likewise, double-clinging a form or report icon opens the select object using the current contents of the database. The effect is the same if you select an object and click the Open button at the top of the database window. (If you select a Report object, the Open button is replaced by the Review button.)
To view and edit the definition and structure of an object, select any object and click the Design button. In Design view, you can modify the appearance of an object (the fonts and colors on a form), change the table or query from which it derives data, or adjust any of hundreds of other properties for the selected object.
Creating new Objects
To create a new table, query, form, or report, use either of the following techniques:
Figure 3
[pic]
• Click the New button on the toolbar at the top of the database window, and select one of the options listed in the resulting dialog box. Figure 2 show the New Form window, listing a variety of wizards and AutoForm tools for create new forms. In addition, the first entry in the list enable you to go directly to the Design view for a form, and begin your work on a new form object from scratch.
Figure 4
[pic]
• Double-click any of the template icons show at the beginning of any object list. These icons (which resemble a scratchpad with a key icon on it) offer multiple ways to create a new object, including various wizards and the option to create an object from scratch in Design view. As figure 3 demonstrates, the canned choice available form the New Table Wizard can be a useful starting point for business and personal databases.
When used judiciously, wizards can be enormously helpful in the initial design of any database object. Some wizards are valuable mainly as introductory tools for newcomers to Access, whereas others are consistently useful even for experienced database developers. The Table and Query Wizards are acceptable for generic databases, but in both cases, Design view represents a far more efficient way to develop individual objects that precisely match specify requirements. On the other hand, the Form and Report Wizards almost always provide an excellent starting point for creating new forms and reports. When working with forms and report in Design view, it’s usually easier to move or modify existing object than it is to add and edit new controls.
TIP
Managing Database Objects
To see a concise list of available options for existing object, select any object in the database window and use the right-click shortcut menus. Many of these options are also available from buttons on the Access toolbar, or from Access menus. You can open any object to view its content or its design; you can also rename or delete an object, cut or copy it to the Windows Clipboard, or add it to a group of favorite shortcuts for quick access.
TIP
Modifying Object Properties
Confusingly, every database object has two set of properties. If you right-click the object’s icon in the database window and choose Properties, you see a bare-bones dialog box that list the object’s General properties. These include the object’s name, a text description, the data the object was created, and the date it was last modified. Ho-hum
By contrast, if you open an object in Design view and click the properties button, you see a complete list of properties that enable you to control the appearance and behavior of that object. Figure 4 shows the Properties dialog box for a form, with all available setting organized by category on five tabs.
Figure 5 The Design view properties of a database object include a much more important
list of settings that can dramatically change the way you see and use an objects.
[pic]
Because the properties dialog box is modeless, you can leave it open as you work with different object. The contents of the dialog box always match the currently selected object. Rather that closing and reopening the dialog box as you edit a form, move it off to the side where you can see its contents without hiding fields or controls on the form. This technique is the most efficient way to adjust the properties of text boxes, subforms, labels, and other part of a form or report.
Using Expressions in Database Objects
When designing database objects, you do not have to limit yourself to data stored in a table. Extend the power of a database by writing expression to transform the data on the fly. An expression is a combination of symbols, values, and identifiers (the name of a field, control, or property) that calculates a numeric result, combines text, or produces a logical value. Some of the operators you will use in expression include everyday arithmetic operators: + (plus), - (minus), * (multiplication), and / (division). These are the same arithmetic operators you use in Excel. Other operators used in expression might be less familiar.
Expressions are useful throughout Access in may types of objects. The following are a few examples:
• In a query, you might include calculated fields, in which each entry is the result of an expression. The operands in the expression might include other field in the same table or in a related table. You can use an expression to calculate a due date for an invoice ([SaleDate]+30) or to product a total, such as [Qty]*[UnitPrice]. You supply the expression for the column and Access performs the operation for each record in the resulting Datasheet.
• The design of an individual field in a table might include a validation rule, which specifies a range of acceptable entries in the field itself. You might create a rule that prohibits users from entering a value in the SaleDate field that is in the future or more than 30 days in the past. If a given data entry does not meet the condition expressed in the rule, Access rejects the entry. To create a validation rule, write an expression that will evaluate to True or False for each new entry. If the result is True, the entry is accepted; if False, it is not.
• A criterion is an expression that you can use to select a target group of records for a particular operation. Any record that meets the criterion becomes part of the group; a record that does not meet the criterion is excluded form the group. Again, a criterion expression results in a value of True or False for each record examined.
In these and other examples, you use specify types of operators in expression to produce the appropriate types of values. The following categories of operators are commonly used:
• The Arithmetic Operators -- In addition to the familiar four (+,-= (greater than or equal), and Between (expressing a numeric range). These operators produce logical values, indicating whether a comparison is True or False.
• The Logical Operators -- These take logical operands and product logical results. A logical operator might combine the values of two comparison expressions. Among these operators, the most commonly used are And (true if both operands are true), Or (true if one or both operands are true), and Not (produces the opposite value of an operand). Other logical operators include Eqv (true if both operands have the same value), Imp (true if the first operand is true and the second is false), and Xor (true if the operands have different values.
• A String Operator -- The & symbol represent concatenation, the process of combining two text values. Example: [FirstName] &‘ ‘&[LastName] in a report.
Figure 6
[pic]
Because expression are so central to the design of database object, Access provide a special tool call the Expression Builder to help you write expressions quickly and accurately. As you see in Figure 5, the Expression builder contains button representing operands, along with other categories of identifiers that might become part of an expression.
You can generally open the Expression builder by clicking the Build button (labeled …) next to the box where the expression is entered. Or, right-click inside the box and choose Build from the shortcut menu.
As you can see, the Expression builder is a very complex wizard and will require some practice to become comfortable with its use. The easiest way to start using the expression builder is to generate a simple expression you already know using the various buttons, drop downs and options. In this way, you reinforce your use of the Expression builder and start to understand the mechanics of using it.
Using Wizards to Create Databases and Objects
As noted earlier, Access offers a collection of wizards to help you create individual object in a database that you are developing. In addition, Access includes another set of wizards that create complete, special-purpose databases, devoted to specific business procedures. These wizards create fully developed database applications designed to handle common business tasks, such as expense accounting, time and billing management, and order entry.
The wizard-generated databases create a full set of tables, queries, forms, and reports, as well as a sophisticated database application. The centerpiece of each of these canned databases is a form called a switchboard, which offers one-click options for viewing tables, opening data-entry forms, running queries, and producing reports. The menus and forms are tied together with VBA code, which you can easily inspect and borrow for other applications.
To build a new database by using any of the database wizards, follow these steps:
1. Click the New button on the Access toolbar, or choose File, New. In the New File task pane, click the On My Computer link under the Templates heading.
2. Click the Databases tab and select any of the database wizard’s icons. Don’t expect any help from the preview window on the right--all it shows is an abstract illustration intended to represent the functions of the corresponding database application.
3. Click OK to start the wizard. In the File New Database window, enter a name for the database file the wizard will generate, choose a new location if necessary, and click the Create button.
4. Follow each step of the wizard to read a brief description of the database application, select fields for specific tables, and choose formatting options, such as background images and presentation styles.
Figure 7
[pic]
5. After you have completed all the steps, click Finish. The wizard generates the database and all its object and opens the main switchboard, such as the one shown in figure 6.
Figure 8
[pic]
Figure 7 shows you the different canned databases you can select from. Sometimes, it is easier to develop a database application by starting with a canned database and then modifying it to fit your current needs.
By click on the Templates on Office Online button you can easily find list of other templates that may fit your current needs.
Figure 9
[pic]
Exporting and Importing Data
Sooner or later--probably sooner-- you will want to transfer information stored in an Access database to some other software environment, or move data originally created in another program into Access. In some cases, you’ll want to move entire tables between database programs for use in different applications. You might want to copy a table of supplier names and addresses from Access so that another database developer can incorporate that data into an application created with SQL Server or Oracle. Or, if you are building an Access database to replace an application create in another program such as Excel. Often you will need to import this data at least twice--once when you begin designing the database, so you can test forms, reports, and queries using real data, and a second time when your are ready to switch from the old system to the new one.
Even when you are extremely careful, exporting and importing information between database formats runs a serious risk of creating duplicate data sets. If you keep information about customers and product in Access and in a SQL Server database, whoever is responsible for data entry has to enter changes in two places, and it’s almost certain that some records will be out of sync or contain errors and inconsistencies. When you must use the same data in two different database programs, you should choose one program to store the data, and then create a link to that data from the other database program so that you can add or edit records or run queries. Because Access can link to data stored in a variety of formats--including dBase, SQL Server, and Paradox--you will most often want to store shared data in another program and create links to it from Access.
In other cases, your need for Access data is strictly temporary. If you’ve created a report or query in Access, you can transfer the data to Word to incorporate it into a larger report, or sent it to Excel, where you can easily analyze it with the help of PivotTables and charts.
The simplest and efficient tools in this category are known as OfficeLinks; these shortcuts are designed to send Access data directly to another Office application. To use one of the three OfficeLinks options, first select a target object in the current database window, or open a form, report, or query’ then choose Tools, Office Links, or click the OfficeLinks drop-down list on the Access toolbar. This list includes the following three links:
• Merge It with MS Word -- This option sends a table of data to a new or existing mail merge document in Word. When the transfer is complete, you can insert fields from the Access table as merge fields in the Word document.
• Publish It with MS Word -- This option creates a text field on disk--in RTF format from a selected Access object, and immediately opens the file in Microsoft Word. This file might become the starting point for a larger business document or report.
• Analyze It with MS Excel -- This option creates a worksheet file on disk (in Excel workbook format) from a selected Access object, and immediately opens the file in Excel. In the worksheet environment, you can perform mathematical, statistical, and other analytical operations that might not be possible in Access. You can also make use of Excel’s versatile charting capabilities. If the report you start with include grouping, the resulting Excel worksheet will include subtotals as well.
You can also create a Word or Excel file by selecting an object in the database window and the choosing File, Export. In the resulting Export dialog box, you can choose a specific format (including formats for pervious versions of Office), and you can supply a nondefault name for the file that will be created. You might prefer this approach to the OfficeLink options if you need more control over the formation or the name of the resulting file.
Importing external Text Files
In the initial development of a Access database, the ability to import current data from another source can shortcut hours of data entry and database development time. However, external data is seldom in a acceptable database table format and will require some data manipulation. Data can exist in many different formats and repositories. Incorporating external data into a database is an essential time saving task. There is no need to rekey existing electronically stored data, you can just import it. Of course, Access can’t possibly read all of the different types of data formats that exist, but you can save data as a delimited text files in most applications. Delimited indicates that each section of data is separated or defined by some sort of special character. For example, the comma, the quote, and the space are very common delimiters. The data can then be interpreted from this file and imported into Access. This same method can be used to extract data from Access into other databases systems. Maybe your are going to import the results of a Web-based survey and then analyze the data to summarize the results.
To import a delimit text file
Figure 10
[pic]
1. On the File menu, point to Get External Data, and then click Import.
2. In the Import dialog box, in the Files of type drop-down list, click Text Files, and then navigate to the file you want to import.
3. Double-click the file, or Click the file, and click Import.
4. The Import Text Wizard opens, Click Next to accept a delimited text file.
5. Select the First Row Contains Field Names check box. The data is shown in the main pane of this page of the wizard. Click Next.
6. Click Next to indicate that you want to store your data in a new table.
7. Click Next to accept the field settings
8. Click No primary key, and then click Next.
9. Change the name of the table in you want, and then click Finish.
10. Click OK to respond to the information box that notifies you that the import completed successfully.
You not have a new table listed in the database Container that is named by your selection in step 9.
Figure 11
[pic]
Figure 8 shows you some of the current File types that can be imported into Access in addition to a delimited text file.
Viewing Your Data
Figure 12
[pic]
There are several ways to view your data. When you first create a table, you can use the Design View to set up the column or field names, data type and description.
After you have completed the process of defining the various fields within the Access database, you are ready to enter records. There are three different ways to generate a Access database table and the above example is the one used most often.
Figure 13
[pic]
When you want to enter records or view all of the data in your file (table), you may use the Datasheet View. Once again, this is one of several ways you may choose to view your table data. It is the one most often used because it closely resembles the normal Excel spreadsheet view of data. When you are creating a form, query, or report, you will also have access to that particular view. These view options are accessed from either the View Button on the Toolbar
Figure 14
[pic]
Or by double-clicking on the desired table icon within the Assess database form.
Creating a New Table
By default, when you open a new blank database you will Create your table in Table Design View. This is where the Field Names, Data Types, Description, and Field Properties are defined.
1. Select the Tables Tab (selected by default)
2. Click on the New button
3. Select Design View option
Field Name The field name is the heading for each field. It can be up to 64 characters long and can include spaces and some special characters; however, it is wise to keep the field name short, especially when you have numerous fields in your table. Spaces are often used to help define a fieldname. You should use the “underscore (_)” character for this because a space in a field name will cause problems when you are using the field name in other functions. Although it is possible to use some special characters as part of a field name, this is not a good practice. The use of upper, lower, or mixed case is optional. It is strongly suggested that what ever case you use be used throughout the various database table(s) you will be defining. There are several documented naming standards utilized by professional database designers and organizations.
The following URL will provide you with the naming standards for the National Park Service:
Data Type The Data Type option is used to specify the type of data stored in a table field. Each field can store data consisting of only a single data type.
|Setting |Type |
|Text |Text or combination of text and numbers that don’t require calculations. |
| |Maximum size is 255. |
|Memo |Lengthy text or combination of text and numbers. Maximum size is 65,535. |
|Number |Various forms of numerical data used in mathematical calculations. |
| |[pic] |
|Date/Time |Date and time values in various formats. |
| |[pic] |
|Currency |Currency values involving data with 1 to 4 decimal places in various formats. |
| |[pic] |
|AutoNumber |A unique sequential number assigned to each record. The number is automatically entered for each new|
| |record. Useful as a Primary Key. However, it may not prevent the actual duplication of data which |
| |is the primary function of the primary key. |
|Yes/No |A check box with Yes and No values and fields that contain only one of two values (Yes/No, |
| |True/False, or On/Off. Use the spacebar to place or remove the checkmark for this cell. |
|OLE Object |A linkage to an object in another file or table. |
|Hyperlink |Data stored as text and used as a hyperlink address. A path to a file on your hard drive, a LAN |
| |server, or a URL address. |
|Lookup Wizard |Creates a field that allows you to choose a value from another table or from a list of values by |
| |using a list box or combo box. Clicking this option starts the Lookup Wizard, which creates a lookup|
| |field. |
In addition each field name has various attributes (properties) associated with it to help define the specific field:
Saving a Table Design
Figure 15
[pic]
After a database object has been designed, it must be named and saved.
1. Click on the File Menu option.
2. Click on Save or Save As.
3. You will be asked if you want to set a primary key (if you have not done so already).
NOTE: You do not have to set a primary key. You can always set one later.
4. Select the drive (A: for disk) you want.
5. Name the file. You can use up to 64 characters and spaces but keep it short when possible.
NOTE: Do not use a period or type an extension to the filename.
Entering Data into Your Table
Figure 16
[pic]
1. Switch to Database View.
2. Enter the data into the appropriate cells. Notice this will look and feel the same as when you enter data in the cells within an Excel spreadsheet. However, there is a very subtle difference. In Excel input or changes to the spreadsheet are not saved until you specifically do a ‘save’ or ‘save as’ or exit the spreadsheet. In Access as soon as you leave the row you are working on, the data is saved to the database table. You can however ‘undo’ any changes or input to the current row you are working on. This subtle difference can be a blessing and it can be a monstrous ‘OOPS’.
3. Use the Tab key, the “mouse” or the keyboard “arrows” keys to move to the next cell; use Shift +Tab or the “mouse” to move back a cell.
4. It is not necessary to resave your table after data entry or data editing because Access does this for you. However, if you are doing a lot of data editing to current data, it may be desirable to make a backup copy of the current table prior to the actual data editing to protect yourself against the ‘OOPS’ principle. To do this:
1. Select the table you will be editing
2. Click on EDIT then Click Copy
3. Click on EDIT then Click Paste. You will be prompted for a table name. Enter any name that will allow you to remember that this is a copy table.
4. If you are successful with your data editing and you no longer need the copy table, you can click on the Copy Table and do an EDIT then delete to remove this table from the database. You can also click on the table and then click the delete key to do the same function.
Printing a Table
You can print your data either in Datasheet or Form View.
1. Select either Datasheet or Form View
2. Click on File Menu
3. Click on Print Option
4. If you want to set options for paper, graphics, orientation, etc., click on the Properties button
5. You can also print a specific range of data and select how many copies you want
6. Click OK when you are ready to print
Closing a Database
1. Click on File, select Exit OR
2. Click on the X in the upper right hand corner
SECTION II
Enhancing a Table
Using the Format Menu, you can do the following:
Figure 17
[pic]
1. Change the Font size and type
2. Change the appearance of the cell (gridlines, background, effect) and the text within the cell (color)
3. Change Row Height (can also click and drag row height)
4. Change Column Width (can also click and drag column width)
5. Rename a Column
6. Hide or Unhide Columns
7. Freeze or Unfreeze Columns
Creating a Form from an Existing Table
Figure 18
[pic]
The New Object Button can be used to create a new form in various formats or to generate a special ChatWizard or PivotTable your existing table. You also have the option of using the create form in design view icon or the create form using the form wizard icon located on in the table window.
Figure 19
[pic]
The AutoForm option automatically creates a new form using the fields and data of the selected table. In this example the AutoForm: Columnar format has been selected
Form Design View and Other Form Options
Figure 20
[pic]
With a form open in view mode, you can click on the form header to open a form options popup menu. From this menu, you have several options for modifying the current form.
Figure 21
[pic]
Figure 22
[pic]
Adding, Deleting, Moving, and Modifying Fields
Figure 23
[pic]
Once you are in the Design mode you have several options to add, delete or modify the appearance of the fields within the form. Each field will have an attached label which be default will be the fieldname you choose when designing or importing you table data. This label can be detached, modified, or renamed as desired. The actual field can also be modified using the various formatting options from the format toolbar. It can also be moved, stretched or shrunk via the use of the mouse. Click on the field and then select one of the field anchors.
Figure 24
[pic]
Use the Toolbox popup menu to add new objects to your form
Using Shortcut Menus
Figure 25
[pic]
Commonly used commands for selected objects, controls or data may be found on the Shortcut Menu that is displayed when you right-click on the mouse. This is a quicker way to access menu options. All shortcut options are found within the Menu options.
Hiding/Unhiding Fields
You also have the option to Hide/Unhide fields within your form. However, this is not recommended. If you no longer need a field, it is better to remove if from your form. If you determine later you do need this field, you can add it back via the toolbox popup menu.
Edit, Add, and/or Delete Records (can be done in either Datasheet or Form View)
Figure 26
[pic]
Edit a Record You can add or remove text by clicking on the location and inserting the new text or by pressing the delete or backspace key to remove text. Pressing the Esc key will cancel any changes made in a field and return it to the original data. Remember when you move to a new record, any changes made to the previous record will automatically be saved to the database.
Figure 27
[pic]
Add a Record: Records can be added but only at the end of the existing records. Records can be rearranged later by using the Sorting option. In most cases records will automatically be rearranged when you close the form by the primary key of the table.
Figure 28
[pic]
Figure 29
[pic]
Delete a Record: Right mouse click within the black tab area of the record you want to cut or copy. This selects the entire record. Click on the desire option. In a will designed database records from a master table may not be cut [deleted] if there are child records associated with the master records in other tables. Example, you can not delete a customer record from the customer table if there are invoice records associated to the customer record you are trying to cut. Access will popup a error dialog box to inform you of this and not allow for the deletion of this master record.
Figure 30
[pic]
Find and Replace Records: You may need to find a specific record for editing or informational purposes. You can find records by searching the entire form or by selecting a specific field for the search. After opening the form, Form View, click on the Edit Menu and select the Find or Find and Replace option.
After completing the Find popup menu options, click on the Find Next button. Access will find the first record within the database table that meets the Find What option. At this point, you can edit the record and modify it as needed or by clicking on the Find Next button again move to the next occurrence.
SECTION III
Working with date in the Datasheet view of a table:
Figure 31
[pic]
Options related to the Datasheet
By clicking the right mouse button within the table header, you will open a popup menu of various options you can apply to the complete table. Three options are often used from this popup menu:
Figure 32
[pic]
Table Design: In this mode, you can:
• change Field names
• Add, remove, or modify table field properties
• Generate Lookup a table with values for a specific field
• Change the Data Type and/or its value
REMEMBER: Making changes to the Primary Key field(s) may or may not be allowed once table relationships and data integrity rules have been applied. Access will notify you if you are affecting table relationships or data integrity rules.
Figure 33
[pic]
Cut [Delete] a specific record(s):
By holding down the shift key and dragging the mouse through a block of records you can select multiple records for cutting (deleting) from a table.
By holding down the ctrl key and clicking on individual records you can select a set of records for cutting (deleting) from a table.
Make modifications to the layout and format of the Datasheet
WARNING: If you select a record for cutting that has associated child records in another table, you will not be allowed to delete this primary record unless special data integrity rules have been turned on. [Cascading delete and/or Cascading update]
Figure 34
[pic]
Datasheet
Selecting the Datasheet option will allow you to Sorting allows you to rearrange the information so that you can look at it in a different way. Remember these datasheet changes will apply to the overall datasheet.
Special Note:
The field to the far right of this table with the “+” sign tells the user that there is a special popup datasheet associated with this datasheet. It will usually be the associated child records from another table within the database.
Changes to a Specific Column within the Datasheet
Figure 35
[pic]
Right click on the column header to open the column popup menu options.
The following options are often used from this method of working with data in the datasheet view:
Figure 36
[pic]
Sort Ascending or Sort Descending:
By default tables are usually sorted in ascending order based on the primary key field(s). However, by clicking on the Sort Descending option, the table will be sorted in descending order of the selected column.
Figure 37
[pic]
Hiding Column(s) Notice that the Company Name field is no longer visible within the current datasheet. By selecting the column(s) to hide and clicking the Hide Column command, the selected columns will be hidden from the datasheet view.
Figure 38
[pic]
Figure 39
[pic]
Figure 40
[pic]
Freezing Column(s):
When working with a large table with a lot of different columns, the use of the Freeze Column options can be a very effective method for data entry or data editing.
Notice: You can NOT freeze disjointed columns. They must be adjacent to eachother.
Figure 41
[pic]
Column Width
There are two methods of changing the column width. The first and most often used method is to click on the column header. Move the mouse to the far right edge and drag the column to its new desired size.
The second method is much more accurate because you will be defining the new column width from a column width popup menu when you click on the Column Width option
Figure 42
[pic]
Notice this popup will allow you to modify the actual width (user inputted), select the default standard width, or do a Best Fit.
Find Option: The find option is not any different then selecting using the find option from the Edit menu drop down. It is not specific to a column of data. It is just another way to find data within the table.
Figure 43
[pic]
Delete Option: When you select the Delete Option, you will be presented this popup to verify that you really want to do this. If you select yes, you will be modifying the database table and this process may affect child tables. It there are data integrity rules in effect for this column, you may or may not be allowed to delete the column subject to how the cascading delete and/or cascading update rules have been applied.
WARNING: Before performing any table field deletes it is a normal Best Practice rule to backup the database.
Figure 44
[pic]
Rename a Column:
When you select the Rename option the column header will be highlighted. Type in the new Column name and click the enter key. The Column will be renamed in the underlying table. If this column is used as a foreign key in another table, the foreign key table name will NOT be changed.
NOTICE: The key to relationships between tables is not the name of a field but the field type.
SECTION IV
Query Introduction
Queries enable you to extract a subset of data from a single table, from a group of related tables, or from other queries, using criteria you define. By saving a query as a database object, you can run the query at any time, using in the current contents of the database. When you display when you display a query in Datasheet view, it looks exactly like a table; the critical difference is that each row of the queries results can consist of fields drawn from several tables. Queries can also contain calculated fields, which display results based on the contents of other fields.
CAUTION
Using Queries to Extract Data from a Database
Queries are database objects that enable you to extract data from a database to use in another way--as the source of data a used in a printed report or to produce a list of items for use in a look up control on a data entry form. A query can be based on a single table or on multiple related tables. In addition to field drawn directly from tables, a query can also contained calculated fields that transform data--adding sales tax to an invoice amount or performing statistical analysis(totals, averages, and the like) on group on groups of records drawn from multiple tables.
Like other office wizards, the access query wizards are efficient at guiding you smoothly through the steps of a complex process, providing detailed explanations of the chores you need to make, and enabling you to view graphic representations of the results.
Although design view is a more versatile environment in which to create queries, it’s often easier to begin by using a wizard to create a basic query. After you finish with the wizard, you can then open the query in design view to modify the results.
To view a list of available quarry wizards, press at 11 to open the database window, and then choose Insert, Query. The New Query dialog box lists four query wizards. Double-click any of the options to launch a wizard.
TIP
Choosing the Right Query Type
Access enables you to create several types of queries. The most common is a select query, which extracts information from one or more tables. You can also create Crosstab quarries, which group and summarize information in row-and-column formats such as an Excel PivotTable. Some of the most powerful (and potentially dangerous) things you can do with access involved action queries, which actually change the data in the underlying table based upon criteria you defined in the query.
Like queries, filters enable you to work with a subset of records in a database. Filters offer a quick way to temporarily limit the display of records in Datasheet or Form views. You can create a filter by entering data in a form or by making a selective in datasheet view. Even though the display of data is filtered, it still represents live data, not a separate copy as in a report. If you enter changes in a Datasheet view based on a query, Access changes the data in the underlying table.
TIP
Figure 45
[pic]
To create a new query, choose Insert, Query, or choose Queries in the database window and then click the New Button. The New Query dialog box lets you start from scratch in Design view or use a wizard to build one of several specify types of queries.
SELECT QUERIES
When you use design view to create a new query from scratch, access creates a select query a default. As the name implies, the purpose of select query is to gather data from want or more tables, and to present it in a format that you save as part of the query itself. Select queries can include any of the following elements:
• Fields drawn from want or more tables or queries. You can base a query on another query, a comment to make when you want to create a summary view of data gathered from multiple tables. Access uses declined relationships to match records from different sources and a find relevant connections between the data. You can also define a new relationship between tables and/or queries and define them as part of the query.
• Calculated fields, which display the results of expressions, using fields from want or more source tables.
• Totals, which performs the statistical operations, such as some an average, on fields from a source table.
• Selection criteria, which the buying the specific set of records the query will return. For example, in an invoice is table, you might define criteria for the InvoiceDate field to return only invoice is prepared in the past 30 days.
• Sorting instructions, which arrange the query results in numerical, alphabetical, or Chronicle order by want or more columns.
• Hidden fields, which are included for the purpose of defining criteria or sorting instructions, but are not actually shown in the query’s results.
Figure 46
[pic]
When you save the query, you save the instructions for achieving and display and records from the database, not the records themselves. As a result, running and saved quarry always display is the current dataset.
Notice the last column, which contains a calculated field that multiplies the retail price by the current quantity on hand for each product. The label in front of the formula in the third column defines the name of the calculated field. Use a colon to separate the label from the formula used to calculate the field results.
Figure 47
[pic]
In Design view, a query includes two panes: The top pane contains field list for each table and query used as a data source; this pane also show relationships between the data sources. The lower pane contains a grid with one column for each field that makes up the query. When you design a query, you can drag any field reference directly from the lists in the upper pane, or you can choose from drop-down list that appear when you activate a given column in the grid. (Double-click a field name to quickly add it to the grid.) The You can enter calculated columns manual or with the help of the Expression Builder.
• The first step in using the expression builder is to click on an empty column in the lower pane (grid).
• Then right click the mouse to open up the option menu as shown.
• Select the Build option to open the Expression Builder wizard.
In the rolls below each field name, you can see specifications that explicitly determine the content of the query:
• The Table row shows the source of each field. This row is visible by default.
• The Total row lets you specify operations to be performed on that field -- Sum, Average, and so on. This row is normally hidden. The default selection is Group By, which displays all values in the selected field without performing a calculation.
• The Sort row specifies whether a particular column will be used for sorting, and is so, whether the sort is in ascending or descending order. If you specify a sort order in multiple columns, Access sorts by each column, going in order from left to right.
• The Show row contains a check for each field that will be displayed as part of the query’s result. Clear this check box when you want to use a field for sorting or filtering but you don’t want it to appear in Datasheet view.
• The Criteria rows contain one or more criterion expressions for determining which records will be included in the query. Example: today() - 30 would show only records for the past thirty days. Today() is a built-in function.
Figure 48
[pic]
The Expression Builder dialog box [wizard] is somewhat complex and will require some experimenting to learn how to use it effective.
Note: Simple rules for building calculated fields:
Function Format - Sum((«expr»)),Avg («expr»)
Field Names - [UnitPrice], [UnitsOnHand]
Arithmetic Operators - +,-,*,/
Comparison Operators - =,>
Logical Operators and, or, like not like
String Operators & concatenation system
Put it all together for a simple calculated field:
Inventory: Sum([UnitsInStock]*[UnitPrice])
CROSSTAB QUERIES
Another kind of query, known as a Crosstab, transforms record-oriented data into a summary view that resembles an Excel PivotTable.
Figure 49
[pic]
Step One: Select Crosstab query wizard from the New Query dialog box and then select the table, row headings, column headings and function. The last step will be to provide a name for this query when you click on the next button. Access will provide a default name you can accept or modify as desired.
Figure 50
[pic]
In the Design window of a Crosstab query, select fields for row heading, column headings and values. The Crosstab Query Wizard fills in these values automatically. However, creating a Crosstab query from scratch in the Design window is fairly straightforward process, but it’s much easier when you use the Crosstab Query Wizard.
PARAMETER QUERIES
Normally, saving a query includes all the criteria you’ve defined for that query. If you want to see all sales results by product for all vendors, it’s easy to save a query that extracts those results from the current contents of the database each time you run the query. But what do you do when you want to specify slightly different criteria every time you run the query? For instance, what if you want to enter a specific vendor number or a maximum price when you run a query? For that task, you need a parameter query.
Each time you open a parameter query, Access displays a dialog box asking you to enter a piece of data to be used in the selection criteria for the query. You define the input prompt as part of the query’s definition.
Figure 51
[pic]
To create a parameter query, open the query in Design view and click in the Criteria box for the field in which you want to add selections criteria. The expression should include the text you want to display as the input prompt, enclosed in square brackets where you would normally enter a constant value. For example the query shown to the right prompts you to enter the minimum retail price you want to use as the selections criterion in a select query.
The value the user enters into the prompt becomes the parameter in the expression, which in this examples specifies a selection criterion for the query of $1.00 dollar.
The simplest parameter queries include a single value, but you can also use wildcards or multiple parameters as part of a query. The following examples should give you some ideas:
• To create a input dialog box that prompts the user to enter the beginning of a search string, use an asterisk with the parameter. For instance, entering like [Enter beginning of product name & “*” will search for all records in which the specified field begins with the value of user inputs.
• To search for a string anywhere in a given field, use two asterisks: Like “*”&[Enter any text that appears in the product name]&”*” will do the trick.
• To define a beginning and ending range of numbers or dates, use two parameters in a single expression, such as: Between [Enter beginning date] And [Enter ending date]. When you run a query with multiple parameters, Access displays an input dialog box for each one.
ACTION QUERIES
An action query potentially changes the data in an existing table, or creates a new table. Access enables you to create four kinds of actions queries:
Figure 52
[pic]
• An update query replaces data in existing records. In the designing of an update query, you write selection criteria to identify the target records, and you prove an expression that generates the replacement data. Use an update query to change a group of records at once--when an area code changes, for example, or to make an across-the-board price increase. The example shown shows the properties of an update query that include a parameter: It adds 5% to the amount in the UnitPrice field for all records that match the supplier code you enter.
[pic]
• A make-table query creates a new table object from the result of the query itself. For instance, you might build a query that produces a list of all customers who have not ordered product from you in the past year and copy those records into an Inactive Customers table. This type of query does not affect the underlying source data. You can choose a table from the current database or from another database file. If you enter the name of a table that does not currently exist, access creates it for you.
• An append query adds new records to an existing table from a source query. This type of query is most commonly used when importing data from an external source. Append queries enable you to take some or all of the imported information and move it into an existing set of tables in the correct format. When you choose Query, Append Query, Access prompts you to enter a table name by using a dialog box that is identical to the one for a make-table query.
• A delete query removes records that match specified selection criteria for an existing table. You might use a delete query to prune outdated records from a database. When you create a delete query, Access adds a delete row to the query’s design grid.
TIP
CAUTION
Following are two fail-safe mechanisms that can prevent a data disaster:
• Before you begin designing an action query, create a copy of the table you intend to modify. In the database window, select the table icon, press Ctrl+C to copy it to the Clipboard, and then immediately press Ctrl+V . In the Paste table as dialog box, enter a name such as Copy of Tablename, choose the structure and data option, and click OK to create the copy. If the action query does not work properly, you can return to the original state by deleting the modified table and renaming the backup copy the same name as the original.
• Before running an action query, test its effects by designing a select query that uses the same selection criteria. When you open this query in Datasheet view, inspect the results carefully; because these records will ultimately be the same ones that change as a result of the action query. After inspecting the results, return to Design view and choose Query, followed by the menu choice for the type of query you want to create. This will not guarantee success but it might point out a logic error.
• If the selected table has referential integrity rules applied to it, some action queries will not work as a result of the desired action. For instance, if you are trying to remove old records from the customer table that still have child records in the invoice table, Access may not allow for the deletion of a parent record until the child records have been deleted first. The exception would be if the cascading delete option was turned on within the parent table referential integrity definition.
CAUTION
DEFINING QUERY PROPERTIES
In addition to the settings discussed so far, which are available in the design grid for a query, you can also adjust a host of settings that apply to the entire query. Open a query and switch to Design view. If necessary, click the Properties button to display the Properties dialog box, and then click anywhere in the background area of the query design window.
Figure 53
[pic]
[pic]
•
• Top Values - Returns a specified number or percentage of records. This setting is most often used in conjunction with a sort setting; to see the 10 most expensive products.
• Unique Values - Returns a query result in which no duplicate records exist. Choose Yes if you want to extract a unique set of values from a database. Access eliminated duplicates from the result set based on records visible in the query’s result, not on the contents of the underlying table or tables.
• Unique Records - Returns a query result after eliminating duplicate records in the data source. Depending on the fields you choose to display, you might see duplicate values in the query results.
• Column Headings - This property, used only in Crosstab queries, lets you limit the columns to be displayed. Separate entries with semicolons. In a data source that contains a Regional Office, field, for example, you might specify East;West;Midwest in this property. Access ignores all other values when performing the Crosstab query and displays these three columns in the specified order.
• Output All Fields - Specifies that you want the query to return all fields from all tables included in the query, regardless of whither the field name is on the design grid or the Show box is checked. When you set this property, you need to add fields to the grid only to set Criteria and Sort properties.
• Link Child Fields, Link Master Fields - Used to set the relationship between a main form and a subform or other embedded object. Normally, Access sets this property automatically based on relationships you define between the tables.
Saving a Query
1. Click on the File menu and select the Save or Save As option.
NOTE: Any new data that is later entered into a table and that meets the criteria will appear in the query.
Using all Fields of a Table for a Query
There are three ways of selecting all fields of a table for a query:
• The first way -When a selected table appears in the top pane of the Query Design Window, the first field option is the asterisk (*) which represents all fields in the table. If this option is selected, only the table name will appear in the lower pane.
• The second way - Click in the table name header (blue area) and hold the mouse button down. Now drag the table name to an empty grid column. Release the mouse button and all fields within the table will be added to the grid. If there is a field you do not want, click on the area just above the field name [column should turn black] and hit the delete key.
• The third way - Select the Output ALL fields from the query properties menu. Remember this will select ALL fields for all tables defined in the query. They do not have to be displayed within the query grid.
Moving a Column in a Query
1. Click on the Datasheet View of the Query.
2. Select the column by clicking on the field name (column will be highlighted).
3. Click again on the field name holding the mouse button down.
4. Drag the line to the vertical grid line that will become the new left boundary of the moved column.
5. Release the mouse button to insert column in new location.
Renaming a Query
1. Click on the Query Tab
2. Highlight the name in the Query database window list
3. Click again to activate the edit mode
4. Type the new name
Printing a Query
A query can be printed in the same manner used to print other database files. The printout will include both a header, containing the query name and current date, and a footer containing the page number.
SECTION V
Building Great Forms and Reports
Access provides a design and development environment that gives you extensive control over the appearance and functionality of forms and reports. The tools you use for either task are remarkably similar. The differences typically reflect the different design goals of forms and reports, as explained here:
• A form is a formatted database object, generally used to display one record at a time in an onscreen window. Forms are most commonly used to create convenient the until then, the blanks windows for entering or editing data symbol, in this case, you use a table or query as the data source for the form.
• Access reports typically organize data in a format suited for printing or publishing. Although you can also using a form to view data onscreen, reports are better suited for this task and often represent the most important in product of a database
When designing a form or report, you start by specifying a data source--one arm one or a one or more tables or queries, or as stated written in SQL--and position controls on a design. Although you can create a report, using a wizard is often a better starting point. The wizard produces the best structure, and you then open the form or report in design view to make detailed changes to its content and appearance.
As with other database objects, Access provides several ways to create a form or report. After selecting Forms or Reports from the database window, click to tilt the New Form or New Report dialog box. The option shown here let you create a new form or in Fort by opening a blank form in design view or by choosing wizards and AutoForm/AutoReport options that enable you to create a default form or report with a single click.
To open an existing form or report in Design view, selected from the database window and click the Design button. If the form or report is already opened, click the Design button to switch into Design view.
DESIGN 101: WORKING WITH CONTROLS
The building blocks of any form or report are object objects called controls, which include text boxes, labels, option buttons, list, command buttons, toggles, and other familiar Windows interface elements. Controls have their own property settings, as do individual sections of the form or report; by changing the settings of these properties, you can modify the appearance and content of the form or report. Controls can take any of three forms.
• Some control some controls are directly tied to a field in a table or query. In a peculiar jargon of Access, these are called bound controls. When you enter data in a control that is bound to a particular field, Access adds the data to that field; when you view data by using a form or report, Access checks the Control Source property for each control to see which data is should display.
Figure 54
[pic]
Figure 1 shows the Properties dialog box for a text box bound to the CatagoryName field in the underlying table.
Two additional dialog boxes are also shown:
o Product by Category field names
o Form/Report ToolBox controls dialog box.
• Some controls are unbound--that is, not tied to any data source. For example, a line, box, clipart, picture, or freestanding text label is an unbound control.
• When you enter an expression in the Control Source property box, Access creates a calculated control. The expression = [SupplierPrice]*2, for example, multiplies the contents of the SupplierPrice field by 2 and displays the result.
When you open a form or report in Design view, you can change the font, font size, color, borders, and other formatting properties of any control. In Design view, Access lets you position controls in a grid for precise alignment. You can also group and align controls.
When you work with a form or report in Design view, three interface elements are essential:
• Use the Toolbox to add new controls or change existing controls. You can customize the Toolbox buttons just as you can any other Office toolbars; to toggle it on or off while you work, click the Toolbox button or choose View, Toolbox.
• The Field List displays a list of all the fields in the source query or table, which you can use to add new controls. To show or hide this list in Design view, click the Field List button or choose View, Field List.
• To adjust the appearance or behavior of a control, section, or the form itself, open the Properties dialog box. You can keep the Properties dialog box open while you work; as you select different objects, the properties displayed in this dialog box change to reflect the available choices.
TIP
ADDING A NEW CONTROL TO A FORM OR REPORT
If you drag a field name from the Field list onto a form or report, Access automatically creates a text box control bound to that filed. If you click another Toolbox button first, and then drag a field onto the form, Access launches a wizard that create the control type you selected. Figure 2, for example, show one step of the Option Group Wizard. Follow the wizard’s instructions to define the data source and behavior of the control.
Figure 55
[pic]
If the Control Wizards button on the Toolbox is selected when you add a new control to the form or report, Access lets you fill in the control’s properties with a wizard
The Toolbox contains buttons for common controls you might want to add to a form or report. By positioning the mouse pointer over a button in the Toolbox, you can view a ScreenTip that show the name of the control itself. Some of the more common and useful controls include the following:
• Check boxes, which let users enter data in a Yes/No field or an option group.
• Combo boxes, which let the users enter data from a drop down data list
• List boxes, which let the users enter data from a data list.
• Labels, which add descriptive text to a control or a form or report.
NOTE
POSITION CONTROL AND LABLES
When you use the Form Wizard to build a form, the default type for all controls is a text box with a label attached to its left. In some cases, however, you’ll want the labels to appear above the text box, and you might want to change the position, alignment, size, or grouping of controls on the form. After you learn the secrets of working with Access controls, you’ll find it easy to position controls precisely where you want them. It does take some practice.
Figure 56
[pic]
Access display handles -- small black rectangles -- around the outside of a selected control, as shown in Figure 3; These handles are a visual indication that you’ve selected the control. The eight small black squares are size handles -- you’ll find one on each corner and one in the middle of each side of the selected control. When you position the mouse pointer over a size handle, it takes the shape of a double-headed arrow, at which point you can drag the handle in any direction to change the size and shape of the control itself.
Note that both the control and its label are selected. The “open hand” pointer means that you can move both the control and the label at once.
The large black square at the upper-left corner of a selected control lets you move the object to a new position within the form. When you drag this handle, the mouse pointer takes the shape of a hand with one finger pointing up; as you move the selected control, you see only its outline, making it easier to position on the form grid. When you release the mouse button the control itself moves to the location you’ve selected.
For the most part, every control you add to a form or report actually consists of two controls; the bound or unbound control (text box or combo box, for example), and the matching label. If you know the techniques, it’s easy to position these controls correctly.
• Use the large square in the upper-left corner of the control or the label to move either one independently. This technique is effective if you want to move a label from the left of a text box so that is sits above the control.
• To move both the control and its label at once, point to any border of the control or the label, until the mouse pointer takes the shape of an open hand. Drag to position the control-label combination in its new location.
TIP
Figure 58
[pic]
Special Note: The label associated with a control on a form or report will always be the FieldName from the underlying table or query unless you have defined a Caption name in the Field Properties section of the Field Name in the underlying table.
MAKING FORMS EASIER TO USE
A well-designed form makes data entry easier and more accurate, especially when you want other people to enter data into a database. By limiting the data the user sees, carefully arranging input boxes and providing explanatory text, you can guide the user through the data-entry process. Often the use of a combo box or list box using a lookup table or predefine list will help maintain data integrity within a given field of data.
A form can include as many as five sections. The data itself typically appears in the Details section; in addition, each form can have up to two headers and two footers, which one header/footer combination for the form itself and another for individual pages. Use the View menu to hide or show headers and footers.
Figure 56
[pic]
Simple forms generally show the contents of one record at a time, but you can also design a form that include a subform, which displays information from a related table or query. If you choose a table that includes a subdatasheet and then create an AutoForm, Access adds a subform automatically. Using this type of form, you can scroll through groups of records, or search for information by using filters and other search tools. Figure 4 shows a form and subform combination in which the main forms is bound to the Customers table and the subform is bound to the related Products table. Use the navigation buttons to move through the list of orders.
MAKING REPORTS EASIER TO UNDERSTAND
Access reports are organized into horizontal sections that are laid out in a specific order. Understand how to work with each section is a crucial step in designing an effective report. Figure 5 show a basic report that illustrates some commonly used sections.
Figure 59
[pic]
Each section in an Access report contains a different type of data. Headers and footers set off groups of data and pages; items you place in Details section repeat as needed.
• The Report Header and Footer appear at the beginning and end of the report. A report header often include the title of the report and a calculated control that contains the expression =Date() to display the date the report was printed. Report footers often contain grand totals or averages for the data within the report. To hide either section, change its Visible property to No.
TIP
• The page Header and Footer appear at the top and bottom of each page, even if the Detail section is a continuation of data from the previous page. Page headers are commonly used for column heading, so readers can follow a lengthy list, and pages footers are useful for dates, and page numbers.
TIP
• Group Header and Group Footer sections appear automatically when you define grouping and sorting options for a report. By placing calculated fields in either of these sections, you can display summaries of the data within each group.
TIP
Creating a Report with the Report Wizard
Figure 57
[pic]
1. Open the database that you want to use to create your report.
2. Select the Reports tab in the Database window.
3. Select New and then select Report Wizard
4. Select the type of report you want to generate.
5. Select the data source, either a table or a query
6. Click the OK button to move to the next Report Wizard page which will be different depending on the report type you choose
7. Respond to the questions For example the standard Report Wizard would ask you to respond to the following:
• Fields - Highlight the field and click > to move fields across (click >> to move all fields)
• Grouping levels – subtotals or subsets
• Sort order – if you want to sort on any fields
• Layout, Orientation, and Column Width setting
• Style – for the report
• Report title and print options
6. When you are finished, the report will be displayed in Layout Preview.
NAME ___________________________
EXERCISE 1A - ACCESS
PART I: Bit-Byte Computer Stores, Inc. has opened numerous branches throughout the northwestern U.S. during the past year. In order to keep track of the branches and related information, you have been asked to create a database to organize this information.
1. Open up Access and create a new Database. Name it COMPUTER.
2. Select the option to create a new table.
3. Using Design View, create a table design using the data listed below:
(Allow Store Number to be the primary key - NOTE: A primary key must be a unique number.)
|FIELD NAME |DATA TYPE |SIZE |DESCRIPTION |
|STORE_ID |Text |8 | |
|BRANCH |Text |25 | |
|TITLE |Text |50 |Title of Software Used |
|TYPE |Text |25 |Type of Software Used |
|PRICE |Currency |Use Fixed Format |At Time of Purchase |
|PURDATE |Date/Time |Use Short Date Format |Date Software was Purchased |
NOTE: As a general rule, it is better to make text fields larger then expected then to later have to readjust a field name text data type length because a new record is being truncated. Access will only store the actual text length within the database.
1. Save the design under the name SOFTWARE.
2. Listed below is the data for the SOFTWARE data file. Enter the information.
|STORE_ID |BRANCH |TITLE |TYPE |PRICE |PURDATE |
|1110 |Computers Etc. |Word-O |Word Processing |499.95 |8/17/03 |
|1111 |Zippy’s |Pagecreator |Desktop |399.95 |1/30/03 |
|1120 |Your PC Center |Micro Words |Word Processing |459.80 |6/14/03 |
|1125 |Bits and Bytes Co. |InfoBase |Database |495.95 |3/20/03 |
|1130 |Lakeview Computers |Word-0 |Word Processing |499.85 |5/15/03 |
|1135 |Pacific Supply Co. |Accessor |Database |550.50 |11/23/03 |
|1140 |Gary’s PC’s and Such |Exceller |Spreadsheet |564.20 |2/20/03 |
|1141 |Software Galore |OfficeMate |Integrated |479.95 |3/15/03 |
|1150 |Wheatland |Quattrain |Spreadsheet |475.50 |12/15/03 |
|1160 |Tower Cities Computers |BBS |Communications |115.50 |4/2/03 |
NOTE: If you are inputting a date into a data type date/time format, you do not have to input the year if the year you want is the current year. This is the same for Excel. However, if the year is not the current year, you will need to remember to input it. The actual input can be in almost any format and Access will reformat to the data type format you set when you designed your original table. You could also set an ‘input mask’ to force the user to utilize a specific format.
3. Proofread for errors, and print a copy.
NAME ______________________
EXERCISE 1B - ACCESS
PART I: Bit-Byte Computer Stores, Inc. needs to maintain an address list for all of its branch offices. Create a table to list this information.
1. Open up Access and select the COMPUTER database.
2. Select the option to create a new table.
3. Create a Table in Design View using the data listed below:
|Field Name |Data Type |Size |Description |
|STORE_ID |Text |4 | |
|ADDRESS |Text |50 | |
|CITY |Text |25 | |
|ST |Text |2 | |
|ZIP |Text |5 | |
|PHONE |Text |10 |Input Mask – phone number |
| | | |Use default placeholder and save without |
| | | |symbols in mask |
5. Set STORE_ID as the primary key for this table.
6. Save the Table as ADDRESS.
7. Enter the following data into the table:
|STORE ID |ADDRESS |CITY |ST |ZIP |PHONE |
|1110 |235 11th Avenue |Berkeley |CA |90211 |(617) 245-8451 |
|1111 |8007 Desmore Avenue |Los Angeles |CA |90151 |(232) 545-1764 |
|1120 |4516 Rocky Ravine |Phoenix |AZ |85295 |(660) 716-3121 |
|1125 |551 S Beverly Glen Road |Los Angeles |CA |90151 |(232) 544-6783 |
|1130 |3202 Oaksdale Road |San Diego |CA |90175 |(245) 578-9912 |
|1135 |401 Regency Court |Oceanside |CA |90457 |(276) 776-0908 |
|1140 |720 Independence Way |Mesa |AZ |02142 |(615) 247-9812 |
|1141 |1499 Doveland Drive |Denver |CO |60514 |(864) 451-0096 |
|1150 |1453 Mirageo Lane |Phoenix |AZ |85296 |(660) 714-2820 |
|1160 |1313 Stewart Avenue |Boulder |CO |60577 |(887) 433-6869 |
8. Proofread for errors, and print a copy.
NAME _______________________
EXERCISE 2 - ACCESS
PART I: There have been some changes in a number of the offices. Update the information in both the SOFTWARE and ADDRESS tables:
SOFTWARE: Software Galore has decided to change their software type. Enter the following changes:
Change Title to Word-O
Change Type to Word Processing
Change Price to $545.00
Change Date to 1/1/99
ADDRESS: Store# 1110 (Computers Etc) has moved to another office. Enter the following changes:
Change Address to 4500 Grandview Drive
Change Zip to 90215
PART II: Bit-Byte Computers is growing. Add the two new companies to both the SOFTWARE and ADDRESS tables
|STORE#: |1165 |STORE#: |1170 |
|BRANCH: |Printers and Such |BRANCH: |Bean Town Computers |
|ADDRESS: |765 Main Street |ADDRESS: |156 14th Avenue |
|CITY/ST: |Denver, CO 60514 |CITY/ST: |Seattle, WA 99465 |
|TITLE: |InfoBase |TITLE: |Exceller |
|TYPE: |Database |TYPE: |Spreadsheet |
|PRICE: |$529.99 |PRICE: |$599.00 |
|PURDATE: |1/15/99 |PURDATE: |1/15/99 |
|PHONE: |(664) 451-2285 |PHONE: |(506) 568-1935 |
PART III: The Bits and Bytes Co. has had financial difficulties in the last few years so the company is closing the store. Remove this record from both databases.
PART IV: Bit-Byte Computer Store would like a printout of data in the SOFTWARE table. They would like it displayed in Form View.
1. Select the Forms Tab
2. Create a New Form using Form Wizard option
3. Use the SOFTWARE table for the form data
4. Select the following fields for the Form:
BRANCH TYPE TITLE
5. Choose the Columnar display option
6. Choose the Clouds background
7. Name the Form SOFTWAREFORM
8. Click on Print Preview to view all the records
9. Select the File menu, then Page Setup, the Column Tab, and change columns to 2
10. Preview records again. Print a copy
There are No exercises associated with Section III.
However, it is suggested that you:
• Backup (make a copy) of your current database
• Open the backup copy
• Make several datasheet changes to at least one table
• View and understand the results of these actions
• Takes some notes for later reference. Remember if you do not understand what to do or what happen use the ASK A QUESTION or the Office Assistant for help.
NOTES:
________________________________________________________________________________________________________________________________________________________________________________________________________________________
________________________________________________________________________________________________________________________________________________
________________________________________________________________________________________________________________________________________________
NAME _____________________________
EXERCISE 4 – ACCESS
PART I: Bit-Byte Computer Stores, Inc., would like a printout of the companies branches, addresses, and what type of software they are currently using.
Using both tables, create the following query:
1. Create a table with the following field.
STORE
BRANCH
ADDRESS
CITY
STATE
ZIP
TYPE
2. From Datasheet View, Click on File menu, and select Page Setup option.
3. Click on Page tab and change paper orientation to Landscape.
4. Save query as STORE1.
5. Print a copy.
PART II: The company would like reports listing specific information. Create the following queries:
1. Create a table with the following field and criteria:
|FIELDS |CRITERIA |
|BRANCH | |
|STATE |AZ, CO |
|TITLE | |
|COST | |
|PURDATE | |
|PHONE | |
2. Set the state so that it lists data in ascending order.
3. Type AZ on the “criteria” row.
4. Type CO on the “or” row.
5. Save query as AZ_CO STORES.
6. Print a copy.
NAME ________________________
EXERCISE 5 - ACCESS
PART I: The Company would like a report printed with all of the branches and their address information. They would also like the information grouped by state and the stores listed in alphabetical order.
1. Click on the Reports Tab
2. Click New and then select Report Wizard
3. Use the following information to create your report:
• Use the query STORE1 as the source document
• Choose the following field:
BRANCH ADDRESS CITY ST ZIP
• Group by State
• Sort by Branch
• Accept the default layout – Stepped
• Accept the default orientation – Portrait
• Change the title option to Corporate
• Save and name the report Stores By State
After viewing the file, you decide to change the title and its layout. Click on the Design View and make the following changes:
1. Insert the word Computer at the beginning of the title. You may have to resize the textbox to fit all the text (click and drag on box sizing buttons).
2. Click and drag the title box to the center of design area.
3. Center align the title within the textbox.
4. Click on the View menu and return to the Layout Preview. Check for errors and appearance.
5. Save file as STORES BY STATE.
6. Print a copy.
PART II: The Company would like a report of all branches, grouped by type of software and sorted by the branch office name. Create a report of your own design that will display this information. Save and name the report SOFTWARE. Print a copy.
[pic]
Record Management Slide Presentations
• Records Management 2004.ppt
• Numeric Records Mgmt.ppt
• Alphabetic Storage & Retrieval.ppt
• Electronic Records.ppt
• Records Control.ppt
• Records Retention, Retrieval & Transfer.ppt
•
-----------------------
Anderson Bank John Anderson Anderson Bank Anderson Bank
450 Cedar Drive 115 SE Concord 4500 Cedar Ave 14th & Oak
Pittsburgh, PA Pittsburgh, PA Houston, TX Philadelphia, PA
Anderson Bank Anderson Bank Jeremy Anderson Anderson Bank
8th and Grand 150 Brentwood Drive 210 N Main 150 Brent Avenue
Philadelphia, PA Dallas, TX Dallas, MA Dallas, TX
Primary Table One with a primary key defined and shown in bold letters.
Mid table with a primary key defined (shown in bold letters.) Two foreign keys from the first and second primary
Foreign Key
.
Be careful when working with queries. Queries typically contain live data. If you change data in a query Datasheet or form, it changes in the underlying table as well without any warning.
Access reports transfer well to other Office applications, when you can use more powerful editing and analysis tools to create good-looking documents and charts. For example, buttons on the Access toolbar enable you to export a report to Microsoft Word, edit the page, and then print it or save it on a Web server. You can also sent a report to Excel, perhaps to create a chart or PivotTable report for use in another document.
When you use a wizard to create the initial version of an Access object, always expect to switch over to Design view to fine-tune the result. A wizard seldom produces the exact object that you need; in particular, the Design views for Forms and Reports offer direct Access to important design elements such as controls, properties, fields, sections, and groups--and enable you to make detailed changes in the appearance and behavior of database objects.
All version of Access since Access 2000 are “smart” about handling changes to the names of objects. When you rename a field in a table, Access automatically changes any reference to that field in queries, forms, report, and other objects. If you change the name of a field, you shouldn’t need to edit any other objects. However, any captions that reference those fields on a existing form or report are unchanged.
WARNING: When using logical operators within a query, form, or report it is very important for you to have a good idea as to what the resulting dataset should be. Assuming you have used the correct operand can often result in bogus data.
Description is optional, but it helps you identify the field and can be viewed by the user. The description can be up to 255 characters
Field name Properties
Be sure to select the table you want to use for this AutoForm.
Clicking on the AutoForm option would allow you to change the overall appearance of the form
Clicking on the Form Design option would open the following view of this form.
AutoForm Option Menu popup
ToolBox
Field to be modified
Go to the last Record form button and click it.
Number of Records
New Record
New Record
First Record
Next Record
Last Record
Edit Menu
Result of Datasheet design changes.
Clicking on Format and then selecting Unhide will display this popup. Notice that Company Name does not have a checked box. This means that this field is hidden[pic][?] ( ) * + F G H I s ôíÔÀ¯žˆwˆdVMV3dV2[?]?jkX[pic]hnMOhnMO>*[pic]B*[?]U[pic]mHnHphÿu[pic]hnMOmHnHu[pic]h¤5«hnMO0JmHnHu[pic]$jh¤5«hnMO0JU[pic]mHnHu[pic]!hP·5?B*[pic]CJ\?^J[?]aJph*jhP·5?B*[pic]CJU[pic]\?^J[?]aJph!hjPí5?B*[pic]CJ\?^J[?]aJph!ht%Z5?B*[pic]CJ\?^J[?]aJph'ht%Zht%Z5?B*[pic]CJ\?^J[?]aJph0joWht%Zht%Z5?B*[pic]CJU[pic]\?^J[?]aJph
h¸Qêht%Z. You can unhide it by clicking on the checkbox.
Be careful when working with queries. Queries typically contain live data. If you change data in a query data sheet or form, it changes in the underlying table(s) as well without any warning
In their Air Force to simplify task, query wizards sometimes unnecessarily restrict your choices. In a Crosstab query Wizard, for example, you must base your new query on a single existing table or query. If you want to use more than one table, you must first create a query that contains all of the target fields. By contrast, if you create a Crosstab Query in designing view. You can add fields from two or more related tables
Filters represent an excellent way to create a query without diving into the sometimes confusing Query Design view. For instance, you can open a table in Datasheet view and select a fragment of data in a single field (the word nylon, for instance). Create a filter based on the selection, and then switch to Advanced Filter/Sort view to save the filter as a query (Products That Contain the Word Nylon) that you can reuse anytime.
Want to archive information from a database table? Run a make-table query to copy records that meet specific criteria, such as customers who have not placed an order in more than a year. Then, aver running that query, use the same selection criteria as part of a delete query to remove the same records from the original table.
Running an action query can absolutely and irrevocably scramble your data, and in most cases the effect of an action query cannot be undone. If you inadvertently run an action query that doesn’t behave as you expected, you might find it difficult or impossible to restore your original data. For this reason, you should proceed with great care when designing and running an action query. At a minimum, make a copy of the original table under another name, so that you can restore the data if your action query has unintended consequences.
You can save an action query for future use, or you can abandon it after a single use. If the purpose of the query is to perform a one-time maintenance chore, get rid of the query when you no longer need it. That way you do not risk accidentally corrupting your database. The only reason to save a query is if you expect to reuse it as part of ongoing database management-for example, in a monthly database cleanup routine.
Tabs in the Properties dialog box make it easier to find the exact function you are looking for. All the tabs that affect the appearance of a control, for example, are on the Format tab. Click the All tab to scroll through a list of all the perperties that apply to the selected object.
Labels are always unbound, and they don’t change as you move from one record to another. Access automatically adds labels to new fields you place on a form or report; you might also use labels for titles and instructions.
Do you want to position an object precisely on a form? If you plan to print out an Access form and use it as an invoice, you might want a graphic to appear in a fixed location at the top of the form. To add the graphic, choose the Image button from the Toolbox. Then click and drag to define a region on the form, in the general location where you want it to appear. Select the image file from the Open dialog box. Finally, click the Format tab on the Properties dialog box for the image you embedded, and set the Left, Top, Width, and Height properties to define the exact size and location on the page.
Caption name
With a modest amount of creativity, you can turn a report header into a dramatic title page for a report. If the report header isn’t visible in Design view, choose View, Report Header/Footer to make it appear. Next, drag the bottom border of the Report Header section to make it occupy as much of the page as you need. In the Properties dialog box for the section, set the Force New Page property to After Section. Finally, add any text labels and graphics you want, and set the background color if necessary.
If you’ve grouped data using a field that contains date information, use the Group On option to arrange it by interval- month, quarter, or year. By combining this header with other groupings, you can see a list of all sales by customer by month, even if the data appears only by day.
If you want to start a new page for each grouping, open the Properties dialog box for the Group Footer section and set the Force New Page property to After Section. If this section is not visible, set this property for the Detail section instead.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- records management 2004
- abstract isasi
- access and identity management system aims ms word
- quality council of india creating an ecosystem for
- system design document template veterans affairs
- handouts ho practice exercises pe
- fsh 5709 16 60 aviation security
- department of behavioral and developmental
- data requirements document hud
Related searches
- records management answers quizlet
- usmc records management quizlet
- records management training answers
- records management quizlet 201
- records management test and answers
- records management questions and answers
- electronic records management system software
- best electronic records management software
- electronic records management system
- quizlet records management navy
- records management answers navy
- records management nko answers