Access 2016 Advanced

ACCESS 2016

A DVANCED

OFFICEPRO, Inc. 8 Granite Pl. Suite 26 Gaithersburg, MD 20878 MD: (301) 468-3312 DC: (202) 347-1114 VA: (703) 922-0400 Fax: (301) 263-6879 www.officeproinc.com help@officeproinc.com

A CCESS 2016

A DVANCED

Presented by OFFICEPRO, Inc.

Manual # MS16-530-01

Copyright 2014, all rights reserved. Material may not be reproduced in any form without written approval from OFFICEPRO, Inc.

About OFFICEPRO – We Change Lives! OFFICEPRO is a software-training company that specializes in providing high-quality, cost- effective, training services to our clients. OFFICEPRO has been providing computer training since 1984. We assist our clients in determining the most advantageous training solutions for their particular requirements and work with them to implement these solutions effectively in a flawless manner.

Student Expectations

Instructors OFFICEPRO’s instructors are simply the best in the business. They each have at least two years of stand-up training experience as well as “real world” experience in the applications they teach. The instructor teaching you today was chosen specifically for your class. When assigning an instructor we consider many variables. The instructor is available to answer questions throughout the day and after class.

Students can expect the following from an OFFICEPRO class:

Clearly stated class objectives

• •

The opportunity to express what they would like to achieve in class

Relevant training

• •

Hands-on training that allows students to learn by doing, not viewing A schedule for the day including lunch and break times

•

The chance to evaluate the instructor and the overall class.

OFFICEPRO, Inc.

Page i

What to expect from OFFICEPRO?

Exercise Files

Training Manuals Contain •Discussions & Procedures •Notes & Warnings •Step-by-Steps

•Download from www.officeproinc.com or •Book publisher's website

Course Levels •2 to 3 levels of training for each topic •Call us at 301-468-3312 to learn how to further build your skills

Technical Support •Receive 1 year of post-training support on topics covered in class • Email: help@officeproinc.com • Phone: 301-468-3312

Microsoft Office Specialist Certifications •Validate skills needed to use features of Microsoft Office applications •Contact OFFICEPRO to schedule an exam!

Copyright & Trademarks Copyright 2014 by OFFICEPRO, Inc.. All rights reserved. Information in this document is subject to change without notice and does not represent a commitment on the part of OFFICEPRO. Trademarked names appear throughout this book. Rather than list the names and entities that own the trademarks or insert a trademark symbol with each mention of the trademark name, OFFICEPRO states that it is using the names for editorial purposes and to the benefit of the trademark owner with no intention of infringing upon the trademark. Disclaimer OFFICEPRO has made every effort to ensure the accuracy of this document. If you should discover any discrepancies, please notify us immediately.

Page ii

OFFICEPRO, Inc.

Table of Contents

ABOUT OFFICEPRO – WE CHANGE LIVES! ...............................................................I

WHAT TO EXPECT FROM OFFICEPRO?...................................................................II

TABLE OF CONTENTS...........................................................................................III

LESSON 1 -

USING SUBFORMS/SUBREPORTS....................................................1 Working with Subforms/Subreports ........................................................................2 Creating a Subform/Subreport.................................................................................3 Editing the Layout of a Subform...............................................................................7 Displaying a Subform in Datasheet View..................................................................8 Displaying a Subform/Subreport Total...................................................................10 Exercise...................................................................................................................14 ANALYZING TABLES......................................................................15 Analyzing a Table....................................................................................................16 Using Relationships in Splits ...................................................................................16 Using the Table Analyzer Wizard............................................................................16 Using the Performance Analyzer............................................................................21 Exercise...................................................................................................................24 CUSTOMIZING THE NAVIGATION PANE.........................................25 Creating Custom Categories ...................................................................................26 Creating Custom Groups ........................................................................................28 Adding Database Objects to a Group .....................................................................29 Showing/Hiding the Unassigned Objects Group ....................................................30 Deleting Objects from a Group...............................................................................31 Exercise...................................................................................................................33 USING OTHER FORM TECHNIQUES................................................35 Creating a Split Form ..............................................................................................36 Converting an Existing Form to a Split Form ..........................................................37 Editing a Split Form.................................................................................................38 Adding a Command Button ....................................................................................40 Saving a Form as a Report ......................................................................................43

LESSON 2 -

LESSON 3 -

LESSON 4 -

OFFICEPRO, Inc.

Page iii

Exercise...................................................................................................................46

LESSON 5 -

USING CHARTS.............................................................................47 Working with Charts...............................................................................................48 Adding a Chart to a Form or Report .......................................................................51 Using Microsoft Graph to Edit ................................................................................53 Changing the Chart Type ........................................................................................56 Changing the Chart Title .........................................................................................59 Formatting the Chart Title ......................................................................................60 Adding Data Labels to a Chart ................................................................................61 Exercise...................................................................................................................64 WORKING WITH INDEXES.............................................................65 Viewing Indexes......................................................................................................66 Creating a Single Field Index...................................................................................67 Creating a Multiple Field Index...............................................................................69 Deleting an Index....................................................................................................71 Creating a Multiple Field Primary Key ....................................................................72 Exercise...................................................................................................................75 EXPORTING DATA ........................................................................77 Exporting Data to an Excel Workbook....................................................................78 Saving Export Setup................................................................................................80 Dragging and Dropping Data into Excel..................................................................80 Creating Word Mail Merge Documents .................................................................82 Exercise...................................................................................................................84 USING ACCESS AND THE INTERNET ...............................................85 Working with Hyperlinks ........................................................................................86 Creating a Hyperlink Field.......................................................................................87 Inserting a Hyperlink Field......................................................................................88 Editing a Hyperlink Field .........................................................................................90 Deleting a Hyperlink Field.......................................................................................91 Exercise...................................................................................................................93

LESSON 6 -

LESSON 7 -

LESSON 8 -

LESSON 9 -

USING ACCESS DATABASE SECURITY.............................................95

Page iv

OFFICEPRO, Inc.

Encrypting a Database with a Password.................................................................96 Opening and Decrypting a Database......................................................................98 Removing a Database Password.............................................................................99 Exercise.................................................................................................................101 CREATING MACROS ................................................................... 103 Working with Macros ...........................................................................................104 Opening the Macro designer Window .................................................................104 Creating a Macro ..................................................................................................106 Saving a Macro .....................................................................................................111 Using Single Step Mode for Testing......................................................................113 Running a Macro ..................................................................................................115 Editing an Existing Macro .....................................................................................116 Exercise.................................................................................................................118 USING MACROS ......................................................................... 119 Using Properties ...................................................................................................120 Creating Embedded Macros .................................................................................120 Assigning a Macro to a Control ............................................................................122 Creating a Command Button ................................................................................125 Adding a Condition to a Macro.............................................................................128 Creating a Group Macro .......................................................................................131 Creating an Autoexec Macro ................................................................................134 Exercise.................................................................................................................136 USING SWITCHBOARDS.............................................................. 137 Opening and Using a Switchboard .......................................................................138 Creating a Switchboard Form...............................................................................140 Adding a Command Button to a Switchboard......................................................144 Setting Startup Options ........................................................................................147 Exercise.................................................................................................................150

LESSON 10 -

LESSON 11 -

LESSON 12 -

OFFICEPRO, Inc.

Page v

Lesson 1 - U SING S UBFORMS /S UBREPORTS

This lesson covers the following objectives:

Working with Subforms/Subreports Creating a Subform/Subreport Editing the Layout of a Subform Displaying a Subform in Datasheet View Displaying a Subform/Subreport Total Exercise

Lesson 1 - Using Subforms/Subreports

Access 2016

W ORKING WITH S UBFORMS /S UBREPORTS



Discussion

You can display a form or report within a form or report, which then creates a main form or report and a corresponding subform or subreport. The main form or report includes information from one table and the subform or subreport includes information from a second, related table.

Show a form/report within a form/report to more effectively show the linked data from tables with one-to-many relationships. The main form/report and the subform/subreport are linked by a common field between the tables. The main form/report represents the one side of the one-to-many relationship and the subform/subreport represents the many side. When viewing a record in the main form/report (one side relationship), the subform/subreport displays the related records from another table (many side relationship). For example, you have a Customer table and an Orders table. Each customer has several orders. In the Customer form (bound to the Customer table), you can include an Orders subform (bound to the Orders table). The tables are most likely linked by a Customer ID field which is present in both tables. As you view a customer’s record in the main form, the customer’s order appears in the subform. When you move to the next customer record in the main form, the order records in the subform are updated to display that customer’s orders. Just as you can manipulate table data in a form, you can also manipulate the related table data using the subform. Using the same example, you can edit the customer records or the order records.

Display subform data in Form view (one record at a time) or Datasheet view (many records displayed at once), while subreport data can only be displayed in print preview. You can also create and display subform data in a PivotTable or PivotChart.

Add any number of subforms/subreports to a form/report as well as add a subform/subreport within another subform/subreport. Keep in mind that the purpose of a form or report is to make viewing and entering data easier . Avoid cluttering the form or report—which can cause confusion—by positioning the subforms or subreports carefully, assigning a different background color to each, and removing any unnecessary items, such as scroll bars. Viewing the subform/subreport properties is helpful when considering these options.

The SubForm/SubReport Wizards provide the easiest method for creating a subform or subreport. If you choose not to use the SubForm/SubReport Wizards, design a form/report and then insert it into another form/report. You may not want to use

Page 2

OFFICEPRO, Inc.

Access 2016

Lesson 1 - Using Subforms/Subreports

the wizard if you are designing a subform/subreport that is very different in appearance from the form or report the wizard produces.

C REATING A S UBFORM /S UBREPORT



Discussion

When you add a subform/subreport to an existing form, it is best to start by creating the main form/report before you create the subform/subreport. Then edit the form/report so that it contains only the information you need, as well as the desired layout. Add the form/report to the main form/report design where it will take on the properties of a subform or subreport.

Create a subform that may be viewed in Form view, Datasheet view, or both views. You can create a subreport that may be viewed in Preview view. Customize the subform in Design view by adding items such as headers, footers, color, fonts, and totals.

The SubForm/SubReport Wizard prompts you for the following information:

1. Whether to use an existing form/report as the subform/subreport or build a new one from tables and queries. 2. If you want to define the link between the main form/report and subform/subreport yourself or choose from a list of available link options. 3. A name for the subform or subreport.

OFFICEPRO, Inc.

Page 3

Lesson 1 - Using Subforms/Subreports

Access 2016

A subform within a form

NOTE Although it is not necessary to click the Control Wizards button before creating a subform or subreport, it is recommended. Clicking this button enables the control wizards that assist you in creating a subform or subreport.



Procedures

1. Open the desired database. 2. Open the desired form or report in Design view. 3. On the Design tab in the Controls group click the Use Control Wizards button , if necessary. in the Controls group. 5. Click in the form or report where you want to place the top left corner of the subform or subreport. 6. Select the desired option for the subform or subreport source. 7. Select the desired form or report you want to insert as a subform or subreport. . 9. Select the desired link option. 10. Select the desired link for the subform or subreport from the list box. 4. Click the Subform/Subreport button 8. Select

11. Select

.

Page 4

OFFICEPRO, Inc.

Access 2016

Lesson 1 - Using Subforms/Subreports

12. Type a name for the subform or subreport.

13. Select

.



Step-by-Step

From the Student Data directory, open SUBFORM.ACCDB . Create a subform/subreport.

Open the Order Entry form in Design view.

If necessary, display the ruler, and select the Design tab on the Ribbon .

Steps

Practice Data

1. Click the Use Control Wizards button in the Controls group, if necessary. The Control Wizards are enabled. 2. Click the Subform/Subreport tool in the Controls group. The mouse pointer changes into a plus sign (+) with a form attached when positioned over the form. 3. Click in the form or report where you want to place the top left corner of the subform or subreport. The default control for the subform or subreport appears and the SubForm or SubReport Wizard opens. 4. Select the desired option for the subform or subreport source. The desired option is selected. 5. Select the desired form or report you want to insert. The desired form or report is selected.

Click

, if

necessary

Click

Click in the form at the 1.5'' mark on the vertical ruler, under the Shipping Cost label box

Click form

Use an existing

Scroll as necessary and click Order Items

6. Select Next .

Click

The next page of the SubForm or SubReport Wizard appears.

OFFICEPRO, Inc.

Page 5

Lesson 1 - Using Subforms/Subreports

Access 2016

Steps

Practice Data

7. Select the desired link option.

Click

Choose from a

The desired link option is selected.

list. , if necessary

8. Select the desired link for the subform or subreport from the list box. The desired link is selected.

Click Show Order Items for each record in Orders using Order Number , if necessary

9. Select Next .

Click

The next page of the SubForm or SubReport Wizard appears with the insertion point in the What name would you like for your subform or subreport? box.

10. Type a name for the subform or subreport.

Type Order Items of Company

The name appears in the What name would you like for your subform or subreport? box.

11. Select Finish .

Click

The SubForm or SubReport Wizard closes and the subform or subreport appears in the form or report.

Size the subform control (from the bottom edge and right corner) so that it is approximately 1 1/2'' high and 3 1/2'' wide, if necessary. Switch to Form view to see the subform. Switch back to Design view and deselect the subform control, if necessary.

Close and save the form.

Practice the Concept: Create a subreport in the Sales Data 1 report at the 2.5” mark on the horizontal ruler and the 1” mark on the vertical ruler. Use the Qtr 2 Sales report for the subreport and select the first Show Customer Sales Query for each record in Customer Sales Query option for the link. Accept the default name for the subreport and view the report in print preview.

Close and save the report.

Page 6

OFFICEPRO, Inc.

Access 2016

Lesson 1 - Using Subforms/Subreports

E DITING THE L AYOUT OF A S UBFORM



Discussion

To improve the efficiency or appearance of a subform/subreport, change the layout the same way you would change the layout of a form/report. In Design view for the main form/report, the subform/subreport is a control you can move, resize, align, add, and delete as you would any other control. Since the subform/subreport control contains a form/report, open it in Design view and make any editing changes to the controls. Changes to the subform/subreport layout are performed in Design view.

NOTE Open a subform/subreport in a separate Design window by right-clicking the desired subform/subreport then selecting Subform in New Window or Subreport in New Window from the shortcut menu.



Procedures

1. Open the main form in Design view. 2. Double-click the subform control you want to edit. 3. Double-click the subform control text to select it. 4. Make the desired editing changes.



Step-by-Step

Edit the subform’s layout .

Open the Order Entry form in Design view.

If you have not been using the Order Entry form, open the Order Entry 1 form in Design view.

OFFICEPRO, Inc.

Page 7

Lesson 1 - Using Subforms/Subreports

Access 2016

Steps

Practice Data

1. Double-click the subform control you want to edit. The subform control is selected and the property sheet for the control opens. 2. Double-click the subform control text to select it. The text is selected. 3. Make the desired editing changes. The editing changes are made. 4. Continue making editing changes as desired. The editing changes are made.

Scroll as necessary and double-click the subform Qty unbound control (the Qty label box in the subform) Double-click the text Qty in the label box

Type Quantity

Press [Enter]

Close the property sheet, then save and close the form.

D ISPLAYING A S UBFORM IN D ATASHEET V IEW



Discussion

Display a subform in Datasheet view. This option allows you to view many subform records at once, rather than viewing them one at a time in Form view. Since the benefit of using subforms is to display the many side of a one-to-many relationship, Datasheet view is generally preferred.

Page 8

OFFICEPRO, Inc.

Access 2016

Lesson 1 - Using Subforms/Subreports

A subform in Datasheet view



Procedures

1. Open the main form in Form view. 2. Right-click the subform.. 3. On the context menu, click Subform then Datasheet . . 4. Click the plus sign at the beginning of a record to view the subform data for that record.



Step-by-Step

Display a subform in Datasheet view.

If you have not been using the Order Entry form, open the Order Entry 2 form in Form view.

Steps

Practice Data

1. Right-click the subform. A context menu appears.

Right-click the subform

2. On the context menu, click Subform A sub-menu appears.

Click Subform

OFFICEPRO, Inc.

Page 9

Lesson 1 - Using Subforms/Subreports

Access 2016

Steps

Practice Data

3. On the sub-menu, select Datasheet . The form appears in Datasheet view. 4. Select the plus sign next to the desired records. The subform data for the desired record appears.

Click Datasheet view

Click for record 3

Close the form.

D ISPLAYING A S UBFORM /S UBREPORT T OTAL



Discussion

Forms and reports often include totals for the information they contain. Totals are easily created by adding a calculated control to the form or report that contains an expression to find the desired total. Expressions are used to obtain information you cannot obtain directly from the tables in your database. For example, if you have a table that contains the quantity and cost of various items, you could use an expression to multiply the quantity and the cost to obtain the current inventory value. Every time you use a form/report, Access calculates any expressions contained in the form/report, helping you keep your information as accurate as possible.

Some examples of expressions are listed in the following table:

Control Name

Expression

Result

Adds the value of the Cost field, the value of the Shipping field, and the value of the Markup field of an item to produce the Total field. Multiplies the Price field of an item by a constant discount of 0.15. Provides the current date.

Total

=[Cost]+[Shipping]+[Markup]

Discount

=[Price]*.15

Date

=Date()

Page 10

OFFICEPRO, Inc.

Access 2016

Lesson 1 - Using Subforms/Subreports

You may want to include a total contained in a subform/subreport in the main form/report total. For example, an Orders form may contain a control for shipping charges. The information in the Product subform may include a total, such as the total cost of all the products ordered. You can display this subform total on the main form so that you can add the shipping charges to this total to create a grand total. This option is performed by adding a control to the main form/report, which refers to the desired subform/subreport total control, to display the information.

The expression in the main form/report control that refers to the subform/subreport control defines the name of the subform/subreport and the name of the subform/subreport control being used in the following format:

= [ Name of the Subform/Subreport ]![ Name of the Subform/Subreport Control ]

NOTE Display a subreport total in the main report using the same methods you use to display a subform total.



Procedures

1. Open the form or report in Design view.

2. On the Design tab in the Controls group select the Text Box button . 3. Click in the form or report where you want to insert the control. 4. Select the label field. 5. Select the text in the label field. 6. Type a label in the label field. 7. Select the text box. 8. Press [F4] to open the property sheet. 9. Select the Data tab. 10. Type the expression that refers to the desired subform/subreport control. 11. Press [F4] to close the property sheet.



Step-by-Step

Display a subform/subreport total in the main form/report.

OFFICEPRO, Inc.

Page 11

Lesson 1 - Using Subforms/Subreports

Access 2016

Open the Product List form in Design view.

Steps

Practice Data

1. On the Design tab in the Controls group select the Text Box button. The mouse pointer changes into a text box with a plus sign (+) when positioned over the form. 2. Click in the form or report where you want to insert the control. The control appears on the form or report.

Click

Scroll as necessary and click in the form below the subform, in line with the 3.5” mark on the horizontal ruler

3. Select the label field.

Click in the label box

Sizing handles appear around the label field.

4. Drag to select the text in the label field. The text is selected.

Drag to select Textxx: ( xx is a number)

5. Type a label in the label field.

Type Items Total

The label appears in the label field.

6. Select the text box.

Click in the text box

Sizing handles appear around the text box.

7. Press [F4] to open the property sheet. The property sheet for the selected text box opens.

Press [F4] , if necessary

8. Select the Data tab.

Click the Data tab, if necessary

The Data page appears with the insertion point in the Control Source property. 9. Type the expression that refers to the desired subform/subreport control. The expression appears in the Control Source property. 10. Press [F4] to close the property sheet. The property sheet closes, and the expression replaces the text in the text box.

Type = [Order Items of Company]![Total]

Press [F4]

Page 12

OFFICEPRO, Inc.

Access 2016

Lesson 1 - Using Subforms/Subreports

View the form in Form view. Scroll to view the Items Total field, if necessary. Then, close and save the form. Close SUBFORM.ACCDB .

OFFICEPRO, Inc.

Page 13

Lesson 1 - Using Subforms/Subreports

Access 2016

E XERCISE

U SING S UBFORMS /S UBREPORTS



Task

Use a subform/subreport.

1. Open SUBFORMX.ACCDB . 2. Open the Invoice Form form in Design view. 3. Use the SubForm Wizard to create a subform in the bottom center of the form. 4. Insert the existing Project Form form as a subform. 5. Select the appropriate option from the list box to link the main form and subform by the Project ID field. Name the subform Project Info . 6. Delete the subform’s label box. 7. Size and position the subform control so that all the fields appear, if necessary. 8. Switch to Form view to see the subform. 9. Select the Project ID field in the subform and switch to Datasheet view. 10. Close and save the form. 11. Open the Payment Form form in Design view. 12. Create a total for the subform called Early bird discount in the Payment Form form at the 5” mark on the horizontal ruler below the subform. Use 15% as the discount. 13. View the form in Form view to see the subform total. 14. Close the form without saving the changes. 15. Close the database file.

Page 14

OFFICEPRO, Inc.

Lesson 2 - A NALYZING T ABLES

This lesson covers the following objectives:

Analyzing a Table Using Relationships in Splits Using the Table Analyzer Wizard Using the Performance Analyzer Exercise

Lesson 2 – Analyzing Tables

Access 2016

A NALYZING A T ABLE



Discussion

When you create or analyze a table, you should look at its structure to determine the efficiency of its design. If the same information appears in multiple records, the table can probably be split into two related tables. Splitting a table is more efficient because the tables are then smaller and, as a result, easier to manage. In addition, information will only need to be entered one time. Smaller tables also reduce the chance of making data entry errors.

If you feel that a table may need to be split, you can use the Table Analyzer Wizard to analyze it for you and suggest which fields should be in each table. The Table Analyzer Wizard can also create the correct table relationships.

U SING R ELATIONSHIPS IN S PLITS



Discussion

When the Table Analyzer Wizard splits a table, it creates the necessary relationships in the resulting tables. These relationships allow data from all the tables to be used in queries, forms, and reports.

Access provides three types of relationships: one-to-one , one-to-many and many-to- many. When a table is split, a one-to-many relationship is usually created. This relationship allows one record in the first table to be related to many records in the other related tables, eliminating data duplication.

U SING THE T ABLE A NALYZER W IZARD



Discussion

The Table Analyzer Wizard looks at a table to determine if it contains duplicated information. If the Table Analyzer Wizard determines that a table has a lot of duplicated data, it splits the table, creating new tables, leaving the original table intact.

Page 16

OFFICEPRO, Inc.

Access 2016

Lesson 2 – Exploring Access

The Table Analyzer Wizard has seven pages. The first two pages are informational, with the first page describing the problem of duplicate data in the table and the second describing the solution. You can choose not to display these pages.

You then select the table you want to analyze. On subsequent pages, you indicate whether you want the wizard to decide how to split the table or whether you want to do it yourself. If you allow the wizard to split the table, the recommended tables and the relationships between them appear. You can then move fields between tables or even drag fields to create new tables. Each piece of information should be stored in only one location, and each table should contain only data that refers to a single subject.

After the fields have been put into the correct tables, you can set the primary keys. To be related properly, each table must have a primary key, and the primary key must be a field that uniquely identifies each record. Therefore, a primary key field cannot contain duplicate data. Access will generate unique fields, if necessary.

On the last page, you can allow the Table Analyzer Wizard to create a query. The query will be given the same name as the old table and the old table will be renamed. As a result, any reports, forms, or queries that refer to the old table can still be used.

The Table Analyzer Wizard

OFFICEPRO, Inc.

Page 17

Lesson 2 – Analyzing Tables

Access 2016



Procedures

1. On the Database Tools tab, click the Analyze Table button

.

2. Select

.

3. Select . 4. Select the table you want to analyze.

5. Select . 6. Select whether you want to allow the Table Analyzer Wizard to decide which fields are put in each table.

7. Select . 8. Double-click the title bar of the first table. 9. Type the desired table name. . 11. Double-click the title bar of the second table. 12. Type the desired table name. 10. Select

13. Select

.

14. Select . 15. Select the field you want to set as a primary key.

16. Click the Set Unique Identifier button

at the top of the Table Analyzer

Wizard window.

17. Select . 18. Select whether you want the Table Analyzer Wizard to create a query for you.

19. Select . 20. Close the Microsoft Access Help window, if necessary.



Step-by-Step

From the Student Data directory, open ANALYZE.ACCDB . Use the Table Analyzer Wizard.

Page 18

OFFICEPRO, Inc.

Access 2016

Lesson 2 – Exploring Access

If necessary, display All Access Objects in the Navigation Pane.

Steps

Practice Data

1. On the Database Tools tab, click the Analyze Table button. The Table Analyzer Wizard opens.

Click

2. Select Next > .

Click

The next page of the Table Analyzer Wizard appears.

3. Select Next > .

Click

The next page of the Table Analyzer Wizard appears.

4. Select the table you want to analyze. The table name is selected.

Click Order Items

5. Select Next > .

Click

The next page of the Table Analyzer Wizard appears.

6. Select whether or not you want to allow the Table Analyzer Wizard to decide which fields are put in each table. The option is selected.

Click

Yes, let the

wizard decide. , if necessary

7. Select Next > .

Click

The next page of the Table Analyzer Wizard appears.

8. Double-click the title bar of the first table. A Table Analyzer Wizard dialog box opens with the text in the Table Name box selected.

Double-click the Table1 title bar

9. Type the desired table name.

Type Line Items

The name appears in the Table Name box.

10. Select OK .

Click

The Table Analyzer Wizard dialog box closes, and the new name appears in the table title bar.

OFFICEPRO, Inc.

Page 19

Lesson 2 – Analyzing Tables

Access 2016

Steps

Practice Data

11. Double-click the title bar of the second table. A Table Analyzer Wizard dialog box opens with the text in the Table Name box selected.

Double-click the Table2 title bar

12. Type the desired table name.

Type Items

The name appears in the Table Name box.

13. Select OK .

Click

The Table Analyzer Wizard dialog box closes, and the new name appears in the table title bar.

14. Select Next > .

Click

The next page of the Table Analyzer Wizard appears.

15. Select the field you want to set as a primary key. The field is selected. 16. Click the Set Unique Identifier button at the top of the Table Analyzer Wizard window. A key symbol appears to the left of the selected field.

Click Product ID in the Items table, if necessary

Click

17. Select Next > .

Click

The next page of the Table Analyzer Wizard appears.

18. Select whether or not you want the Table Analyzer Wizard to create a query for you. The option is selected.

Click

Yes, create the

query.

19. Select Finish .

Click

The Table Analyzer Wizard closes, the new tables and query are created, the old table is renamed, and a Microsoft Office Access Help window opens. 20. Close the Microsoft Office Access Help window, if necessary. The Microsoft Office Access Help window closes.

Click on the Microsoft Office Access Help window title bar

Page 20

OFFICEPRO, Inc.

Access 2016

Lesson 2 – Exploring Access

Close all open objects.

U SING THE P ERFORMANCE A NALYZER



Discussion

The Performance Analyzer analyzes the efficiency of your database to optimize its performance.

The Performance Analyzer window contains a page for each available object type in Access, as well as a Current Database page and an All Object Types page. The Current Database page allows you to analyze features that are not objects, such as relationships. You can analyze a single object or multiple objects on different pages. If you are analyzing different types of objects, the All Object Types page allows you to view all database objects in one location. The results of the performance analysis appear in the Analysis Results box. The results are categorized as a Recommendation , a Suggestion , or an Idea . The Analysis Notes box provides additional information about the selected result. Use the Optimize button to have Access perform the action suggested in a Recommendation or Suggestion ; you must manually perform the action for an Idea .

Performance Analyzer

OFFICEPRO, Inc.

Page 21

Lesson 2 – Analyzing Tables

Access 2016

NOTE After selecting the Optimize button, the Performance Analyzer marks the action as Fixed . Use the Select All button to select all the results in the Analysis Results box.



Procedures

1. On the Database Tools tab, click the Analyze Performance button . 2. Select the tab containing the objects you want to analyze. 3. Select the objects you want to optimize. . 5. To view an explanation, click any result in the Analysis Results box. 4. Select

6. Select

.



Step-by-Step

Use the Performance Analyzer to analyze the efficiency of a database.

Steps

Practice Data

1. On the Database Tools tab, click the Analyze Performance button. The Performance Analyzer opens. 2. Select the tab containing the objects you want to analyze. The corresponding object types page appears. 3. Select the object you want to optimize. The object is selected. 4. Select additional objects as desired. The objects are selected.

Click

Click the All Object Types tab

Click

Customers

Click

Orders

Page 22

OFFICEPRO, Inc.

Access 2016

Lesson 2 – Exploring Access

Steps

Practice Data

5. Select OK .

Click OK

The Performance Analyzer displays its recommendations in the Analysis Results box.

6. To view an explanation, click any result in the Analysis Results box. An explanation of the selected recommendation appears in the Analysis Notes box.

Click Table ‘Orders’:

7. Select Close .

Click

The Performance Analyzer closes.

Display Tables in the Navigation Pane, if necessary, and open the Customers table in Design view. Change the data type for the Customer Number field to Long Integer and close and save the Customers table. Open the Orders table in Design view. Change the data type for the Customer ID field to Long Integer . Then, close and save the Orders table. Practice the Concept: Open the Performance Analyzer, select all objects on the Tables page, and run the Performance Analyzer. Notice that the Performance Analyzer now has no suggestions for the selected objects. Select OK to close the Performance Analyzer message box. Close ANALYZE.ACCDB .

OFFICEPRO, Inc.

Page 23

Lesson 2 – Analyzing Tables

Access 2016

E XERCISE

A NALYZING T ABLES



Task

Analyze tables.

1. Open Analyzex.accdb . 2. Open the Table Analyzer Wizard. Select Next as needed and then select the Project List table. Select Next . 3. Allow the wizard to decide how to split the table. 4. Rename Table1 to Project and Table2 to Client . 5. Drag the State field from the Project field list below the City field in the Client field list. 6. Set the Client ID field as the primary key in the Client table. 7. Have the Table Analyzer Wizard create a query. 8. Select Finish . 9. Close the Microsoft Office Access Help window. View the tables and then close them and restore the Database window. 10. Analyze the performance of the Client and Payment tables. (Notice that the analysis result does not fit the data; changing the Zip field to a long integer field would not allow for zip codes with leading zeroes, such as 08054.) 11. Close the Performance Analyzer. 12. Close the database file.

Page 24

OFFICEPRO, Inc.

Lesson 3 - C USTOMIZING THE N AVIGATION P ANE

This lesson covers the following objectives:

Creating Custom Categories Creating Custom Groups Adding Database Objects to a Group Showing/Hiding the Unassigned Objects Group Deleting Objects from a Group Exercise

Lesson 3 – Customizing the Navigation Pane

Access 2016

C REATING C USTOM C ATEGORIES



Discussion

In previous versions of Access, you could use a switchboard to provide users with easy access to selected objects in your database. With the introduction of Access 2010, it is easier to create a custom category using the Navigation Pane .

You can create up to a maximum of ten custom categories, each of which can contain multiple custom groups.

The Navigation Pane Options dialog box



Procedures

1. Open the desired database. 2. Show the Navigation Pane , if necessary.

3. Select the arrow in the Navigation Pane header. 4. Select All Access Objects . 5. Right-click the All Access Objects category. 6. Select Navigation Options . . 8. Type a name for the new category, then press [Enter] . 7. Select Add Item

9. Select OK

.

Page 26

OFFICEPRO, Inc.

Access 2016

Lesson 3 - Customizing the Navigation Pane



Step-by-Step

From the Student Data directory, open NAVPANE.ACCDB . Create a custom category.

Steps

Practice Data

1. Show the Navigation Pane , if necessary.

Press [F11] to open the Navigation Pane .

The Navigation Pane menu appears.

2. Select the arrow in the Navigation Pane Header. The Navigation Pane menu appears.

Click

3. Select All Access Objects .

Click All Access Objects

The object type is selected, and the objects stored in it appear in the object list. 4. Right-click the All Access Objects header. The context menu appears.

Right-click the All Access Objects header

5. Select Navigation Options .

Click Navigation Options

The Navigation Options dialog box appears.

6. Select Add Item under the Categories list. A new category appears in the Categories list. 7. Type a name for the new category, then press [Enter] . The new category appears in the Categories list.

Click

Type Product Info , then press [Enter]

8. Select OK .

Click

The Navigation Options dialog box closes.

Notice when you click on the Navigation Pane header, the new custom category appears in the drop-down list.

OFFICEPRO, Inc.

Page 27

Lesson 3 – Customizing the Navigation Pane

Access 2016

C REATING C USTOM G ROUPS



Discussion

After creating the custom category, you can then create one or more custom groups for that category. There is no limit to the number of custom groups you can create.



Procedures

1. Show the Navigation Pane , if necessary. 2. Right-click the Navigation Pane header. 3. Select Navigation Options . 4. Select the desired category in the Categories list.

5. Under the Groups list select the Add Group button

.

6. Type a name for the new group, then press [Enter] .

7. Select OK

.



Step-by-Step

Create a custom group.

Steps

Practice Data

1. Show the Navigation Pane , if necessary. The Navigation Pane appears. 2. Right-click the Navigation Pane header.

Press [F11] .

Right-click the Navigation Pane header

The Navigation Pane context menu appears.

3. Select Navigation Options .

Click Navigation Options

The Navigation Options dialog box appears.

4. Select the desired category in the Categories list. The desired category is selected.

Click Product Info

Page 28

OFFICEPRO, Inc.

Access 2016

Lesson 3 - Customizing the Navigation Pane

Steps

Practice Data

5. Select the Add Group button for the desired category. A new group appears in the Groups for list. 6. Type a name for the new group, then press [Enter] . The new group appears in the Groups for list.

Click

Type Product Group 1 , then press [Enter]

7. Select OK .

Click

The Navigation Options dialog box closes.

Notice when you click the Navigation Pane header and select the Product Info category, your new custom group appears in the drop-down list. You can repeat the process to add as many custom groups as you need.

A DDING D ATABASE O BJECTS TO A G ROUP



Discussion

After creating your custom group, you can select which objects you want to assign to it. Select objects individually, or select multiple objects by holding down [Ctrl] as you select.



Procedures

1. Select the arrow on the Navigation Pane header. 2. Select the desired category. 3. Select the object you want to assign from the Unassigned Objects group. 4. Drag the object to the custom group.



Step-by-Step

Add database objects to a group.

OFFICEPRO, Inc.

Page 29

Lesson 3 – Customizing the Navigation Pane

Access 2016

Steps

Practice Data

1. Select the arrow on the Navigation Pane header. The Navigation Pane menu appears.

Click

2. Select the desired category.

Click Product Info

The groups created for your category appear in the lower section of the Navigation Pane menu, together with the Unassigned Objects group. 3. From the Unassigned Objects group select the object you want to assign. A drop-down menu appears. 4. Drag the object to the custom group. The object appears in the custom group.

Click the Items table

Drag the Items table to group Product Group 1

Practice the concept : Add the LineItem table to Product Group 1 .

Notice your actions create shortcuts to the chosen objects. You can rename the shortcuts by right-clicking and selecting Rename Shortcut .

S HOWING /H IDING THE U NASSIGNED O BJECTS G ROUP



Discussion

When you have finished creating your custom groups and assigned your chosen objects, you can leave the Unassigned Objects group visible in the Navigation Pane, or you can choose to hide it.



Procedures

1. Show the Navigation Pane , if necessary. 2. Right-click the Navigation Pane header. 3. Select Navigation Options . 4. Check or uncheck the Unassigned Objects check box in the Groups for list, as necessary.

5. Click

.

Page 30

OFFICEPRO, Inc.