Is anyone advanced in Microsoft Access? I need answers to these questions

584007.pdf

Home >Business & Finance homework help >Management homework help >Is anyone advanced in Microsoft Access? I need answers to these questions

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.

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

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

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

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

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

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

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

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.

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

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.

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

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

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.

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.

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.

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.

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

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).

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

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.

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.

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.

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

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

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

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

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.

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.

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.

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

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

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

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.

23. On the Print Preview tab, click Close Print Preview or press Esc to return to Datasheet view.

24. Save and close the table.

25. Open the Services form and add the following records, skipping the Service ID field because it will be automatically added when you create a record. Note that there’s a $0 charge for services billed to insurance:

OO Visiting Nurse, 60, $0

OO Phlebotomist, 30, $0

OO Physical Therapist, 60, $0

OO Nutrition Counseling, 45, $0

OO Cooking, 60, $22

OO Cleaning, 120, $45

OO Grocery Shopping, 60, $20

OO Companionship, 60, $18

(Continued)

ASSIGNMENT 3

DISCOVER MORE

26. Close the Services form.

27. Open the Services table and format the datasheet so that all data and field names are completely displayed.

28. Save and close the Services table.

29. Open the Schedules form and then add the following records, skipping the Schedules ID field because it will be automatically added to each record. You can choose the Client ID and Service ID with the drop-down arrows in their entry boxes. Because of the field type selected when the database was created, your dates will automatically convert to a long form:

OO 2, 4, 9/18/17, 10:00 AM

OO 4, 6, 9/19/17, 9:00 AM

OO 4, 7, 9/20/17, 4:00 PM

OO 2, 5, 9/19/17, 9:00 AM

OO 3, 3, 9/18/17, 11:00 AM

OO 1, 6, 9/22/17, 9:00 AM

OO 2, 8, 9/21/17, 2:00 PM

OO 1, 2, 9/19/17, 7:30 AM

OO 2, 1, 9/20/17, 10:00 AM

OO 1, 8, 9/18/17, 2:00 PM

OO 3, 3, 9/20/17, 11:00 AM

30. Close the Schedules form.

31. Open the Schedules table and format the datasheet so that all data and field names are completely displayed.

32. Save and close the Schedules table.

(Continued)

ASSIGNMENT 3

DISCOVER MORE

33. Open the Clients form. Scroll through the records and note the subdatasheet with corresponding appointments.

34. Carlo Perez has notified you that he has moved. In the Search box, type “Perez” to display his record.

35. Click in the Address entry box and update it to “110 Atlantic Circle.”

36. Close the form.

37. Open the Services table and click the + beside record 3. View the appointments about which you’ll need to remind the Healthy Home Care physical therapist.

38. Close the Services table.

39. View other tables and subdatasheets. You can already see how useful a database is for showing relationships among data.

40. Close the Healthy Home Care database.

RELIABLE DATA

A database is only as reliable as its data. What are some mistakes you might make when entering records?

ASSIGNMENT 3

DISCOVER MORE

ASSIGNMENT 3

REFLECT AND RESPOND

1. When you add records to a database, you _______ the database.

2. How do you open table and form objects for a database?

3. What are the record controls used for?

4. Which key do you press to move from one entry box to another on a form?

5. Is it possible to edit a hyperlink entry? Explain.

6. Is it possible to recover a deleted record?

Click here to check your answers.

ASSIGNMENT 3

SELF-CHECK

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

Microsoft Access 2016 has many tools for analyzing data. You can reveal trends, patterns, and other information by comparing and viewing data in different ways. Two commonly performed tasks for changing the way data is displayed are sorting and filtering.

SORTING RECORDS IN A TABLE

When you sort a table, you order records based on the data in a specific field. For exam- ple, you can sort a table in ascending order from low to high, or descending order from high to low. Alphabetizing by name is an example of sorting data. If the field you’re basing the sort on is a date or time, then ascending sort puts records in chronological order. Descending sort puts them in reverse chronological order.

To sort a table, click the drop-down arrow next to the field name on which to base the sort and then select a sort command (Figure 27). An arrow is displayed in the field name when a sort has been applied (Figure 28). To clear a sort, click Remove Sort on the Home tab. If you want to change the order in which records are displayed in a form, click an entry field and then click a sort command on the Home tab.

ASSIGNMENT 4

SORTING AND FILTERING RECORDS

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

4. Compare records

The terms you need to know for this assignment are

ASSIGNMENT 4

VOCABULARY

OO Sort

OO Ascending order

OO Descending order

OO Chronological order

OO Filter

FIGURE 27—The Sort Options

FIGURE 28—The Table Sorted by Last Name

FILTERING RECORDS IN A TABLE

When you’re examining data and looking for a trend, you may find it easier to display only those records that match certain criteria. For example, if you want to determine the sales for one particular product, it might be easier to display just the records for that product. To limit the records displayed to those that match your criteria, you apply a filter. To apply a filter, click the drop-down arrow next to the field name on which you want to base the filter. If you want to limit the records to certain entries, click Select All to clear the check boxes and then click the entries that you want to display (Figure 29). A funnel is displayed in a field name when a filter has been applied (Figure 30).

FIGURE 29—Select the entries you want to filter.

FIGURE 30—Results of the Filter

Remove the filter and display all the records in the table again by clicking the Filter icon in the field name and then clicking Clear filter. You may also click Toggle Filter on the Home tab to clear a filter.

You can filter records based on relational operator commands, which depend on the field data type. For example, when a field contains numbers, click Number Filters in the field menu and then click a comparison type (Figure 31). When the field contains text, the Text Filters command is available for relational comparisons, including Begins With and Ends With. When you click a comparison type, a dialog box is displayed so that you can type the value for comparison (Figure 32).

FIGURE 31—The Number Filters Options

APPLY YOUR KNOWLEDGE

In this exercise, you’ll sort and analyze tables. Follow the steps below to examine the data in 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, double-click the Clients table to open it.

3. Sort the records in alphabetical order by last name. To do this, click the drop-down arrow next to the Last Name field name and then click Sort A to Z.

4. Save the Clients table and then close it.

(Continued)

FIGURE 32—Custom Filter Dialog Box

ASSIGNMENT 4

DISCOVER MORE

5. Open the Schedules table and place the records in chronological order from oldest to newest. Your table should look similar to Figure 33.

FIGURE 33—These schedules are sorted in chronological order.

6. Save the Schedules table and then close it.

7. Sort the Services table by Service Name and then save it.

8. Now filter services to those requiring one hour (60 minutes). To do this, click the drop-down arrow next to Time (minutes) to display the menu.

9. Click Select All to clear the options, click 60, and then click OK. Five services are displayed (Figure 34).

FIGURE 34—The Services Table Filtered for 60 Minutes

(Continued)

ASSIGNMENT 4

DISCOVER MORE

10. On the Home tab, click Toggle Filter to remove the filter. All Services records are again displayed.

11. Close the Services table.

12. Open the Schedules table. Now filter services to those scheduled between 9:00 AM and 11:00 AM.

13. Click the drop-down arrow next to Time. Click Date Filters > Between. A dialog box is displayed.

14. Enter the time range shown in Figure 35. Select OK. Records meeting the criteria are displayed.

15. Click the funnel in the Time field name and then click Clear filter from Time to again display all records.

16. Save the Schedules table and close it.

17. Close the Healthy Home Care database.

ASSIGNMENT 4

DISCOVER MORE

FIGURE 35—Type “9:00” and “11:00.”

INTERPRETING DATA

When a table is sorted or filtered, you may misinterpret the data. For example, you might think the current sequence shows the order in which the records were entered rather than a sort that was recently applied. What other kinds of mistakes might you make when looking at a table if you don’t realize it has been filtered or sorted?

ASSIGNMENT 4

REFLECT AND RESPOND

1. A/An ________ places records in a table in a designated order.

2. A/An ________ limits the records displayed to those that meet specified criteria.

3. Records that are organized by date are in _______ order.

4. Why would you want to order customer names by last name?

5. Does a filter delete records from a table? Explain.

Click here to check your answers.

ASSIGNMENT 5

SELF-CHECK

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

The power of a relational database is demonstrated through select queries. When you have a question involving the data stored in your Access database, you get the answer using a select query, which can display results that include fields from any or all of your tables.

CREATING A SELECT QUERY

A select query retrieves, or selects, data matching specified criteria. It’s an RDBMS object that can be saved with the database and then run repeatedly by opening it from the Navigation pane. It works by joining tables through their primary keys and then using the criteria you specify to select matching data from these joined records. The results for a select query are displayed in Datasheet view, similar to a table. For example, Figure 36 shows the results of the Clever Acme Corp. Widget Customers select query. The Widget Customers query results are compiled from the Customers table (Customer ID, First Name, Last Name), Products table (Product Name, Color), and Orders table (Quantity). For the Widget Customers query, the criteria was “Product Name is Widget.”

ASSIGNMENT 5

SELECT QUERIES

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

5. Analyze data with select queries

The terms you need to know for this assignment are

ASSIGNMENT 5

VOCABULARY

OO Select query

OO Wildcard characters

OO Range query

OO Complex query

OO AND logical operator

OO OR logical operator

OO Parameter query

OO Parameter

OO Calculated field

FIGURE 36—Query Results

To create a select query, click Query Design on the Create tab. The Query Tools Design tab displays the tables used in the query and a design grid where criteria are entered (Figure 37). When you create a select query, you’ll be prompted to add tables. You don’t need to add every table from your database, but the tables you add must be related. Add fields to the design grid by dragging a field name from the table above to one of the Field boxes below. If you want to specify a sort order for the query results, click in a Sort box and select an option. Type the criteria for a field in the corresponding Criteria box. Access automatically encloses your criteria with quotation marks. You should not include dollar signs or commas in numeric criteria.

FIGURE 37—The Query Tools Design Tab

To run your select query, click Run on the Query Tools Design tab. Click Save on the Quick Access toolbar to save the query object.

USING WILDCARDS IN QUERY CRITERIA

You can use wildcard characters to query for text that matches several possible criteria. For example, you might want to view all customers whose last names begin with L. The wildcard characters * and ? are useful for defining this type of criteria. The * character matches any number of characters or none at all. For example, a search for “I*” matches I, In, Inn, India, and Intrepid. The ? character matches one character only. For example, a search for “I?” matches I and In but not Inn, India, or Intrepid. When using a wildcard in your select query criteria, you must also include the word Like, as in Like I*.

MODIFYING A QUERY

If you want to make any changes to a select query, such as adding or removing fields or revising criteria, double-click the query name in the Navigation pane and then click View on the Home tab to switch to Design view, where you can make modifications. The Design grid is much like any other table. You can drag a border to change the col- umn widths, select a column and press Delete to remove it, or drag columns to reorder the results of your query Datasheet view. To delete a query, right-click the object in the Navigation pane and then click Delete.

SQL

Structured query language (SQL) is the basis of every select query. Access converts your select query into an SQL com- mand that gets executed when you run the query. There are

many courses designed to teach SQL, and there are many job oppor- tunities for those who understand SQL. To see an example of SQL, open one of your select queries and then click View > SQL View on the Home tab.

APPLY YOUR KNOWLEDGE

Follow the steps below to create select queries for the Healthy Home Care database you modified in the previous assignment.

1. Start Access and open Healthy Home Care.accdb. There are three table objects and three form objects.

2. To determine which clients have scheduled a companionship appointment, on the Create tab, click Query Design. A select query is displayed in Design view with a dialog box.

3. The query should display the first and last name of the client and the appointment time for a companionship visit, so you’ll need fields from all three tables. Click Clients and then Add. Click Schedules and then Add. Add the Services table and close the dialog box. Your select query should look similar to Figure 38.

FIGURE 38—Add Clients, Schedules, and Services

(Continued)

ASSIGNMENT 5

DISCOVER MORE

4. From the Clients table, drag the First Name field name into the first Field box.

5. Continue to drag the fields into Field boxes until your design grid looks similar to Figure 39.

FIGURE 39—Drag fields into the new query fields.

6. Click in the Sort box for the Last Name field and then select Ascending.

7. In the Service Name Criteria box, type “Companionship.” Your design grid should look similar to Figure 40.

FIGURE 40—Add criteria.

8. On the Quick Access Toolbar, click Save. A dialog box is displayed.

9. Type “Companionship Clients” and click OK.

10. On the Query Tools Design tab, click Run. The select query results are displayed in Datasheet view, similar to Figure 41.

FIGURE 41—Your Query Results

11. Close the select query. Note the query object in the Navigation pane.

ASSIGNMENT 5

DISCOVER MORE

RANGE QUERIES

A range query uses criteria to select data within a range of values. Range queries use relational operators, including <, >, <=, >=, and <> to narrow the criteria. For example, the Orders After 10/12/17 query uses criteria with the > relational operator (Figure 42). Access automatically encloses date and time criteria within # signs.

COMPLEX QUERIES

You can specify criteria for any number of fields. When you select data based on multi- ple criteria, you create a complex query that uses the AND and OR logical operators. In some cases, the logical comparison is implied, as in the query in Figure 43 that selects orders placed after 10/12/17 for the G200 product line. The program automatically uses AND when you specify two criteria in different fields. When querying within a single field, you must enter an operator. For example, the query in Figure 44 uses the AND logical operator to select orders placed within a range of dates.

FIGURE 42—Example of a Range Query

The OR logical operator requires that just one of the criteria be met. The OR operator is built into the query framework. For example, the query in Figure 45 selects products that are green or white.

FIGURE 43—This query displays only those orders that were placed after 10/12/17 for a G200 product.

FIGURE 44—This query displays only those orders that were placed after 10/7/17 and before 10/12/17.

PARAMETER QUERY

A parameter query prompts the user to enter criteria, called a parameter, when running the query. This type of query is useful when you want to run the same query over and over with different criteria for a particular field. For example, Clever Acme Corp. can run the query in Figure 46 to get the orders for any product ID entered. When you run a parameter query, you’ll get a dialog box similar to that in Figure 47. For this query, if you type “G200-B,” the results shown in Figure 48 are displayed in Datasheet view.

FIGURE 45—Adding Criteria of Green or White

FIGURE 46—Parameter Query

FIGURE 48—Results of the Parameter Query

FIGURE 47—The Parameter Dialog Box

APPLY YOUR KNOWLEDGE

In this exercise, you’ll create more queries for the Healthy Home Care database. Follow the steps below to create range and complex queries.

1. Start Access and open Healthy Home Care.accdb. There are table, form, and query objects.

2. To find out which clients have appointments after September 19, on the Create tab, click Query Design and then add all three tables to the query.

3. Using Figure 49 as a guide, drag fields into the design grid, type criteria, and select a Sort option.

FIGURE 49—Your Range Query Design

4. Save the query, naming it “Appts After 9/19/17.”

(Continued)

ASSIGNMENT 5

DISCOVER MORE

5. On the Query Tools Design tab, click Run. Your query Datasheet view should look similar to Figure 50.

FIGURE 50—Results of the Appts After 9/19/17 Range Query

6. Close the Appts After 9/16/17 query.

7. To find out the schedules for the physical therapist and visiting nurse, on the Create tab, click Query Design and then add the Schedules and Services tables to the query.

8. Using Figure 51 as a guide, drag fields into the design grid, type criteria, and select a Sort option.

9. Save the query, naming it “PT and Nurse Appts.”

(Continued)

ASSIGNMENT 5

DISCOVER MORE

FIGURE 51—The OR Query Design

10. On the Query Tools Design tab, click Run. Your query Datasheet view should look similar to Figure 52.

11. Close the PT and Nurse Appts query.

12. To find out which services are scheduled between 9:00 AM and 11:00 AM, create a new query and add the Schedules and Services tables.

13. Using Figure 53 as a guide, drag fields into the design grid, type criteria, and select a Sort option.

14. Save the query, naming it “9:00-11:00 Appts.”

(Continued)

ASSIGNMENT 5

DISCOVER MORE

FIGURE 52—Results of the PT and Nurse Appts Query

FIGURE 53—The AND Query Design

15. On the Query Tools Design tab, click Run. Your query Datasheet view should look similar to Figure 54.

16. Close the query.

17. To be able to print a daily schedule with times, clients, and addresses for any date you enter, create a new query and add all three tables.

18. Using Figure 55 as a guide, drag fields into the design grid, type a prompt, and select a Sort option.

FIGURE 55—The Parameter Query Design

19. Save the query, naming it “Daily Appointments.”

(Continued)

ASSIGNMENT 5

DISCOVER MORE

FIGURE 54—Results of the 9:00– 11:00 Appts Query

20. On the Query Tools Design tab, click Run. A dialog box is displayed. Type “9/17/2017” and then click OK. Your query Datasheet view should look similar to Figure 56.

FIGURE 56—Results of the Parameter Query

21. Run the Daily Appointments query a few more times, trying different dates.

22. Close the query.

23. Close the Healthy Home Care database.

CREATING A CALCULATED FIELD WITH A SELECT QUERY

A select query can also be used to generate additional data, such as tax on a sale or profit (price – cost). Define a calculated field in the select query Design grid; the results are displayed when the query is run. For example, the Shipping Cost query calculates the cost of shipping for an order. Clever Acme Corp. charges a $1.50 flat fee plus an addi- tional $0.50 for every item in the order (Figure 57). Note that field names are enclosed in brackets [ ]. If necessary, you can further distinguish a field by including its table name, as in [Orders.Quantity]. When run, the query Datasheet view looks like Figure 58.

ASSIGNMENT 5

DISCOVER MORE

FIGURE 57—Example of a Calculated Field

FIGURE 58—Results of the Query

To format the results of a calculated field, right-click the field in the design grid and click Properties. A Property sheet is displayed, where you can select a display format from the Format options.

APPLY YOUR KNOWLEDGE

In this exercise, you’ll create a query that gives a more complete picture of the Healthy Home Care schedule. Follow the steps below to create a query with a calculated field for the Healthy Home Care database.

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

(Continued)

ASSIGNMENT 5

DISCOVER MORE

2. To add a Completion Time field to a query that includes schedule information, on the Create tab, click Query Design and then add all three tables to the query.

3. The completion time for a service is calculated by adding the service time to the start time. Because the service time is in minutes, it must be converted to a decimal time format by dividing Time (minutes) by the number of minutes in a day (24*60). Using Figure 59 as a guide, drag fields into the design grid, select a Sort option, and type the expression for the calculated field.

FIGURE 59—The Calculated Field Design View

4. Right-click the calculated field and click Properties. A Property sheet is displayed.

5. In the Property sheet, click Format, click the drop-down arrow, and click Medium Time. Click Close.

6. Save the query, naming it “Schedule with Completion Times.”

(Continued)

ASSIGNMENT 5

DISCOVER MORE

7. On the Query Tools Design tab, click Run. Your query Datasheet view should look similar to Figure 60.

FIGURE 60—Results of the Query

8. Close the query.

9. Close the Healthy Home Care database.

THE QUERY WIZARD

You can use the Query Wizard to get started with a query. On the Create tab, click Query Wizard and then use the dialog box to add the fields you want in your query. Click Modify the query design in the last dialog box to open query Design view, where you can add sorting options and criteria.

Queries can be used to update records, delete records, display a datasheet with summa- ries, and more. In the Tell Me box, type “Introduction to Queries” and explore the many applications of queries.

ASSIGNMENT 5

DISCOVER MORE

COMPLEX CRITERIA

When working with large databases, it can be difficult to determine if your query results are accurate, especially when query criteria become complicated. Think about the criteria needed to find data for three different product IDs that were sold between two dates in quantities less than five in four different cities. Complex! What can you do to make sure that criteria for your queries are specifying the appropriate records?

ASSIGNMENT 5

REFLECT AND RESPOND

1. ________ are used to specify which data is retrieved in a select query.

2. When you run a select query, it’s displayed in _______ view.

3. How should you not specify numeric data in query criteria?

4. Which wildcard is best if you want to match product IDs G200, G300, and G400?

5. What’s the difference between using a relational operator and a logical operator?

Click here to check your answers.

ASSIGNMENT 5

SELF-CHECK

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

Reports are used to organize, group, format, and summarize data in a way that’s easy to understand. They’re based on a table or query and provide an effective way to present your data. Reports can also be used to generate labels.

CREATING A REPORT

Along with formatting data from a table or query, you can use a report to group data and include summaries such as totals and averages. A report is an RDBMS object that can be saved with the database and then displayed at any time by opening it from the Navigation pane. Because a report is often printed or emailed as a PDF, you should format it with page size in mind. The Clever Acme Corp. Orders report in Figure 61 is an example of how data from a table can be presented.

ASSIGNMENT 6

REPORTS

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

6. Summarize data in a report

The terms you need to know for this assignment are

ASSIGNMENT 6

VOCABULARY

OO Report

OO Report sections

OO Group

FIGURE 61—Presenting Data in a Report

To create a report, click a table or query name in the Navigation pane and then click Report on the Create tab. For example, a report based on the Clever Acme Corp. Products tables would look similar to Figure 62. A newly created report is displayed in Layout view with data in a tabular format of rows and columns. Figure 63 illustrates the different report sections in the Clever Acme Corp. Orders report.

FIGURE 62—This report is based on Clever Acme Corp. products.

FIGURE 63—Sections of a Report

MODIFYING A REPORT IN LAYOUT VIEW

Modifying a report is easiest from Layout view. To display a report in this view, first open it, if necessary, and then click View > Layout View on the Home tab. This command is also available on the Report Layout Tools Design tab.

You can group data with the same entries for a specified field to make your report easier to read and understand. For example, if you were to prepare a report for a fruit database, you might group as follows:

OO Apples

OO Red Delicious

OO Macintosh

OO Granny Smith

OO Citrus Fruits

OO Orange

OO Grapefruit

OO Lemon

Specifying a sort gives your data a more logical structure. To group and sort data, click Group & Sort on the Report Layout Tools Design tab to display the Group, Sort, and Total pane at the bottom of your report Layout view (Figure 64). In the Group, Sort, and Total pane, click Add a group and then click a field by which to group the report. Click Add a sort and then click a field on which to base your sort. The Products report, grouped by product name and sorted by color, is shown in Figure 64. Notice that the field used for grouping was moved to the left side of the report. If you group by numeric data, you can choose to add calculations by using commands in the Group on row of the pane.

FIGURE 64—This report uses grouping and sorting to make it easier to understand.

Following are more features of the Report function:

OO To remove a group or sort, click the row in the Group, Sort, and Total pane and then click Delete at the right side.

OO The heavy dashed line on the right of the report indicates the width of a printed page. Change field widths as necessary to format your report for one page.

OO Click a box and then drag a side to change its width or height.

The Report Layout Tools Format tab has many options for formatting your report (Figure 65). To apply formatting, click a field name, data value, or row and then choose the fonts, colors, and formats you want. Commands for applying a theme, adding an image, or changing the date and time format are available in the Report Layout Tools Design tab. Note that if the report is likely to be printed on a single color printer, you should make sure that any colors used will show up well on a printout. You should also think about which fonts and sizes will make the report easier to read.

FIGURE 65—Report Layout Tools Format Tab

If your report is very wide, you may want to change the page orientation or decrease the margins. Click the Report Layout Tools Page Setup tab to apply these commands.

REPORT VIEW AND PRINT PREVIEW

When you’ve completed your report layout, click View > Report View on the Report Layout Tools Design tab. Your formatted report can’t be edited from this view.

When you’re ready to print your report, click View > Print Preview on the Home tab or the Report Layout Tools Design tab. In this view, you’ll see how your report will look when printed. From here you can decide if you need to modify page setup options before printing, saving as a PDF, or emailing the report.

APPLY YOUR KNOWLEDGE

In this exercise, you’ll create reports for Appointments and Daily Appointments from queries in the Healthy Home Care database. Follow the steps below to create formatted reports for the Healthy Home Care database you modified in the previous assignment:

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

(Continued)

ASSIGNMENT 6

DISCOVER MORE

2. To generate a report that shows appointment times, client names, and charges, on the Create tab, click Query Design. Using Figure 66 as a guide, add all three tables and drag the fields into the design grid.

FIGURE 66—The Appointments Query

3. Save the query, naming it “Appointments,” and then run it. The corresponding Datasheet view presents a lot of unorganized data.

4. Close the Appointments query.

5. With the Appointments query selected in the Navigation pane, on the Create tab, click Report. A tabular report corresponding to the Appointments query is dis- played (Figure 67). Note the Report Layout Tools tabs that are now available on the Ribbon.

(Continued)

ASSIGNMENT 6

DISCOVER MORE

FIGURE 67—The Initial Appointments Report

6. Before grouping and sorting, you need to format widths so that the appointment dates are displayed properly and your report data is on one page. Click the first Appointment box. It displays ##### because the cell isn’t wide enough to display the value. Drag the right edge of the box to size all of the boxes until they display the dates.

7. Decrease the widths of the remaining fields until they all fit within the page break dashed line (Figure 68).

(Continued)

ASSIGNMENT 6

DISCOVER MORE

FIGURE 68—Make sure all of the fields fit on the page.

8. Scroll down and change the height of the Charge Total, if necessary, so that the number is completely displayed.

9. Drag the “Page 1 of 1” page footer so that it’s approximately centered below the report data. Notice that the report appears to fit on one page now (Figure 69).

(Continued)

ASSIGNMENT 6

DISCOVER MORE

FIGURE 69—The Report So Far

10. If the Group, Sort, and Total pane isn’t displayed below the report, click Group & Sort on the Report Layout Tools Design tab.

11. Click Add a group and click Service Name. The Service Name field is moved to the left and the records are grouped by service.

12. Click Add a sort and click Appointment. The groups are sorted by appointment.

13. To see subtotals for each service, click the Group on row in the Group, Sort, and Total pane.

(Continued)

ASSIGNMENT 6

DISCOVER MORE

14. Click More to see more Group on options. Click the drop-down arrow next to with Charge totaled. In the menu, select Charge from the Total On box and then click Show subtotal in group footer (Figure 70). Subtotals are added to each service.

FIGURE 70—Show Subtotal in Group Footer

15. Click one of the subtotals and drag the bottom edge until the subtotal is correctly displayed.

16. To show a count of how many appointments are in your report, in the Group on row, click More.

17. Click the drop-down arrow next to with Charge totaled, change the Total On to Appointment, change Type to Count Values, and then click Show Grand Total (Figure 71).

(Continued)

ASSIGNMENT 6

DISCOVER MORE

FIGURE 71—Setting the Total Display

18. At the top of the report, click to the right of the page header column titles to select all the titles.

19. Using commands on the Report Layout Tools Format tab, change the titles to 14-point bold.

20. Click Appointments in the report header and format it as 24-point bold.

21. On the Report Layout Tools Design tab, click View > Report View. The completed report is displayed.

22. On the Home tab, click View > Print Preview. Carefully inspect your report. If any field boxes need to be resized or if the footer doesn’t look centered, click Close Print Preview and then go to Report Layout view to make changes. Repeat this process until you’re satisfied with your report.

23. Save the report, naming it “Appointments.” Your report should look similar to Figure 72.

(Continued)

ASSIGNMENT 6

DISCOVER MORE

FIGURE 72—The Final Appointments Report

(Continued)

ASSIGNMENT 6

DISCOVER MORE

24. Close the report.

25. In the Navigation pane, click the Daily Appointments parameter query.

26. On the Create tab, click Report. A dialog box is displayed, prompting you for the date to use for the report records.

27. Type “9/20/17” and click OK.

28. Size the data boxes so that the data is properly displayed, and center the footer so that your report looks similar to Figure 73.

FIGURE 73—Your report layout should look like this.

29. If the Group, Sort, and Total pane isn’t already displayed, on the Design tab, click Group & Sort.

30. In the Group, Sort, and Total pane, click Add a sort and click Time. The report is sorted chronologically.

31. Save the report, naming it “Daily Appointments.”

32. Close the report.

33. In the Navigation pane, double-click the Daily Appointments report to open it. A dialog box is displayed.

34. Type “9/18/17” and then click OK. A report for this date is displayed.

35. Close the report and then close the Healthy Home Care database.

ASSIGNMENT 6

DISCOVER MORE

THE REPORT WIZARD

On the Create tab, click Report Wizard to display a series of dialog boxes for gener- ating a report. The dialog boxes will prompt you for grouping, sorting, and summarizing options.

CREATING LABELS

Whether for labeling folders with client names, adding patient information to a chart, or creating mailing labels to send customers a brochure, businesses often have a need for labels. With Access, you can make labels directly from a table or query with a labels report.

To create a labels report, click a table or query name in the Navigation pane and then click Labels on the Create tab. You’ll see a Labels Wizard dialog box for selecting the label size. Label layouts are usually designated by the product number of the label blanks. If you have a package of labels, the product number is normally on the front of the package.

As you proceed through the Labels Wizard, you can select the font and size of label text before you select the fields to be used for the labels. A labels report is saved with your database and can be opened at any time from the Navigation pane.

APPLY YOUR KNOWLEDGE

In this exercise, you’ll create a labels report from the Clients table in the Healthy Home Care database. Follow the steps below to create formatted labels for the Healthy Home Care database:

1. Start Access and open Healthy Home Care.accdb, if necessary.

(Continued)

ASSIGNMENT 6

DISCOVER MORE

2. To make labels with client names and mailing addresses, on the Create tab, click Labels. The first Label Wizard dialog box is displayed (Figure 74).

FIGURE 74—The Label Wizard

3. For this demonstration, you’ll use the first label selected, C2160. Note that if you had a different label type, you would change the Filter by manufacturer option to match the brand of label before scrolling through the list of product numbers.

4. Click Next. Font options are displayed. Change Font Size to 9.

5. Click Next. Available fields are displayed. Click First Name and then click >. The field is moved into the Prototype label box.

6. Press the spacebar to insert a space after the first name.

7. Move the Last Name field over and then press Enter.

8. Move Address over and press Enter.

(Continued)

ASSIGNMENT 6

DISCOVER MORE

9. Move City over and type a comma followed by a space.

10. Move State over and then type a space before moving Zip over. Your prototype label should look similar to Figure 75.

11. Click Next. Sort your labels by the Last Name field.

12. Name the report “Client labels.” With the See the labels as they’ll look printed option selected, click Finish. Your labels are displayed in Print Preview (Figure 76).

FIGURE 76—The Labels Preview

13. Close Print Preview and then close the report.

14. Close the Healthy Home Care database.

ASSIGNMENT 6

DISCOVER MORE

FIGURE 75—The Prototype Label

REPORT CONSIDERATIONS

When deciding to present a query or table as a report, what considerations do you need to make regarding the reader of a printed report?

ASSIGNMENT 6

REFLECT AND RESPOND

1. Name three reasons for creating a report.

2. Which section of a report displays a grand total?

3. Which view allows you to see a report, but doesn’t allow changes to be made?

4. What is one important reason for changing the widths of data boxes in a report?

5. How do you create a report to be printed as labels?

The following multiple-choice questions have been taken from the entire course, not just this assignment. Answer each of the questions.

6. A database is a way to manage a

a. set of formulas.

b. sheet of data.

c. collection of related data.

d. way to display a spreadsheet.

(Continued)

ASSIGNMENT 6

SELF-CHECK

7. A form object is for

a. storing data.

b. data entry.

c. creating an organized report.

d. sorting records.

8. The Navigation Pane contains

a. database objects.

b. links to other databases.

c. help content.

d. shortcuts to Ribbon commands.

9. What is the purpose of the primary key?

a. It creates duplicate records for a table.

b. It’s the field on which to base a sort.

c. It’s the field used for a filter.

d. It makes every record in a table unique.

10. A good database design principle is to

a. duplicate fields in as many tables as possible.

b. create field names with multiple words.

c. eliminate data redundancy.

d. make sure that tables are independent of each other, with no related fields.

11. A _______ field stores a value from another table.

a. Lookup

b. Index

c. Primary

d. Data

(Continued)

ASSIGNMENT 6

SELF-CHECK

12. A new table is initially displayed in _______ view.

a. Design c. Layout

b. Datasheet d. Format

13. Which field type is a good choice for ensuring that ID fields contain unique entries?

a. Hyperlink c. Date/Time

b. Text d. AutoNumber

14. When you populate a database, you

a. change the field names in a table.

b. add a new table to a database.

c. create a form.

d. add records to a table.

15. A form is based on

a. a table’s fields.

b. the table names in a database.

c. the primary keys for each table in a database.

d. a set of field names that you enter when you create the field.

16. To ensure that your database is reliable, you should

a. create tables with Number fields only.

b. use a hyperlink field in each table.

c. use the Tab key when entering data. d. check the spelling of every record.

17. Datasheet view is used to display

a. a form.

b. a relationship.

c. every record in a table at once.

d. field properties.

(Continued)

ASSIGNMENT 6

SELF-CHECK

18. Placing records in a specific order is called

a. sorting. c. filtering.

b. searching. d. querying.

19. When your records are organized in order from highest to lowest, they’re in _______ order.

a. hi-lo c. descending

b. ascending d. chronological

20. When a table displays only records that match certain criteria, a _______ has been applied.

a. filter c. key

b. record d. match

21. A database object that retrieves data matching specified criteria is called a

a. find. c. select filter.

b. select query. d. parameter.

22. Which operator indicates a range query?

a. + c. >=

b. OR d. !

23. A query that prompts for data when it’s run is called a _______ query.

a. data c. complex

b. check d. parameter

24. A report with data organized by matching entries for a field has been

a. sorted. c. totaled.

b. grouped. d. summarized.

25. A report can include calculations such as totals, averages, and counts for what kind of data?

a. Numeric data only c. Text data only

b. Date/time data only d. Numeric, date/time, and text data

Click here to check your answers.

ASSIGNMENT 6

SELF-CHECK

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

ASSIGNMENT 1

OO The Access Start screen displays links to spreadsheets and a link to create a blank desktop database.

OO Database objects include tables, forms, queries, and reports.

OO Tables are database objects that store all the data.

OO Forms are database objects for data entry.

OO Queries are database objects that search and extract data.

OO Reports format and present data.

OO The File tab displays the Backstage view, where you can open, save, print, and close a database.

OO The Navigation pane is used to manage the objects of a database.

OO You must provide a descriptive name for a database when it’s created because changes are saved automatically.

OO A new table is displayed in Datasheet view.

OO The Tab and Enter keys are used to move from one data entry field to another.

ASSIGNMENT 2

OO Before creating a database, you must create the database design.

OO Database design requires defining the purpose, dividing data, choosing primary keys, and specifying table relationships.

OO Tables are used to store all the data for a database. They’re organized into rows and columns. Rows are records and columns are fields.

OO The AutoNumber field type can be used to ensure that a field has a unique value.

OO A Lookup field stores a value from another table and is created by selecting Lookup Wizard as the data type in Design view.

OO Validation rules help enforce data integrity by requiring it to fit within certain param- eters. They’re created for a field in Design view.

MICROSOFT ACCESS 2016

KEY POINTS

OO Input masks guide data entry by displaying placeholder characters like under- scores, dashes, or asterisks to indicate the type of data expected.

OO Designate a field as the primary key using the Design tab.

OO Relationships specify how one field in a table corresponds to a field in a different table and are automatically defined when you create lookup fields.

OO A relationship should be one-to-many.

OO To specify how one table is related to another, click Relationships on the Database Tools tab.

OO Referential integrity is a feature of Access that notifies you when a deletion or other change in one table will create invalid records in a related table.

OO If a table doesn’t relate to any other table in the database, then it doesn’t belong in the database.

ASSIGNMENT 3

OO Database forms make data entry easier and faster.

OO Create a form for a table by selecting the table in the Navigation pane and then clicking Form on the Create tab.

OO In Design view, you can change entry box sizes and apply formats.

OO The Form Layout Tools Design tab has commands for adding images, date, and time to a form.

OO Click New (blank) record in the record controls at the bottom of a form to add a new record.

OO The Tab, Ctrl+Tab, and Enter keys move the insertion point from entry box to entry box on a form.

OO You should always use the Spelling command to ensure that your data is reliable.

OO To edit a hyperlink entry, right-click the entry and then click Hyperlink > Edit Hyperlink.

OO If you need to delete an entire record from a database, click Delete > Delete Record on the Home tab. Deleted records can’t be recovered.

OO In Datasheet view, you can see all the records for a table at once. Subdatasheets display related records.

OO You can format a table in Datasheet view by dragging or double-clicking column boundaries.

OO Before printing a table, click Print Preview to see how it will look printed.

OO From Print Preview, you can apply page formatting, save a table as a PDF, or email it as an attachment.

ASSIGNMENT 4

OO Sort the records in a table by clicking the drop-down arrow next to a field name and then selecting a sort command.

OO Tables can be sorted in ascending order (low to high), descending order (high to low), or chronological order (by date or time).

OO To clear a sort and put records back to the original order, click Remove Sort on the Home tab.

OO Apply a filter to display records that meet certain criteria. Click the field name to base the filter on and then use a filter command in the drop-down menu.

OO To clear a filter and again display all records, click Toggle Filter on the Home tab. Or, click the funnel in the field name and click Clear filter.

ASSIGNMENT 5

OO Create a select query by clicking Query Design on the Create tab.

OO Select queries join tables through their primary keys and use specified criteria to select matching data from the joined records.

OO In Design view, drag a field from a table into the design grid to display it in query Datasheet view.

OO Click Run on the Query Tools Design tab to run a query and display the results in Datasheet view.

OO Wildcard characters * and ? can be used to specify query criteria with several pos- sible matches.

OO The * character matches any number of characters or none at all. The ? character matches one character or none at all.

OO To edit a query, open it and then click View on the Home tab.

OO A range query specifies criteria using relational operators such as <, >, <=, >=, and <>.

OO Create a complex query by using the logical operators AND and OR in criteria.

OO The OR logical operator requires that just one of the criteria be met. The AND logi- cal operator requires that both of the criteria be met.

OO A parameter query is a more general purpose query that prompts for a data value when the query is run.

OO A calculated field can be included as part of a select query. It displays the results of its calculation when the query is run.

OO The Query Wizard can help you build a query.

ASSIGNMENT 6

OO Reports are used to organize, group, format, and summarize data in a way that’s easy to understand. They’re based on a table or query.

OO Create a report by clicking Report on the Create tab.

OO The easiest way to format a report is in Layout view.

OO Group data with the same entries for a specified field to make a report easier to read and understand.

OO Display the Group, Sort, and Totals pane by clicking Group & Sort on the Report Layout Tools Design tab.

OO In the Group, Sort, and Totals pane click Add a sort to specify a sort or Add a group to specify a grouping option.

OO You can add summaries, such as totals, averages, and counts, to a group by using options in the Group On row in the Group, Sort, and Totals pane.

OO Labels can be created from a table or query by using the Labels Wizard, which is started when you click Labels on the Create tab.

Active field The field in Table Design view that has a highlighted box to its left.

AND logical operator Used to specify criteria that selects data matching two or more values.

Ascending order Records sorted from low to high based on a specific field.

AutoNumber A field type that stores an automatically generated number that’s one greater than the last field entry.

Calculated field A field defined in a select query design grid with results displayed in query Datasheet view.

Chronological order Records sorted from low to high or high to low based on a date/ time field.

Complex query A query that selects data based on multiple criteria.

Control The type of object used for data entry.

Data integrity The accuracy and consistency of data.

Data redundancy Unnecessary data duplication.

Database A collection of related data.

Datasheet view A table displayed as rows and columns.

Descending order Records sorted from high to low based on a specific field.

Design view The view in which fields for a table are defined.

Entry The data for a field.

Entry box The location for a data entry on a field.

Field Contains data about a single aspect of the items in a table. A column in a table.

Field properties The name, type, size, format, and decimal places for a field.

Filter To display only records that match specified criteria.

Foreign key A field in a table that’s a primary key in another table.

Form object An RDBMS object used for data entry.

Group To place together records with the same entries for a specified field.

MICROSOFT ACCESS 2016

GLOSSARY

Logical operator A symbol used to connect two or more search criteria to narrow the search.

Lookup field A field type that stores a value that originates from another table.

Normalization The process of organizing data to reduce redundancy.

One-to-many A relationship defined by a unique data entry in one table that can occur many times in a related table.

OR logical operator Used to specify criteria that selects data matching one value or another, but not both.

Parameter A value typed into a dialog box that’s displayed when a parameter query is run.

Populating a database Adding records to a database.

Primary key The field in a table that ensures that a record is unique.

Query object An RDBMS object for displaying the results of a question based on the stored data.

Range query A select query that uses criteria covering a range of values.

Record Specific data for several fields in a table. A row in a table.

Record controls Buttons at the bottom of a form for navigating through records and for displaying a new blank record.

Referential integrity When foreign keys always refer to valid primary keys in a related table.

Relational database management system (RDBMS) An application used to create and manage a database.

Report An RDBMS object that is used to organize, group, format, and summarize data.

Report object An RDBMS object for formatting and presenting data.

Report sections Areas of a report that contain the headers, detail, and footers.

Select query An RDBMS object that‘s used to retrieve data matching specified criteria.

Sort To order records in a table based on the data in a specific field.

Table object An RDBMS object for storing data in a database. A relational database should contain at least two tables.

Update To change the data in a record.

Validation rules Rules that check an entry for a value outside a specified range.

Wildcard characters Characters that can be used to generalize select query criteria. To match any number of characters or no character at all, use *. To match any one char- acter or no character at all, use ?.