Download Create a Database from a Template - Project | CCIS 100 and more Study Guides, Projects, Research Computer Science in PDF only on Docsity!
Access 845
- Create a Database Using a Template
- Organize Database Objects in the Navigation Pane
- Create a New Table in a Database Created With a Template
- View a Report and Print a Table in a Database Created With a Template
- Use the Access Help System
- Start Access and Create a New Blank Database
- Add Records to a Table
- Rename Table Fields in Datasheet View
- Modify the Design of a Table
- Add a Second Table to a Database
- Print a Table
- Create and Use a Query
- Create and Use a Form
- Create and Print a Report
- Close and Save a Database
Access 2007
PROJECT 12B
Create a Database from a
Template
OBJECTIVES
At the end of this chapter you will be able to:
OUTCOMES Mastering these objectives will enable you to:
PROJECT 12A
Create a New Blank Database
Getting Started with
Access Databases
and Tables
chaptertwelve
Getting Started with
Access Databases
and Tables
Do you have a collection of belongings that you like, such as a coin or stamp collection, a box of favorite recipes, or a stack of music CDs? Do you have an address book with the names, addresses, and phone numbers of your friends, business associates, and family members? If you collect something, chances are you have made an attempt to keep track of and organize the items in your collection. If you have an address book, you have probably wished it was better organized. A program like Microsoft Office Access can help you organize and keep track of information.
Microsoft Office Access 2007 is a program to organize a collection of related information about a particular topic, such as an inventory list, a list of people in an organiza- tion, or the students who are enrolled in classes in a college. Whether you use Access for personal or business purposes, it is a powerful program that helps you orga- nize, search, sort, retrieve, and present information about a particular subject in an organized manner.
Texas Lakes
Medical Center
Texas Lakes Medical Center is an urban hospi- tal serving the city of Austin and surrounding Travis County, an area with a population of over 1 million people. Texas Lakes is renowned for its cardiac care unit, which is rated among the top 10 in Texas. The hospital also offers state-of-the-art maternity and diagnostic ser- vices, a children’s center, a Level II trauma cen- ter, and a number of specialized outpatient services. Physicians, nurses, scientists, and researchers from around the world come together at Texas Lakes to provide the highest quality patient care.
848 Access | Chapter 12: Getting Started with Access Databases and Tables
Objective 1 Start Access and Create a New Blank Database
A database collects and organizes data —facts about people, events, things, or ideas—related to a particular topic or purpose. Data that has been organized in a useful manner is referred to as information.
Many databases start as a simple list on paper, in a Word document, or in an Excel spreadsheet. As the list grows bigger and the data becomes more difficult to keep track of, it is a good idea to transfer the data to a database management system ( DBMS ) such as Access.
Examples of data that could be in a database include the titles and artists of all the CDs in a collection or the names and addresses of all the doctors and patients at a medical facility. A database includes not only the data, but also the tools for organizing the data in a way that is useful to you.
The first step in creating a new database from data that you already have is to plan your database on paper. Determine what information you want to track, and then ask yourself, What questions should this database be able to answer for me?
For example, in the Contact Information database for the Texas Lakes Medical Center, the questions to be answered may include:
- How many doctors and patients are there at the Texas Lakes Medical Center?
- Which and how many patients live in Austin?
- Is any doctor or patient listed twice?
- Which and how many patients have a balance owed?
Activity 12.1 Starting Access, Creating and Naming a Folder, and Creating a Database from a New Blank Database There are two methods to create a new Access database: create a new database using a template —a preformatted database designed for a spe- cific purpose—or create a new blank database. A blank database has no data and has no database tools; you create the data and the tools as you need them. In this activity, you will create a new blank database.
Regardless of which method you use, you must name and save the data- base before you can create any objects in the database. Objects are the basic parts of a database; you will create objects to store your data and work with your data. Think of an Access database as a container for the database objects that you will create.
! On the left side of the Windows taskbar, click the^ Start^ button
, determine where the Access program is located, point to Microsoft Office Access 2007, and then click one time to open the
Access
|^
chapter
Project 12A: Doctor and Patient Contact Information | Access 849
program. Take a moment to compare your screen with Figure 12. and study the parts of the Microsoft Access window described in the table in Figure 12.3. From this Access starting point, you can open an existing database, start a new blank database, or begin a new database from one of the available database templates.
Spotlight (yours may vary)
Office button
Quick Access Toolbar
Template Categories
Getting Started area Title bar
List of recently opened databases
Program-level buttons for Minimize, Restore Down, and Close
Help button
Blank Database button
Figure 12.
The Access Getting Started Screen
Window Part Description
Blank Database button Starts a new blank database. Getting Started area Contains the starting point to begin a New Blank Database or view new infor- mation from Microsoft Office Online. Help button Displays the Access Help window. Open Recent Database Displays a list of the most recently opened databases on the computer at which you are working. Office button Displays a menu of commands related to things you can do with a database, such as opening, saving, printing, or managing. Program-level buttons Minimizes, restores, or closes the Access program. for Minimize, Restore Down, and Close (Continued)
Access
|^
chapter
Project 12A: Doctor and Patient Contact Information | Access 851
% In the upper right corner of the^ File New Database^ dialog box, click the Create New Folder button. In the displayed New Folder dialog box, type Access Chapter 12 and then click OK. At the bottom of the dialog box, in the File name box, select the existing text, and then type 12A_Contact_Information_Firstname_Lastname Press J, and then compare your screen with Figure 12.5. Text that you select is replaced by new text that you type. The Microsoft Windows operating system recognizes file names with spaces. However, some Internet file transfer programs do not. To facilitate sending your files over the Internet, in this textbook you will save files using an underscore rather than a space. On most keyboards, the underscore key is the shift of the hyphen key, which is to the right of the zero key.
Create button
Drive and folder where your database is stored
File Name box with your database name
.accdb file extension
Figure 12.
^ In the lower right corner, click the^ Create^ button, compare your screen with Figure 12.6, and then take a moment to study the screen elements described in the table in Figure 12.7. Access creates the new database and opens a table named Table1. A table is the Access object that stores your data organized in an arrangement of columns and rows. Recall that object is the term used to refer to the parts of an Access database that you will use to store and work with your data. Table objects are the foundation of your Access database because tables store the actual data.
852 Access | Chapter 12: Getting Started with Access Databases and Tables
Left side of status bar
Navigation Pane
Ribbon with command groups arranged on tabs
Command group names
Title bar with your database name
Table Tools for working with tables Object window
Table1 tab
Right side of status bar
Figure 12.
Note — Comparing Your Screen With the Figures in This Textbook
Your screen will match the figures shown in this textbook if you set your screen resolution to 1024 × 768. At other resolutions, your screen will closely resemble, but not match, the figures shown. To view your screen’s resolu- tion, on the Windows desktop, right-click in a blank area, click Properties, and then click the Settings tab.
854 Access | Chapter 12: Getting Started with Access Databases and Tables
Activity 12.2 Adding Records to a Table In a table object, each column contains a category of data called a field. Fields are categories that describe each piece of data stored in the table. You can add the field names, which display at the top of each column of the table, before or while you are entering your data. Each row in a table contains a record —all of the categories of data pertaining to one person, place, thing, event, or idea. Your table design refers to the number of fields, the names of fields, and the type of content within a field, for example numbers or text.
There are two ways to view a table—in Datasheet view or in Design view. Datasheet view displays the table data organized in a format of columns and rows similar to an Excel spreadsheet. Design view displays the underlying structure of the table object.
When you buy a new address book, it is not very useful until you fill it with names, addresses, and phone numbers. Likewise, a new database is not useful until you populate , or fill, a table with data. You can populate a table with records by typing data directly into the table.
In this activity, you will populate a table in Datasheet view that will list contact information for patients at Texas Lakes Medical Center.
! Look at your screen and notice that the Datasheet view for a table displays. Then, take a moment to study the elements of the table object window as shown in Figure 12.8. When you create a new blank database, only one object—a new blank table—is created. You will create the remaining database objects as you need them. Because you have not yet named this table, the Table tab indicates the default name Table1. Access creates the first field and names it ID. In the ID field, Access will assign a unique sequential number— each number incremented by one—to each record as you type it into the table.
Table tab
New table in Datasheet view with default table name
Navigation Pane
Access creates and names first field ID
Navigation Pane Close button
Figure 12.
Access
|^
chapter
Project 12A: Doctor and Patient Contact Information | Access 855
@ In the^ Navigation Pane, click the^ Open/Close^ button^ to collapse the Navigation Pane into a narrow bar at the left side of your screen. Collapsing the Navigation Pane in this manner gives you more screen space in which to work with your database.
In the second column, click in the^ cell —the box formed by the inter-
section of a row and a column—under Add New Field , type Elena and then press F or J. Click in the ID field. On the Datasheet tab, in the Data Type & Formatting group, click the Data Type arrow, and then click Text. Type 1248-P and then to the right of Elena , click in the Add New Field cell, and type L Press F. Compare your screen with Figure 12.9. As soon as information is entered, Access assigns the name Field1 to the field and enters an AutoNumber of 1 in the ID field. The ID field is automatically created by Access. By default, Access creates this field for all new tables and sets the data type for the field to AutoNumber , which sequentially numbers each entry. Changing the ID field data type from AutoNumber to Text lets you enter a custom patient number. As you enter data, Access assigns Field names as Field1 , Field2 , and so on; you can rename the fields when it is conve- nient for you to do so. The pencil icon in the record selector box —the small box at the left of a record in Datasheet view which, when clicked, selects the entire record—indicates that a new record is being entered.
$ With the insertion point positioned in the fourth column, in the cell under Add New Field , type Montoya and then press F or J.
% Type^ (512) 555-0723^ and then press^ J. Type^ 854 Red Willow Drive and then press J to form Field. Do not be concerned if the data does not completely display in the column. As you progress in your study of Access, you will adjust the column widths so that you can view the data.
^ Type^ Austin^ and then press^ J^ to form^ Field6. Type^ TX^ and then press J to form Field.
Pencil icon indicates a new record is being entered
Field named Field
Record selector box
First patient ID is 1248-P
First name of first patient entered
Figure 12.
Access
|^
chapter
Project 12A: Doctor and Patient Contact Information | Access 857
Objective 3 Rename Table Fields in Datasheet View
Recall that each column in a table contains a category of data called a field , and that field names display at the top of each column of the table. Recall also that each row contains a record —all of the data pertaining to one person, place, thing, event, or idea—and that each record is broken up into small parts—the fields.
Activity 12.3 Renaming the Fields In a Table in Datasheet View In this activity, you will rename fields in your table to give the fields more meaningful names.
! At the top of the second column, point to the text Field1 to display
the pointer and click. Compare your screen with Figure 12.12.
Field 10 out of view (your screen may vary in how many columns are shown)
Records for four patients entered
Figure 12.
More Knowledge
Format for Typing Telephone Numbers in Access
Access does not require any specific format for entering telephone numbers in a database. The examples in this project use the format used in Microsoft Outlook. Using such a format facilitates easy transfer of Outlook information to and from Access.
858 Access | Chapter 12: Getting Started with Access Databases and Tables
Column selected
Fields & Columns group
Rename button
Datasheet tab active in the Ribbon
Figure 12.
@ On the Ribbon, notice that^ Table Tools^ display above the^ Datasheet tab. In the Fields & Columns group, click the Rename button, and notice that the text Field1 is selected.
Type^ First Name^ as the field name, and then press^ J. Point to the
text Field2 , click to select the column, and then in the Fields & Columns group, click the Rename button. Type Middle Initial and then press J.
$ Point to the text^ Field3^ and^ double-click^ to select the text. With the text selected, type Last Name and then press J. Point to the text Field4 and right-click. From the displayed shortcut menu, click Rename Column, and then type Phone Number and press J.
% Using any of the techniques you just practiced, rename the remaining fields as follows, and then compare your screen with Figure 12.13. Field5 Address Field6 City Field7 State/Province Field8 ZIP/Postal Code Field9 Doctor Field10 Amount Owed
Fields renamed
Four records entered
Figure 12.
Data Type Description Example
AutoNumber Available in Design view. A unique An inventory item number, such as 1, 2, 3 or a sequential or random number assigned randomly assigned employee number, such as by Access as each record is entered and 3852788. that cannot be updated. Text Text or combinations of text and numbers; An inventory item such as a computer, or a also numbers that are not used in phone number or postal code that is not calculations. Limited to 255 characters or used in calculations, and which may contain length set on field, whichever is less. characters other than numbers. Access does not reserve space for unused portions of the text field. This is the default data type. Memo Lengthy text or combinations of text and Description of a product or information numbers up to 65,535 characters or pertaining to a patient. limitations of database size. Number Numeric data used in mathematical A quantity, such as 500. calculations with varying field sizes. Date/Time Date and time values for the years 100 An order date, such as 11/10/2009 3:30 P.M. through 9999. Currency Monetary values and numeric data that An item price, such as $8.50. can be used in mathematical calculations involving data with one to four decimal places. Accurate to 15 digits on the left side of the decimal separator and to 4 digits on the right side. Use this data type to store financial data and when you do not want Access to round values. Yes/No Contains only one of two values—Yes/No, Whether an item was ordered—Yes or No. True/False, or On/Off. Access assigns -1 for all Yes values and 0 for all No values. OLE Object An object created by programs other A graphics file, such as a picture of a product, than Access that is linked to or embedded a sound file, a Word document, or an Excel in the table. OLE is an abbreviation spreadsheet stored as a bitmap image. for object linking and embedding , a technology for transferring and sharing information among programs. Hyperlink Web or email addresses. An email address, such as dwalker@txlakemed.org or a Web page, such as www.txlakemed.org. Attachment Any supported type of file—images, A graphics file, such as a picture of a product, spreadsheet files, documents, charts. a sound file, a Word document, or an Excel Similar to email attachments. spreadsheet stored as a bitmap image—same as OLE Object. Lookup Available in Design view. Not a data type, Link to another field in another table. Wizard but will display on data type menu. Links to fields in other tables to display a list of data instead of having to manually type in the data.
860 Access | Chapter 12: Getting Started with Access Databases and Tables
Figure 12.
Access
|^
chapter
Project 12A: Doctor and Patient Contact Information | Access 861
Click the^ Data Type arrow^ again to close the list without changing
the data type. In any record, click in the Address field, and notice that the Data Type box indicates Text. As described in the table in Figure 12.16, Access assigns a data type of Text to combinations of letters and numbers.
$ Scroll to the right as necessary, in any record click in the^ Amount Owed field, and then in the Data Type box, notice that Access assigned the data type of Number. Click the Data Type arrow to the right of Number , and then from the displayed list, click Currency. Based on your typing, Access determined this data type to be Number. However, Amount Owed refers to a monetary value, so the data type must be defined as Currency. When you click the Currency data type, Access automatically adds a U.S. dollar sign ($) and two decimal places to all the fields in the column. Compare your screen with Figure 12.17.
Amount Owed field data Data Type box indicates Currency type changed to Currency
Figure 12.
% Scroll to the left as necessary, and in any record, click in the^ ID^ field. In a database, each record should, in some way, be different from all the other records. What is important is that the number is unique; no other record in the table will be assigned this number. You are probably familiar with unique numbers. For example, at your college, no two students have the same Student ID number, although they could have the same name, such as David Michaels. When records in a database have no unique number, for example the CDs in your personal collection probably have no unique number, the AutoNumber data type is a useful way to automatically create a unique number so that you have a way to ensure that every record is unique.
Access
|^
chapter
Project 12A: Doctor and Patient Contact Information | Access 863
& On the^ Quick Access Toolbar, click the^ Save^ button^. The Save As dialog box displays. Recall that an individual record is saved as soon as you move to another row in the table. However, because you have changed the table design by changing field names and data types, Access will prompt you to save the design changes made to the table. Here you can also give the table a more meaningful name if you want to do so. In the Table Name box, a suggested name of Table1 dis- plays and is selected—yours may differ depending on the number of tables you have attempted to create in this database. You will likely want to give your table a name that describes the information it contains. You can use up to 64 characters (letters or numbers), including spaces, to name a table.
- In the^ Save As^ dialog box, in the^ Table Name^ box and using your first and last name, type 12A Patients Firstname Lastname and then click OK. Compare your screen with Figure 12.19. The table tab displays the new table name. When you save objects within a database, it is not necessary to use underscores. Your name is included as part of the object name so that you and your instructor will be able to identify your printouts and electronic files.
Table name
Figure 12.
864 Access | Chapter 12: Getting Started with Access Databases and Tables
Objective 4 Modify the Design of a Table
When you create and populate a new table in Datasheet view, the data that you type for the first record determines the number and content of the fields in the table. Recall that the number and names of the fields and the data type of each field is referred to as the table design. After you have created a table, you may find that you need to make changes to the design of the table by adding or deleting fields, or changing the order of the fields within a table. You can modify a table in Datasheet view, but you may prefer to modify the table in Design view where you have addi- tional options.
Activity 12.5 Deleting a Field in Design View June Liu has decided that a field for the patient’s middle initial is not necessary for the Patients table. In this activity, you will delete the Middle Initial field in Design view.
! On the^ Datasheet tab, in the^ Views group, click the^ View button arrow. There are four common views in Access, but two that you will use often are Datasheet view and Design view. On the displayed list, Design view is represented by a picture of a pencil, a ruler, and a protractor. Datasheet view is represented by a small table of rows and columns. When you see these icons on the View button, you will know that clicking the button will take you to the view represented by the icon.
@ From the displayed list, click^ Design View, and then take a moment to study Figure 12.20.
More Knowledge
Changing the Table Name
If you type the table name incorrectly or need to change the name of a table, click the Close button in the upper right corner of the object win- dow to close the table. Open the Navigation Pane, right-click the table name, and then from the displayed shortcut menu, click Rename. The table name will display in edit mode so that you can type the new name or edit it as you would any selected text.
