A field's ________ determines what will show in a datasheet, form, and report label.

© PENN FOSTER, INC. 2016

Study Unit

Microsoft Access 2016 By Beth Brown

ABOUT THE AUTHOR

Beth Brown is the author of more than 35 computer science and computer applications textbooks. An engineering graduate of Florida Atlantic University, Ms. Brown holds a Bachelor of Science degree in Computer Science . She has worked with students and educators worldwide to develop Microsoft Office curriculum materials in addition to her work in programming, research and development, technical writing, and business.

© PENN FOSTER, INC. 2016 PAGE 1MICROSOFT ACCESS 2016 Introduction

ASSIGNMENT 1: GETTING STARTED 6

The Access Window 6

Creating an Access Database 9

Working with Input Devices 10

ASSIGNMENT 2: CREATING TABLES AND FIELDS 12

Designing a Relational Database 13

Field Properties 14

Creating Tables 16

Table Relationships 17

ASSIGNMENT 3: FORMS AND RECORDS 26

Populating a Database 26

Creating a Form 27

Adding Records with Forms 28

Updating and Deleting Records 29

Viewing Records and Related Records 29

Printing a Table 30

ASSIGNMENT 4: SORTING AND FILTERING RECORDS 37

Sorting Records in a Table 37

Filtering Records in a Table 38

ASSIGNMENT 5: SELECT QUERIES 44

Creating a Select Query 44

Range Queries 49

Complex Queries 49

Parameter Query 51

Creating a Calculated Field with a Select Query 57

The Query Wizard 60

INTRODUCTION

CONTENTS

© PENN FOSTER, INC. 2016 PAGE 2MICROSOFT ACCESS 2016 Introduction

ASSIGNMENT 6: REPORTS 62

Creating a Report 62

Modifying a Report in Layout View 64

Report View and Print Preview 66

The Report Wizard 75

Creating Labels 75

KEY POINTS 82

GLOSSARY 86

© PENN FOSTER, INC. 2016 PAGE 3MICROSOFT ACCESS 2016 Introduction

Your study materials are designed so that you can take ownership of your educational goals and schedule and complete your coursework whenever and wherever you can. At Penn Foster, you won’t study at the same pace as other students in the same program, but you can reach out to your peers using the Community. Your study materials are bro- ken down into small chunks that are easy to handle, and each section is tied directly to the learning outcomes and objectives. Materials include summaries, reviews, self-checks, and activities to help you master them!

You’ll find it easiest to study if you follow the plan outlined below.

1 Look over the contents page to get a general idea of what you’re going to learn in this study unit.

2 Quickly read the pages in Assignment 1. This process is called skimming.

3 Return to the beginning of Assignment 1. This time, read more closely and pay careful attention to what you’re reading. Focus on main concepts and definitions.

4 Complete all exercises as you come to them and check your answers with those provided.

5 When you finish reading Assignment 1, complete any exercises at the end of the assignment. The exercises aren’t graded; they’re designed to help you test yourself to make sure you understand what you’ve read.

6 Complete each assignment in this manner; then review the material in preparation for the examination.

7 When you feel confident that you understand the material, complete the examination for this study unit.

INTRODUCTION

NAVIGATING THIS COURSE

© PENN FOSTER, INC. 2016 PAGE 4MICROSOFT ACCESS 2016 Introduction

Your Microsoft Access 2016 course includes the materials listed below.

OO This study unit, Microsoft Access 2016, which includes

O� All of your assigned readings

O� Self-checks and other exercises that allow you to measure how well you understand your course material

OO An examination to test your understanding of the material

INTRODUCTION

COURSE MATERIALS

© PENN FOSTER, INC. 2016 PAGE 5MICROSOFT ACCESS 2016 Introduction

What will you get from this study unit?

Microsoft Access is a relational database management system application, which is part of the Microsoft Office suite. Access is used to manage and analyze data. In this unit, you’ll learn about the features of Access and how they’re used to produce databases that can be sorted, filtered, queried, and analyzed. You’ll also learn about database design and how to organize data for the most effective analysis. Learning how to use Access will give you a greater range of professional skills and help you have a better understanding of databases in general.

When you complete this study unit, you’ll be able to

1 Identify the features of the MS Access 2016 interface

2 Create a database

3 Create records to populate a database

4 Compare records

5 Analyze data with select queries

6 Summarize data in a report

INTRODUCTION

UNIT OBJECTIVES

© PENN FOSTER, INC. 2016 PAGE 6MICROSOFT ACCESS 2016 Assignment 1

A database is a collection of related information, called data. Microsoft Access 2016 is a relational database management system (RDBMS), which is used to create and manage a database. With Access you’ll learn how to search, sort, query, and produce reports about data. The instructions to start Access vary depending on your installation. However, you’ll most likely need to either click the Access 2016 icon on the Taskbar at the bottom of the screen or double-click the icon on the Desktop.

THE ACCESS WINDOW

An Access database contains table objects for storing data, form objects for data entry, query objects for displaying the results of a question based on the stored data, and report objects for formatting and presenting data.

ASSIGNMENT 1

GETTING STARTED

ASSIGNMENT OBJECTIVES When you complete Assignment 1, you’ll be able to

1. Identify the features of the MS Access 2016 interface

The terms you need to know for this assignment are

ASSIGNMENT 1

VOCABULARY

OO Database

OO Relational database management system (RDBMS)

OO Table object

OO Form object

OO Query object

OO Report object

© PENN FOSTER, INC. 2016 PAGE 7MICROSOFT ACCESS 2016 Assignment 1

FIGURE 1—The Access Window

Features of the Access window (Figure 1) include the following:

OO The Quick Access Toolbar at the top of the window has shortcuts to commonly used commands. The default shortcuts are Save, Undo, and Redo. You can click the Customize Quick Access Toolbar icon to add or remove shortcuts (Figure 2).

OO The file name, or database name, is displayed in the top center of the window. When you save a new database, the name you provide will be displayed here.

FIGURE 2—The Quick Access Tool- bar

© PENN FOSTER, INC. 2016 PAGE 8MICROSOFT ACCESS 2016 Assignment 1

OO In the upper-right corner are the Minimize button that you click to hide a window, the Restore button that you click to reduce or expand the window size, and the Close button that you click to close the application window.

OO The Ribbon contains Access commands. Commands are organized into tabs that run along the top of the Ribbon. Click a tab name to display a different set of com- mands. Within a tab, commands are grouped. Some groups include a dialog box launcher (a small arrow icon) in the lower-right corner that you can click to display a dialog box with additional options related to the group. Some commands on the Ribbon have an arrow at the side that you can click to display a menu of additional options.

OO The File tab is different from other Ribbon tabs. Click File to display the Backstage view with options for opening, saving, printing, and closing a file. To get back to the database window without executing a command on the File tab, click the Back arrow in the upper-left corner or press the Esc key.

OO The Tell Me box is a search tool and help feature. When you can’t locate a com- mand or when you just want to learn how to perform an action, click Tell me what you want to do, and then type any word or phrase in the box to display a menu of related search results. These results will include related commands and an option to display a Help dialog box with more information.

OO The Navigation pane is used to manage the objects of a database. Double-click an object name in the Navigation pane to open or run it. If you want to close the Navigation pane to give you more room in the Access window, click the Shutter Bar Open/Closed Button in the top-right corner. Click the button again to open the pane.

OO ScreenTips are small boxes that pop up when you hover the mouse over a command on the Ribbon or other features in the window. ScreenTips display infor- mation about a feature, including keyboard shortcuts for executing the command.

An open object is displayed in the window below the Ribbon. In Figure 3, a new table is open. All the data in a database is stored in related tables. A relational database should contain at least two tables. You’ll learn more about how to create related tables in the next assignment.

© PENN FOSTER, INC. 2016 PAGE 9MICROSOFT ACCESS 2016 Assignment 1

FIGURE 3—A New Table

CREATING AN ACCESS DATABASE

After starting Access, you’ll first see a Start screen with links to recently opened data- bases on the left (or, if there are none, a link to Open Other Files). On the right of the screen are links you can click to create a new blank database or to create a database based on a template (Figure 4).

FIGURE 4—The Access Start Screen

© PENN FOSTER, INC. 2016 PAGE 10MICROSOFT ACCESS 2016 Assignment 1

When you click Blank desktop database, a dialog box opens in which you must type a descriptive database name. Access requires that you specify a name for a new database when it’s created because changes are automatically saved as they’re made (Figure 5).

FIGURE 5—You must type in a file name.

Click the folder icon in the dialog box to browse for the appropriate location for the file, and then click the Create button to display your database with a new table in Datasheet view. Figure 3 shows the Datasheet view. Note that a new database doesn’t yet contain any objects besides the new table in the Navigation pane.

WORKING WITH INPUT DEVICES

The input devices you’ll most likely use with Access are the keyboard, mouse, and touch pad. You use the mouse or touch pad to select commands, click inside fields, and cus- tomize the layout of forms and reports.

You use the keyboard for typing and entering data. Along with keys for typing data, the keyboard has keys for data entry:

OO The Tab key enters data and moves to the next entry box in a table or form. Press Shift+Tab to move to the previous entry. Press Tab in the last entry of a record to display a new record.

OO The Enter key also enters data and moves to the next entry box in a table or form. When you press Enter in the last entry of a record, a new record is displayed.

OO The Esc key function varies depending on the action, but it’s commonly used to cancel the current operation.

© PENN FOSTER, INC. 2016 PAGE 11MICROSOFT ACCESS 2016 Assignment 1

RDBMS

You can use the Access Help feature to better understand Access objects. In the Tell Me box, type “Learn the structure of an Access database” and then click the Get Help on option at the bottom of the menu. In the displayed Help, click Learn the structure of an Access database, and then scroll down and read the Overview.

Sensitive Data

Databases are used to store vast amounts of data. What considerations should you make when storing personal information or other sensitive data in your database?

ASSIGNMENT 1

DISCOVER MORE

ASSIGNMENT 1

REFLECT AND RESPOND

1. What type of application is used to create and manage a database?

2. Name the four types of objects in an Access database.

3. The _______ is used to manage database objects.

4. Where is all the data for a database stored?

5. Why does Access require that you enter a descriptive name when the data- base is created?

Click here to check you answers.

ASSIGNMENT 1

SELF-CHECK

http://lessons.pennfoster.com/pdf/584007_an0679.pdf

© PENN FOSTER, INC. 2016 PAGE 12MICROSOFT ACCESS 2016 Assignment 2

Learning how to create and use a database is exciting when you consider that many aspects of your life are affected by databases. For example, have you ever Googled for information? Google uses a database to deliver search results. Did you know that mas- sive databases such as CODIS help investigators use DNA information to solve cold cases? And how about online shopping? It’s all processed through databases.

ASSIGNMENT 2

CREATING TABLES AND FIELDS

ASSIGNMENT OBJECTIVES When you complete Assignment 2, you’ll be able to

2. Create a database

The terms you need to know for this assignment are

ASSIGNMENT 2

VOCABULARY

OO Table

OO Field

OO Data redundancy

OO Normalization

OO Field properties

OO AutoNumber

OO Lookup field

OO Data integrity

OO Validation rules

OO Record

OO Primary key

OO Datasheet view

OO Design view

OO Active field

OO Foreign key

OO One-to-many

OO Referential integrity

© PENN FOSTER, INC. 2016 PAGE 13MICROSOFT ACCESS 2016 Assignment 2

DESIGNING A RELATIONAL DATABASE

Tables are used to store all of the data in a database. For example, the Clever Acme Corp. database is divided into three tables: orders, customers, and products (Figure 6).

Before creating a database, you must give thought to the database design, which has four major steps:

1. Clearly define the purpose of the database. Do you want to keep track of cus- tomers and orders for your business? Are you trying to organize your comic book collection and related memorabilia? With the purpose in mind, decide on the data you need to store for the queries and reports you want to generate.

2. Divide data into tables that don’t duplicate data. Eliminating unnecessary data duplication, or data redundancy, is a guiding principle of good database design. The process of organizing data to reduce redundancy is called normalization. For example, an Orders table shouldn’t contain customer addresses when the Customers table already stores that information. However, the Orders table should include a Customer ID field so it can indirectly access the address by looking it up in the Customers table.

3. Choose the primary key for each table. The primary key is the field in a table that contains unique entries only. For example, Order ID is never the same for any two orders.

4. Specify table relationships. As the name implies, in a relational database every table must have a field that relates it to at least one other table. For example, the Orders table and Customers table are related by Customer ID. You may need to add a field to a table to achieve a relationship, but if you can’t relate a table to at least one other table, then it doesn’t belong in the database.

Within a table, fields contain data about a certain aspect of the subject. Records contain all the field data about a specific item. Field values hold each individual piece of data being stored (Figure 7). How you group data into tables and further divide it into named fields determines how useful your database will be.

FIGURE 6—There are three tables in the database for Clever Acme Corp.

© PENN FOSTER, INC. 2016 PAGE 14MICROSOFT ACCESS 2016 Assignment 2

FIGURE 7—Last Name is the field, the blue highlight is the record, and “Lee” is the field value.

FIELD PROPERTIES

Your database design should also include determining the properties for each field. Field properties refer to the name, type, size, format, and number of decimal places, if any. Data types include

OO Text Short (up to 255 characters) Long (up to 1GB of data)

OO Number (digits)

OO Date/Time

OO Currency

OO Hyperlink

OO Yes/No (displayed as a checkbox)

OO Attachment (stores a file)

OO AutoNumber (a unique number that’s generated by Access for each record; useful for ID fields)

OO Calculated (an expression that uses data from fields)

OO Lookup field (stores a value from another table)

In the Orders table, the Customer ID and Product ID fields are Lookup fields. When you click a Lookup field, an arrow is displayed so you can select an existing value from the related table.

© PENN FOSTER, INC. 2016 PAGE 15MICROSOFT ACCESS 2016 Assignment 2

The Orders table includes several data types, as shown in Figure 8:

OO AutoNumber in the first column

OO Date in the second column

OO Text in the fourth column

OO Number in the fifth column

FIGURE 8—Examples of Different Data Types

Your field names should describe the data being stored accurately while using the short- est name possible. Some words, such as Name and Date, are reserved by Access and can’t be used as field names. You should also avoid special characters and use complete words rather than abbreviations.

ENFORCING DATA INTEGRITY

If you design your database tables properly and without data redundancy, you need only check the accuracy of your data in one place. This is a good start to enforcing data integrity, the accuracy and consistency of data. Another way you can enforce data integ- rity is by using validation rules, which check your data for a value outside a specified range of values. For example, your order quantity should be a number greater than 0. Validation rules can include relational operators, such as < and > (less than and greater than) as well as the logical operators AND, OR, and NOT. For a color field, you may want to limit entries to “orange OR blue” but “NOT red.” These rules cause an error message to display if the user attempts to enter data that doesn’t fit the rule.

© PENN FOSTER, INC. 2016 PAGE 16MICROSOFT ACCESS 2016 Assignment 2

CHOOSING THE PRIMARY KEY

A table is organized into rows and columns, with each column being a field. Each row in a table is a record. Your database must have unique records for it to be reliable. To ensure no two records are the same, Access requires every table to have a primary key, which is a field that must contain a unique entry. An ID field is commonly designated the primary key, as in the Orders table in Figure 8.

CREATING TABLES

After you’ve designed the tables, fields, field properties, validation rules, and primary keys, you’re ready to create your database tables.

When you create a database, Access automatically displays the new table in Datasheet view, which shows rows and columns. Although you can type field names and select most field properties from this view, you can more easily define field properties from Design view, which looks similar to Figure 9. (Note the different data types.)

FIGURE 9—Design view makes database design easier.

OO The AND logical operator requires that both criteria be met.

OO The OR logical operator requires that one of the two criteria be met.

OO The NOT logical operator requires that both criteria be absent.

© PENN FOSTER, INC. 2016 PAGE 17MICROSOFT ACCESS 2016 Assignment 2

To display a table in Design view, click View on the Home tab. You may be prompted to enter a table name before switching to Design view. Type a descriptive name because this will be the object name in the Navigation pane. If the table is already named, clicking View toggles between Design and Datasheet view.

In Design view, you create one field per line. If you want a Lookup field, select Lookup Wizard as the data type and then follow the instructions in the dialog boxes. The Field Properties at the bottom of the window apply to the active field, which is highlighted. Type any validation rules into the Validation Rule box as well as any text that will appear if invalid data is entered in Validation Text.

To designate a field as the primary key, click the field and then click Primary Key on the Table Tools Design tab. In some cases, you may need to designate a combination of two fields as the primary key. To do this, click the gray box to the left of the first field, and then press and hold the Ctrl key while you click the gray box of the second field before clicking Primary Key.

After creating your fields, save the table and then click View on the Home tab to switch to Datasheet view. You can close a table by clicking the Close box in the upper-right corner of the table. To create another table, click Table or Table Design on the Create tab.

INPUT MASKS

An input mask is another way to enforce data integrity. An input mask guides data entry by displaying underscores, dashes, asterisks, and other placeholder characters to indicate the type of data expected. For example, the input mask for a date might be __/__/____. Click Input Mask in the Field Properties area of Design view to get started.

TABLE RELATIONSHIPS

A relationship specifies how one field in a table corresponds to a field in a different table. Often a relationship is based on the primary key and the foreign key. The foreign key is a field that’s a primary key in one table and is referenced in another table. For example, the Product ID field is the primary key in the Products table and a foreign key in the Orders table (Figure 10).

ASSIGNMENT 2

DISCOVER MORE

© PENN FOSTER, INC. 2016 PAGE 18MICROSOFT ACCESS 2016 Assignment 2

FIGURE 10—The key symbol indicates the primary key in the table, and the lines indi- cate relationships.

A relationship should be one-to-many, which means that for one field in a table there can be many fields with that same data in the related table. For example, in the Products table, Product IDs are unique. However, in the Orders table, the same Product ID can occur many times. If a relationship isn’t one-to-many, then you probably need to go back to the design stage and break data down into more tables.

When building your database, you should ensure that relationships are arranged to pre- serve referential integrity. This simply means that you want Access to notify you when a deletion or other change in one table will create invalid records in a related table. For example, if you delete a record from the Products table, then records in the Orders table might refer to nonexistent products. This loss of integrity reduces the reliability of the database. You choose to have Access notify you of an issue when designating relationships.

If you created Lookup fields, Access automatically defined relationships for you. However, you should still check to be sure that every table in your database has been related to another table. Without relationships, the queries and reports you create later may not work properly.

To view your tables and any specified relationships, click Relationships on the Database Tools tab. If a table is missing from this view, click Show Table on the Relationship Tools Design tab to add missing tables. To specify a relationship, drag the field name from one table to the corresponding field in another table. A line will appear to show that the relationship is specified, and the Edit Relationships dialog box allows you to select Enforce Referential Integrity (Figure 11).

© PENN FOSTER, INC. 2016 PAGE 19MICROSOFT ACCESS 2016 Assignment 2

APPLY YOUR KNOWLEDGE

As a manager at Healthy Home Care, Inc. you want to use a database to keep track of clients and the services they’ve requested. Follow the steps below to create a database to generate schedules:

1. Design the database carefully. Consider the following information:

OO Healthy Home Care has a menu of services, which include visiting nurse, phlebotomist, physical therapist, nutrition counseling, cooking, cleaning, grocery shopping, and companionship. Each service has an assigned time in minutes and a charge if not billed to insurance.

OO You have client information, including first and last names, addresses, email addresses, and special requests such as hobbies and other interests for companionship.

OO You want to email your clients PDF copies of their weekly schedules.

OO Based on the purpose of your database, you’ve decided you need three tables: Services (Service ID, Service Name, Time (minutes), Charge, Clients (Client ID, First Name, Last Name, Address, City, State, Zip, Email, Special Requests), and Schedules (Schedule ID, Client ID, Service ID, Appointment, Start Time).

OO You’ll use the Service ID, Client ID, and Schedule ID fields as the primary keys for your tables.

(Continued)

FIGURE 11—The Edit Relationships Dialog Box

ASSIGNMENT 2

DISCOVER MORE

© PENN FOSTER, INC. 2016 PAGE 20MICROSOFT ACCESS 2016 Assignment 2

OO The Client ID and Service ID fields will be Lookup fields in the Schedules table, so this will automatically define relationships for all three tables.

OO You want to validate that the Charge field is >=0.

2. Start Access and click Blank desktop database. A dialog box is displayed.

3. Type “Healthy Home Care” and then click the folder icon to display a dialog box in which you navigate to the appropriate location for the file. Click OK when you’ve selected the location and then click Create. The Access database window is dis- played with a new table.

4. On the Home tab, click View. You’re prompted to enter a name for the table.

5. Type “Services” and then click OK. The new table is displayed in the Design view.

6. Edit the ID field to be “Service ID.” Note that it’s an AutoNumber data type, which means that it will automatically change when records are later added (Figure 12).

7. Type “Service Name” in the next Field Name box.

8. Click in the Data Type box, click the drop-down arrow, and then click Short Text.

(Continued)

ASSIGNMENT 2

DISCOVER MORE

FIGURE 12—The Service ID field is an AutoNumber data type.

© PENN FOSTER, INC. 2016 PAGE 21MICROSOFT ACCESS 2016 Assignment 2

9. Add the remaining fields so that your Design view looks similar to Figure 13. Be sure that you enter the validation rule and validation text for the Charge field (Figure 13).

FIGURE 13—Add a validation rule and validation text.

10. Save the modified table.

11. On the Table Tools Design tab, click View. Note that Access places default values in your numeric fields until you add data of your own.

12. In the upper-right corner of the Services table, click the Close box to remove it from the Access window. Note the Services object in the Navigation pane.

13. On the Create tab, click Table Design. A new table is displayed in Design view.

14. Create the fields for the table as shown in Figure 14.

(Continued)

ASSIGNMENT 2

DISCOVER MORE

FIGURE 14—Create the fields as shown.

© PENN FOSTER, INC. 2016 PAGE 22MICROSOFT ACCESS 2016 Assignment 2

15. Click in the Client ID field and then on the Table Tools Design tab, click Primary Key. A small key appears next to the field name.

16. On the Table Tools Design tab, click View. When prompted to save the table, name it “Clients.”

17. Close the table.

18. Create a new table. Add a new field named “Schedule ID” and make it the primary key.

19. In the next Field Name box, type “Client ID” and then select Lookup Wizard for the Data Type (Figure 15). A Lookup Wizard dialog box is displayed.

a. Click I want the lookup field to get the values from another table or query and then click Next.

b. Click Table: Clients and then click Next. c. Click Client ID, click > to move it to the Selected Fields list, and then

click Next. d. In the 1. list, click Client ID and then click Next. e. Click Next to skip the width adjustment. f. If necessary, type “Client ID” for the label name and then click Finish. g. When you’re prompted to save the table, save it with the name “Schedules.”

(Continued)

ASSIGNMENT 2

DISCOVER MORE

FIGURE 15—Use the Lookup wizard to link the tables.

© PENN FOSTER, INC. 2016 PAGE 23MICROSOFT ACCESS 2016 Assignment 2

20. In the next Field Name box, type “Service ID” and then select Lookup Wizard for the Data Type. Use the Services table and the Service ID field to define the lookup field.

21. In the next Field Name box, type “Appointment.”

22. For the Data Type, click Date/Time.

23. In the Field Properties in the bottom of the window, click Format, click the drop- down arrow, and then select Long Date from the list.

24. Create the last field, naming it “Time” with data type Date/Time.

25. In the Field Properties in the bottom of the window, click Format, click the drop- down arrow, and then select Medium Time from the list. Your fields should look similar to Figure 16.

FIGURE 16—The Schedules Table

(Continued)

ASSIGNMENT 2

DISCOVER MORE

© PENN FOSTER, INC. 2016 PAGE 24MICROSOFT ACCESS 2016 Assignment 2

26. Switch to Datasheet view and close the table. You now have three database objects in the Navigation pane.

27. You need to check the relationships of your table. On the Database Tools tab, click Relationships. All three tables are displayed with relationships defined because you created Lookup fields.

28. Double-click the line between the Clients table and the Schedules table. A dialog box is displayed.

29. Note that the dialog box indicates that the relationship is One-To-Many. Click Enforce Referential Integrity and click OK.

30. Edit the relationship between Schedules and Services to Enforce Referential Integrity. Your relationships should look similar to Figure 17.

FIGURE 17—The Database Relationships

31. Close the Relationships window and then close the Healthy Home Care database.

ASSIGNMENT 2

DISCOVER MORE

© PENN FOSTER, INC. 2016 PAGE 25MICROSOFT ACCESS 2016 Assignment 2

DATABASE DESIGN

What kind of problems do you think might surface if you don’t put enough time and thought into creating your database design?

ASSIGNMENT 2

REFLECT AND RESPOND

1. What are the four steps to designing a database?

2. Name the best field type for the following fields:

a. Last Name

b. Date of Birth

c. Price

d. Company Website

3. What is the purpose of the primary key?

4. What is a foreign key?

5. How do you create a Lookup field?

Click here to check your answers.

ASSIGNMENT 2

SELF-CHECK

http://lessons.pennfoster.com/pdf/584007_an0680.pdf

© PENN FOSTER, INC. 2016 PAGE 26MICROSOFT ACCESS 2016 Assignment 3

Microsoft Access 2016 makes it easy to add and update records in a database. Features include tools for creating user-friendly data entry forms, a spelling checker for data accu- racy, and navigation controls for quickly displaying records to update.

POPULATING A DATABASE

Just as you would use a paper or digital form to fill in information, you would also use a database form to collect data. When an Access form is filled in, the data is stored as a record in a table in a process called populating the database.

Forms are a good choice for populating a database because you can format them to be user-friendly, making data entry easier and faster. They also show all the fields for just one record at a time, allowing data entry to be more accurate. The simplest type of form displays the field names and entry boxes for one record of a table, as in the Orders form for the Clever Acme Corp. database shown in Figure 18. An entry box is where you type the data, or entry, for a field.

ASSIGNMENT 3

FORMS AND RECORDS

ASSIGNMENT OBJECTIVES When you complete Assignment 3, you’ll be able to

3. Create records to populate a database

The terms and phrases you need to know for this assignment are

ASSIGNMENT 3

VOCABULARY

OO Populating a database

OO Form

OO Entry

OO Entry box

OO Control

OO Record controls

OO Update

© PENN FOSTER, INC. 2016 PAGE 27MICROSOFT ACCESS 2016 Assignment 3

CREATING A FORM

To create a form based on the fields in a table, click the table name in the Navigation pane to select it and then click Form on the Create tab. If you previously specified a relationship for the table, then a datasheet is displayed at the bottom of the form to show records from the related table as in Figure 19.

When you create a form this way, Access determines the best type of control for data entry—that is, how the data is entered. Text, memo, number, date/time, currency, and hyperlink fields will have a text box control for data entry. Other types of controls include buttons, boxes, and drop-down lists.

A new form is displayed in Design view, where you can size field names and controls and apply formats. If you want to shorten text box widths, click a text box and then drag the right edge to the left. This will apply the size to all of the boxes.

FIGURE 18—A Database Entry Form

FIGURE 19—The Products form shows a link to the Orders table.

© PENN FOSTER, INC. 2016 PAGE 28MICROSOFT ACCESS 2016 Assignment 3

You can click the Form Layout Tools Design tab to display options for adding an image or a company logo, for changing the title at the top of the form, and for adding the date and time to the form header area (Figure 20). When you’re done formatting, click View on the Form Layout Tools Design tab to switch to Form view.

ADDING RECORDS WITH FORMS

To populate your database, first open a form by double-clicking the form name in the Navigation pane. You’ll see record controls at the bottom of the open form. If your form doesn’t display a blank record, click the New (blank) record control (Figure 21). In the blank record, click an entry box to place the insertion point and then type your entry.

FIGURE 21—The record controls are at the bottom of the form.

Data entry is usually faster if you keep your hands on the keyboard. To move to the next entry box in a form, press the Tab key. You can press Ctrl+Tab to move to the previous entry box. If you press Tab or Enter when the insertion point is in the last entry box of the form, a new blank record is displayed. If a datasheet is at the bottom of a form, meaning the form is linked to a different table, pressing Tab or Enter will take you into the data- sheet. Click the New (blank) record control at the very bottom of the form window to display a new record.

FIGURE 20—The Design options allow you to insert images.

© PENN FOSTER, INC. 2016 PAGE 29MICROSOFT ACCESS 2016 Assignment 3

After entering the data for a record, click Spelling on the Home tab to check your entries. Data accuracy is very important for a database because you’ll want to search, sort, and perform queries. If data doesn’t match your criteria because of spelling errors, then your database is unreliable.

UPDATING AND DELETING RECORDS

Records in a database often require changes, or updates. The fastest way to locate the record for updating is to use the Search box in the record controls. As you type text into the Search box, the first record with a matching entry is displayed.

To update an entry, click in its entry box and edit the text. If you need to edit a hyperlink entry, right-click the entry box, and then click Hyperlink > Edit Hyperlink to display a dialog box where you can change the link. To delete the contents of an entry, press Delete or click Delete > Delete on the Home tab. When you need to delete an entire record from the database, click Delete > Delete Record on the Home tab. The currently displayed record will be permanently deleted from the database. Be careful when deleting because deleted records can’t be recovered. If the record is linked to a related table, you’ll get an error message that you can’t delete the record because that table contains related records (Figure 22).

FIGURE 22—You can’t delete records that are related to other tables.

VIEWING RECORDS AND RELATED RECORDS

If you want to see all the records for a table at once, double-click the table name in the Navigation pane to display it in Datasheet view. If necessary, drag a column boundary to change the width so you can see all the field data. You can also format Datasheet view for a best-fit width by double-clicking a column boundary.

When you’ve specified relationships for a database, related records will be displayed in a form’s subdatasheet. If you display a table in Datasheet view, you can click the + next to a record to display its subdatasheet, as in Figure 23.

© PENN FOSTER, INC. 2016 PAGE 30MICROSOFT ACCESS 2016 Assignment 3

FIGURE 23—Click the + to display the subdatasheet.

PRINTING A TABLE

If you need to print a table, you should preview it before actually printing. Click Print on the File tab and then Print Preview to see how your table will look when printed (Figure 24). Previewing a table lets you decide if you need to click Landscape to change the page orientation so that more fields can fit across the page. You can also change the margins to fit more data on a page. When you’re satisfied with the formatting of your table, click Print to print it.

FIGURE 24—Print Preview

© PENN FOSTER, INC. 2016 PAGE 31MICROSOFT ACCESS 2016 Assignment 3

The Print Preview ribbon also lets you export the table to Excel, text, email, or other types of databases (Figure 25). Click PDF or XPS to create a file. Click Email for options on how you can email the table as an attachment.

APPLY YOUR KNOWLEDGE

In this exercise, you’ll create forms and populate the Healthy Home Care database with data. Follow the steps below to modify the Healthy Home Care database you created in the previous assignment:

1. Start Access and open Healthy Home Care.accdb.

2. In the Navigation pane, click the Clients table and then on the Create tab, click Form. A new form is displayed in Design view.

3. Click the control for the Client field to select it. A border is displayed.

4. Point to the right edge of the control until you see a double-headed pointer and then drag the edge to the left. Notice that all of the text box controls are sized.

5. On the Form Layout Tools Design tab, click Title. The form title is selected.

6. Edit the form title to read “Healthy Home Care, Inc. Clients.”

7. On the Quick Access Toolbar, click Save and then save your form with the name “Clients.”

8. Click the Close box to remove the form from the window. You’ll see the Clients form in the Navigation pane.

9. Create forms for the Schedules and Services tables, changing the control widths and editing the titles as you did for the Clients form.

(Continued)

FIGURE 25—Export Options

ASSIGNMENT 3

DISCOVER MORE

© PENN FOSTER, INC. 2016 PAGE 32MICROSOFT ACCESS 2016 Assignment 3

10. Open the Clients form. The Client ID entry displays New. Press Tab to move the insertion point to the next entry box.

11. In the First Name entry box, type “Clara.” Notice that the Client ID has changed to 1. Press Tab.

12. Enter the following data, pressing Tab to move from field to field until you get to the last field (the Special Requests field): Davis, 2716 Oak Street, Any Town, FL, 12345, knit@any-town.com.

13. In the Special Requests field, type “knitting and needlepoint.”

14. On the Home tab, click Spelling and correct any misspellings.

15. In the records controls at the bottom of the form, click New (blank) record. A new record is displayed.

16. Add the following records, skipping the Client ID field because it will be automati- cally added when you create each record. Be sure to check the spelling for each record before you move on to the next:

OO Carlo, Perez, 45 Lake Drive, Any Town, FL, 12345, perez1029@any-town.com, chess

OO Millie, Wilson, 149 7th Street, Any Town, FL, 12345, mwilson@any-town.com, card games

OO Walter, Jackson, 3716 Maple Ave., Any Town, FL, 12345, wjack@any-town.com, t’ai chi

17. Close the Clients form.

18. In the Navigation pane, double-click the Clients table to open it. The table has been populated with four records.

19. Drag the boundary for the Address field to the right to widen the column so you can see the addresses completely.

(Continued)

ASSIGNMENT 3

DISCOVER MORE

© PENN FOSTER, INC. 2016 PAGE 33MICROSOFT ACCESS 2016 Assignment 3

20. Double-click other column boundaries for the best fit. Your table should look similar to Figure 26.

FIGURE 26—The Completed Clients Table

21. On the File tab, click Print > Print Preview.

22. You want to fit the printout on one sheet for easier reading. On the Print Preview tab, click Landscape. If all of your data still isn’t on one sheet, click Margins and reduce the right and left margins. When your entire table fits on one sheet, click Print to print a copy.