MODULE 3

To my students:
You are now about to start the 9th lesson for Week 11. Please answer the following
activities given below (KNOWLEDGE ASSESSMENT (LESSON 11-15)). (Refer your
answer on the learning materials found in google classroom). For Hands-on
activities and Projects from 11-15, please try it at home using MS Access and
submit it to me. Save your work in a flash drive or send it in our google classroom.
(Note: assessment = 20%, hands-on activity = 40% and Project = 40%) Good luck!

Knowledge Assessment (LESSON 11)

Matching
Match the term in Column 1 to its description in Column 2.
Column 1 Column 2
1. Group header a. A field that contains an aggregate function to calculate data
2. Group footer b. A field by which data is grouped
3. Grouping field c. The nested arrangement of groups in a report
4. Aggregate field d. The section of a report where the name of a grouped field is displayed and
printed
5. Grouping levels e. The section of a report where the data in a group is summarized
True/False
Circle T if the statement is true or F if the statement is false.
___1. Group headers take on the name of the group.
___2. The arrows at the end of a Group On row determine sort order.
___3. Average is an aggregate function.
___4. You must preview a report before you can print.
___5. Labels can be printed using reports.
HANDS-ON ACTIVITY 11

Project 11-1: Creating Address Labels, and Totaling and Printing Reports
In this project, you need to send out confidential contract information to the authors in the
Business Books division. Create labels for the authors using the Author Contact Information
table. You also need to finish the Book Sales report to show totals for Domestic and
International Sales and make adjustments in Print Preview before printing.

GET READY. LAUNCH Access if it is not already running.

1. OPEN the Lucerne database and then SAVE it as Lucerne-final.
2. Select the Author Contact Information table in the Navigation
Pane.
3. On the Create tab, in the Reports group, click the Labels button.
4. Select the C2242 label in the Product number box and then click
Next.
5. Select Arial from the Font name menu and then select 9 from the
Font size menu.
6. Click the Italic button and then click Next.
7. Type CONFIDENTIAL in all caps and then press Enter.
8. Type For Addressee Only and then press Enter.
9. Select the Author First Name field and then click the > button.
Press the Spacebar.
10. Select the Author Last Name field and then click the > button. Press
Enter.
11. Select the Author Address field and then click the > button. Press
Enter.
12. Select the Author City field and then click the > button. Type , (a
comma) and then press the Spacebar.
13. Select the Author State field and then click the > button. Press the
Spacebar.
14. Select the Author Zip field and then click the > button.
15. Click Finish.
16. CLOSE the report.
17. Double-click the Book Sales report in the Navigation Pane to open
it.
18. In Layout view, select the Domestic Sales field header.
19. On the Design tab, in the Grouping & Totals group, click the Totals
button and then select Sum from the menu.
20. Select the International Sales field header.
21. On the Design tab, in the Grouping & Totals group, click the Totals
button and then select Sum from the menu.
22. Select the Book Title field header.
23. On the Design tab, in the Grouping & Totals group, click the Totals
button and then select Count Records from the menu.
24. On the Design tab, in the Views group, click the View menu and
then select Print Preview from the menu.
25. On the Print Preview tab, change between Portrait and Landscape
layout. Portrait looks better so click Portrait.
26. In the Page Size group, click the Margins button and then select
Wide from the menu.
27. On the Print Preview tab, in the Zoom group, click the Zoom button
arrow and then select Fit to Window.
 28. SAVE the report.
29. On the Print Preview tab, in the Print group, click the Print button or
click Cancel to close the dialog box.
30. CLOSE the report and then CLOSE the database.
PAUSE. LEAVE Access open for the next project.
Project 11-2: Creating and Printing a Grouped Report
In this project, your supervisor at Fourth Coffee asks you to create and print a report using the
Monthly Sales by Store table. View the report in Print Preview to make sure the report is
centered on the page before printing.

GET READY. LAUNCH Access if it is not already running.

1. OPEN Fourth Coffee from the data files for this lesson and then SAVE the database as
Fourth Coffee-final.
2. Select the Monthly Sales by Store table.
3. Use the Report Wizard to create a report that includes the Month, Store, and Sales fields.
4. Group by Store and create a Stepped layout.
5. Change to Design view to decrease the width of the Month and Sales columns and move the
Sales column closer to the month.
6. Click the Group & Sort button to open the Group, Sort, and Total pane.
7. Click the Add a sort button and then select Sales from the menu. Sort from smallest to
largest.
8. Change to Layout view.
9. Select the Sales column.
10. Click the Totals button and then select Sum from the menu.
11. SAVE the report.
12. Change to Print Preview.
13. Click the Zoom button arrow and then select Fit to Window.
14. Click the Margins button and then select Wide from the menu.
15. Click the Landscape button.
16. Click the Page Setup button.
17. Click the Print Options tab. In the Margins section, type 1 in the Top box, 1 in the Bottom
box, 1.5 in the Left box, and 1.5 in the Right box.
18. Click OK.
19. Click the Print button and then click OK to print the report or click Cancel to close the
dialog box.
20. SAVE and CLOSE the report.
STOP. CLOSE the database and then EXIT Access.
Knowledge Assessment (LESSON 12)
Fill in the Blank
Complete the following sentences by writing the correct word or words in the blanks
provided.
1. A(n) ____________ removes rows matching the criteria you specify from one or more
tables.
2. To minimize the risk of running an action query, you can first preview the data that will be
acted upon by viewing the action query in ____________ view before running it.
3. You can use the Group by function in the ____________ row on the design grid in query
Design view.
4. A(n) ____________ query always includes three types of data: the data used as row
headings, the data used as column headings, and the values that you want to sum or
otherwise compute.
5. To be able to apply a filter when and where you want, save the filter as a(n) ___________.

Multiple Choice
Select the best response for the following statements or questions.
1. Which type of query displays its results in a grid similar to an Excel worksheet?
a. Crosstab
b. Append
c. Aggregated
d. Subquery
2. Which of the following is not a type of join?
a. Inner join
b. Exterior join
c. Cross join
d. Unequal join
3. Which of the following is not an aggregated function?
a. Lowest
b. Sum
c. Average
d. Count
4. Which of the following is pressed to open the Zoom box, which provides you with more space in which
to enter the SELECT statement in a field or criteria cell?
a. Shift+F2
b. Ctrl+2
c. Shift+Enter
 d. Ctrl+Spacebar
[Link] display the pane used to format a calculated value in query Design view, click the
____________ button in the Show/Hide group on the Design tab of the Ribbon.
a. Undo
b. Property Sheet
c. Datasheet View
d. Totals
HANDS-ON ACTIVITY 12

Project 12-1: Creating a Calculated Query Field

In your job as a travel agent at Margie’s Travel, you are frequently asked the length of various
trips. So that you don’t have to calculate it mentally, you will create a calculated field that will
give you this information.

GET READY. LAUNCH Access if it is not already running.

1. OPEN the M Travel database from the data files for this lesson and then SAVE the
database as M Travel-final.
2. On the Create tab, in the Queries group, click Query Design.
3. In the Show Table dialog box, double-click Events to add the table to the design grid.
4. Click Close.
5. In the Inventory field list, double-click Event, StartDate, and EndDate to add the fields
to the design grid.
6. Click the Field cell in the first blank column after EndDate and then press Shift+F2 to
open the Zoom dialog box.
7. In the Zoom dialog box, type the following expression: TripLength: [EndDate] -
[StartDate]
8. Click OK.
9. On the Design tab, in the Results group, click Run. The query is displayed, with a new
TripLength field calculating the number of days for each trip.
10. SAVE the query as Calculated Query and then CLOSE the query.
11. CLOSE the database.

PAUSE. LEAVE Access running for the next project.

Project 12-2: Saving a Filter as a Query and Creating a Subquery

As sales manager for Fourth Coffee, you frequently run the same filters on the database. Now
that you have learned to save a filter as a query, you can save yourself some time. You are also
interested in extracting specific information about monthly sales for all Fourth Coffee stores
from the database. Create a subquery to determine which months and corresponding stores have
sales that are above average.
 GET READY. LAUNCH Access if it is not already running.
1. OPEN Fourth Coffee from the data files for this lesson and then SAVE the database as
Fourth Coffee-final.
2. On the Create tab, in the Queries group, click the Query Wizard button.
3. In the New Query dialog box, click Simple Query Wizard and then click OK.
4. In the Tables/Queries drop-down list, click Table: Order Summary.
5. Click the >> button to move all the fields from the Available Fields to the Selected Fields
box and then click Next.
6. Click Next again and then click Finish to display a simple select query.
7. On the Home tab, in the Sort & Filter group, click the Advanced button and then click Filter
by Form.
8. In the Filter by Form, click the down arrow in the Status field and then click Completed.
9. On the Home tab, in the Sort & Filter group, click the Toggle Filter button to apply the
filter. The results are displayed.
10. On the Home tab, in the Sort & Filter group, click the Advanced button and then click
Advanced Filter/Sort to display the new query design grid.
11. On the Home tab, in the Sort & Filter group, click the Advanced button and then click Save
As Query. The Save As Query dialog box appears.
12. Type Filter Query in the Query Name box and then click OK.
13. CLOSE the Order Summary QueryFilter1 query.
14. On the Home tab, in the Sort & Filter group, click the Toggle Filter button to remove the
filter.
15. CLOSE the Order Summary Query and SAVE the changes when prompted. If presented
with a message box stating the query has been changed, click Yes.
16. On the Create tab, in the Queries group, click Query Design.
17. Use the Show Table dialog box to add the Monthly Sales by Store table to the upper section
of the query design grid and then close the Show Table dialog box.
18. Add the Month, Store, and Sales fields to the design grid.
19. Place the insertion point in the Criteria row of the Sales field and display the Expression
Builder.
20. Type the following expression in the Expression Builder, using the available categories and
menus:
> (SELECT AVG([Sales]) FROM [Monthly Sales by Store])
21. Click OK.
22. Use the Property Sheet pane to change the calculated field format to Euro.
23. On the Design tab, in the Results group, click Run to display the query results.
24. SAVE the query as Subquery and then CLOSE the query.

STOP. CLOSE the database and then EXIT Access.

Knowledge Assessment (LESSON 13)

Matching
Match the term in Column 1 to its description in Column 2.
Column 1 Column 2
1. Legend a. Contains six tabs with options for changing the look and layout of a
chart
2. Chart Options dialog box b. A component of Access 2016 used to make changes to a chart
created by the Chart Wizard
3. Refresh All button c. Displays a list of the colors, shapes, or patterns used as categories in
a chart
4. Microsoft Graph d. Allows you to save objects as other file formats like PDF and XPS
and as other database objects
5. Save Object As command e. Updates the data in a chart
True/False
Write T if the statement is true or F if the statement is false.
______1. The Chart Wizard is a control.
______ 2. A legend can be displayed only on the right side of a chart.
______ 3. The Refresh All button updates data in a chart with data modified in the underlying
data source.
______ 4. You can save a table as a report.
______ 5. An Axis is a type of chart.
HANDS-ON ACTIVITY 13

Project 13-1: Creating a Chart and Changing the Chart Type

The City Power & Light human resources department is reviewing the salary budgets for the
office. In this project, your supervisor asks you to create a pie chart within a report to show the
distribution of funds for each employee. Then, you decide to create a variation of the pie chart
that will more clearly show the salary amounts in relationship to each other.

GET READY. LAUNCH Access if it is not already running.

1. OPEN the City Power & Light database and then SAVE the database as City Power &
Light-final.
2. Double-click the Salary report in the Navigation Pane to open it and then change to Design
view.
3. Click the Chart button and draw a large rectangle in the space provided in the Page Footer
section. The Chart Wizard dialog box appears.
4. Click the Next button.
5. Move the Employee ID and Salary fields to the Fields for Chart list and then click Next.
 6. Click the Pie chart button and then click Next.
7. Drag and drop the Salary field button to the data box that says SumofEmployee ID box.
SumofEmployee ID is replaced with SumOfSalary
8. Drag and drop the Employee ID field button to the Series box. Series is replaced with
Employee ID.
9. Click the Preview Chart button.
10. Click Close.
11. Click Finish. Resize the chart if necessary if the text is too large.
12. Click the Data tab of the Property Sheet and set the Enabled property to Yes.
13. Change to Report view.
14. Click the Refresh All button.
15. SAVE the Report.
16. Double-click the Salary report in the Navigation Pane to open it in Report view if it’s not
already open.
17. Click the File tab and then click Save As.
18. Click the Save Object As button.
19. Click the Save As button.
20. Type Salary Line Chart in the “Save ’Salary’ to:” box and then click OK.
21. Change to Design view.
22. Double-click the chart to launch Microsoft Graph.
23. Click the Chart menu and then click Chart Type from the menu.
24. Click Line in the Standard types list.
25. In the Chart subtype list, if not already selected, click the Line with markers displayed at
each data value button.
26. Click OK.
27. Click the Chart menu and then click Chart Options from the menu.
28. Click the Legend tab and then click the Bottom option button.
29. Click OK.
30. SAVE the chart and then view the chart in Report view.
31. CLOSE the database.
PAUSE. LEAVE Access open for the next project.
Project 13-2: Creating and Formatting an Expenses Chart
You created a chart representing the Income & Expenses Summary table for Blue Yonder
Airlines earlier. Now you need to create a chart solely for the expenses.

GET READY. LAUNCH Access if it is not already running.

1. OPEN Blue Yonder Airlines-final that you saved in an earlier exercise.
2. Double-click the Expenses Detail table in the Navigation Pane to open it.
3. Click the File tab and then click Save As.
4. Click the Save Object As button.
5. Click the Save As button.
6. Type Expenses Detail in the “Save ’Expenses Detail’ to:” box, select Report from the As
menu, and then click OK.
7. Double-click the Expenses Detail report in the Navigation Pane to open it and then change
to Design view.
8. Expand the Page Footer section by moving the Report Footer section down.
9. Create a chart in the Page Footer section using the Expenses Detail table as the data source.
10. Move the Amount and Yr fields to the Fields for Chart list and then click Next.
11. Click the Doughnut chart type button and then click Next.
12. Drag and drop the Yr field button to the Axis box (where it says Series) and then click Next.
13. Select <No Field> in the Report Fields and Chart Fields drop-down boxes and then click
Next.
14. Click Finish.
15. Click the Property Sheet button.
16. Click the Data tab of the Property Sheet and then change the Enabled cell to Yes.
17. View the chart in Report view.
18. Change to Design view and double-click the chart to launch Microsoft Graph.
19. Click the Chart menu and then select Chart Options from the menu.
20. Click the Data Labels tab and then select the Percentage check box.
21. Click OK.
22. SAVE the chart.
23. View the chart changes in Report view.
24. CLOSE the report.
STOP. CLOSE the database and then EXIT Access.

Knowledge Assessment (LESSON 14)

Fill in the Blank

Complete the following sentences by writing the correct word or words in the blanks
provided.
1. When you link to an Excel worksheet, Access creates a new table, called a(n) ___________,
that is linked to the source cells.
2. In a(n) ___________ file, each record appears on a separate line and the fields are separated
by a single character.
3. In a(n) ___________ file, each record appears on a separate line and the width of each field
remains consistent across records.
 4. When you export the content of a table or query to a text file with ___________ and
___________, hyphens (-) and pipe characters (|) are used to organize the content in a grid.
5. When exporting to Excel, the data is always added in a new ___________.

Multiple Choice
Select the best response for the following statements or questions.
1. If you want to add, edit, or delete data in a linked table, you must make the changes in which of the
following?
a. First row of data
b. Access object
c. Field headers
d. Source file
2. You can save an import or export operation involving any of the file formats supported in Access, but
you cannot save the details of which of the following?
a. Linking operation
b. Text file import operation
c. Query export operation
d. Fixed-width file
3. When you export an object to Word, Access creates which type of file?
a. MS-DOS text
b. Rich Text Format
c. HTML
d. Linked
4. Which of the following does Access do if you choose to store imported data in a new table?
a. It links the new table to an existing table.
b. It overwrites the data in the existing table.
c. It creates a table and adds the imported data to this table.
d. It gives you an error message.
5. Which of the following is an advantage of linking an Access database to data in another program?
a. Maintaining a copy of the external data in Access
b. Being able to use Access querying and reporting tools
c. Being able to edit the linked table in Access
d. Easily being able to change the structure of the Access table
HANDS-ON ACTIVITY 14

Project 14-1: Importing and Exporting Data

In this project, you are the purchasing manager for Fourth Coffee and an associate has provided
some information about exotic coffees that are being considered for the monthly coffee club. The
data is in an Excel worksheet and will need to be imported into the database. Your supervisor
also wants a list of customer information in a Word file. So you use the Customers table in the
Access database to export the data to a Rich Text Format file.

GET READY. LAUNCH Access if it is not already running.

1. OPEN the Fourth Coffee database from the data files for this lesson
and then SAVE the database as Fourth Coffee-final.
2. On the External Data tab, in the Import & Link group, click Excel to
display the Get External Data – Excel Spreadsheet dialog box.
3. Click Browse to open the File Open dialog box.
4. Use the File Open dialog box to locate the Coffee_Exotic
spreadsheet file and then click Open.
5. Click Import the source data into a new table in the current
database and then click OK. The Import Spreadsheet Wizard
appears.
6. Click Next to display the next screen.
7. Select the First Row Contains Column Headings check box if it is
not already selected.
8. Click Next to display the next screen where the wizard prompts you
to review the field properties.
9. Click the column headings to display the corresponding field
properties.
10. Click Next to display the next screen.
11. Click Next to let Access add the primary key. The final screen
appears.
12. In the Import to Table box, type Coffee_Exotic and then click
Finish. When the Save Import Steps screen appears, click Close.
13. In the Navigation Pane, double-click the Coffee_Exotic: Table to
open the new table with imported data.
14. CLOSE the Coffee_Exotic table.
15. In the Navigation Pane, select the Customers: Table.
16. On the External Data tab, in the Export group, click the More button.
On the menu that appears, click Word to display the Export - RTF
File dialog box.
17. If you want to specify a different destination, click Browse to open
the File Save dialog box. Choose a folder and then click Save.
18. Select the Open the destination file after the export operation is
complete check box.
19. Click OK. Word opens and the new file with exported data is
displayed. (You may need to adjust the margins and columns and
then change the orientation to layout to get the content to actually fit
if you want to print the report.)
20. CLOSE the file and then CLOSE Word.
21. Go back to Access.
22. On the Save Export Steps screen, click Close.
23. CLOSE the database.
PAUSE. LEAVE Access open for the next project.
Project 14-2: Exporting Data to a New Database
In this project, you are the manager at Southridge Video. You have created a new database to
store information about new video games. You want to export the Games table to the new
database. You have exported an Access table to other destinations, but not to another Access
database. Use Access Help if you need more information.

GET READY. LAUNCH Access if it is not already running.

1. Create a new database called New Games-final.
2. CLOSE the New Games-final database.
3. OPEN Sale Games from the data files for this lesson and then SAVE the database as Sale
Games-final.
4. On the External Data tab, in the Export group, click the Access button. The Export – Access
Database dialog box appears.
5. Click Browse and navigate to the location where you saved the New Games-final database.
Select it and then click Save.
6. Click OK. The Export dialog box appears.
7. Leave the default settings and then click OK.
8. Click Close in the Export -Access Database dialog box.
9. CLOSE the Sales Games-final database.
10. OPEN the New Games-final database, double-click the Games table to open it, and then
verify that the data was successfully exported.
11. CLOSE the Games table.
12. On the External Data tab, in the Import & Link group, click Excel. The Get External Data
– Excel Spreadsheet dialog box appears.
13. Click Browse and then open the Games to [Link] spreadsheet in the data files for this
lesson.
14. Select the Append a copy of the records to the table Games radio button and then click
OK.
15. Click Next, click Finish, and then click Close.
16. Double-click the Games table in the Navigation Pane to open it. Notice there are now more
items in the table (two new NBA Live games and one new College Hoops game).
17. CLOSE the Games table. Click Yes if you’re asked to save it.
STOP. CLOSE the database and then EXIT Access.

Knowledge Assessment (LESSON 15)

Matching
Match the term in Column 1 to its description in Column 2.
Column 1 Column 2
1. Back-end file a. In a split database, the file that contains the queries, forms, reports, and other
objects created from the tables
2. Front-end file b. Optimizes files and fixes minor problems in the file structure of a database
3. Compact and c. To scramble data in a way that can only be reconverted by an
Repair command authorized user who has the correct password
4. Encrypting d. Creates a report that shows details, or definitions, about a selected object
database and opens it in Print Preview
5. Database Documenter e. The file that contains the tables in a split database

True/False
Write T if the statement is true or F if the statement is false.
____1. Backing up files on a regular basis is really not necessary.
____ 2. Compacting and repairing a database leaves the file fragmented.
____ 3. The .accdb extension is for the Access 2002–2003 file format.
____ 4. If you forget a password for a database, Microsoft can retrieve it for you.
____ 5. It is a good idea to back up a database before splitting it.

Competency Assessment

Project 15-1: Setting Database Properties, and Encrypting and Decrypting a Database
As an investor relations specialist for Blue Yonder Airlines, you need to maintain and safeguard
the databases that you use. In this project, you will set the database properties and compact and
repair the Income and Expenses database. Then, set the Income chart to open on startup and
create a password to protect the data in the Income and Expenses database. Once the protection
is no longer needed, remove the encryption from the Blue Yonder Income and Expenses
database.

GET READY. LAUNCH Access if it is not already running.

1. OPEN the Blue Yonder database from the data files for this lesson
and then SAVE the database as Blue Yonder-final.
2. Click the File tab, click Info (if necessary), and then select the View
and edit database properties link.
3. Type Income and Expenses in the Subject box.
4. Type [Your Name] in the Author box.
5. Type Andrew Lan in the Manager box.
6. Type BlueYonder Airlines in the Company box.
 7. Click OK.
8. Click File and then select Compact & Repair Database.
9. Click File and then click Options.
10. Click the Current Database link on the left.
11. Select Income Chart from the Display Form menu.
12. Click OK and then click OK again.
13. CLOSE the database.
14. OPEN the Blue Yonder-final database in Exclusive mode.
15. Click the File tab, and on the Info menu click the Encrypt with
Password button.
16. Type #1BlueYonder$87 in the Password box.
17. Type #1BlueYonder$87 in the Verify box.
18. Click OK. Click OK if you receive a warning dialog box.
19. CLOSE the database.
20. OPEN the database.
21. Type #1BlueYonder$87 in the Enter database password box and
then click OK.
22. Open the Database Documenter.
23. Select the Income & Expenses Summary table and then click OK
to view the report.
24. Print the report or close Print Preview.
25. CLOSE the database and then reopen it in Exclusive mode.
26. Type #1BlueYonder$87 in the Enter database password box and
then click OK.
27. Click File and then click Decrypt Database.
28. Type #1BlueYonder$87 and then click OK.
29. Click File and then Save As.
30. Click Back Up Database.
31. Use the generated file name, and SAVE it in the same location as the
original version.
32. CLOSE the database.
PAUSE. LEAVE Access open to use in the next project.

Project 15-2: Merging, Backing Up, and Splitting the WingTip Database
As part of your maintenance of database files at WingTip Toys, you decide to merge a database
with another database with similar data to consolidate the two databases. In this project, you will
also create a backup of a database and split it so that others in the company can create their own
forms and reports using the data in the tables.
GET READY. LAUNCH Access if it is not already running.
1. OPEN the Wingtip database from the data files for this lesson and then SAVE the database
as Wingtip-final.
2. On the External Data tab, in the Import & Link group, click the Access button.
3. Click Browse to open the File Open dialog box.
4. Locate the Toys database file and then click Open.
5. The Import tables, queries, forms, reports, macros, and modules into the current database
option should be selected. Click OK.
6. Click the Select All button on each tab in the Import Object dialog box to import all objects
into the current database. Click OK.
7. When the Save Import Steps screen appears, click Close.
8. Click the File tab, click Save As, and then select Back Up Database.
9. Click the Save As button and then, using the generated file name with the date, click Save.
(Change the destination location if you want.)
10. On the Database Tools tab, in the Move Data group, click the Access Database button.
11. Click the Split Database button.
12. Accept the Wingtip-final_be file name and then click Split.
13. Click OK.
STOP. CLOSE the database and then EXIT Access.