Microsoft Office Tutorials and References
In Depth Information
| Case Problem 1
Apply the skills you
learned in the tutorial
to import and export
data and create charts
for a small music
Data Files needed for this Case Problem: Contract.accdb ( cont. from Tutorial 7 ),
Instrument.csv, and Room.xlsx
Pine Hill Music School Yuka wants you to integrate the data in the Contract database
with other programs, and she wants to be able to analyze the data in the database. You’ll
help her achieve these goals by completing the following:
1. Open the Contract database located in the Level.02\Case1 folder provided with
your Data Files.
2. Export the rptTeacherLessons report as an HTML document to the Level.02\Case1
folder; do not use a template or save the export steps. Use your Web browser to
open the rptTeacherLessons HTML document. Then scroll to the bottom of the page;
use the First, Previous, Next, and Last links to navigate through the document.
3. Import the CSV file named Instrument, which is located in the Level.02\Case1
folder, as a new table in the database. Choose your own primary key, name the
table tblInstrument , run the Table Analyzer, and record the Table Analyzer’s recom-
mendation, but do not accept the recommendation. Do not save the import steps.
4. Export the tblTeacher table as an XML file named tblTeacher to the Level.02\Case1
folder; do not create a separate XSD file. Save the export steps.
5. Link to the Room workbook, which is located in the Level.02\Case1 folder, using
tblRoom as the table name. Add a new record to the Room workbook as follows:
Room Num 5 , Rental Cost $25 , and Type Private .
6. Open the qryLessonsByTeacher query, and create a PivotTable with MonthlyLessonCost
as the detail field, LessonType as the column field, LastName as the row field, and
LessonLength as the filter field. Filter the LessonType field, selecting Guitar, Piano, and
Saxophone. Add subtotals and a grand total for the MonthlyLessonCost field values.
7. Switch to PivotChart view. Display the legend (if necessary), change the color of the
Saxophone data markers to yellow, and then save the query.
8. Close the Contract database without exiting Access, make a backup copy of the
database, open the Contract database, compact and repair the database, close the
database, and then exit Access.
| Case Problem 2
Use the skills you
learned in the tutorial
to analyze the data for
a health and fitness
Data Files needed for this Case Problem: CreditCard.xml, ParkLogo.gif, ParkTemp.htm,
Schedule.xlsx, and Training.accdb ( cont. from Tutorial 7 )
Parkhurst Health & Fitness Center Martha Parkhurst wants you to integrate the data in
the Training database with other programs, and she wants to be able to analyze the data
in the database. You’ll help her achieve these goals by completing the following:
1. Open the Training database located in the Level.02\Case2 folder provided with your
2. Export the qryPhysicalsNeeded query as an HTML document to the Level.02\Case2
folder, using the HTML template file named ParkTemp, which is located in the
Level.02\Case2 folder, and saving the file as qryPhysicalsNeeded . Save the export steps.