Homework answers / question archive / New Perspectives Access 2016 | Module 8: SAM Project 1b GRMH Recreation Centers Linking, Importing, and Exporting Data   GETTING STARTED Open the file NP_AC16_8b_FirstLastName_1

New Perspectives Access 2016 | Module 8: SAM Project 1b GRMH Recreation Centers Linking, Importing, and Exporting Data   GETTING STARTED Open the file NP_AC16_8b_FirstLastName_1

MS Access

Share With

---

New Perspectives Access 2016 | Module 8: SAM Project 1b

GRMH Recreation Centers

Linking, Importing, and Exporting Data

 

GETTING STARTED

• Open the file NP_AC16_8b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC16_8b_FirstLastName_2.accdb by changing the “1” to a “2”.
  • If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
  • To complete this Project, you will also need to download and save the following support files from the SAM website:
    • Support_NP_AC16_8b_Nonprofit.csv
    • Support_NP_AC16_8b_Events.xml
    • Support_NP_AC16_8b_Offsite.xlsx
    • Support_NP_AC16_8b_Venues.accdb
    • Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

 

PROJECT STEPS

1. Import the text file Support_NP_AC16_8b_Nonprofit.csv, available for download from the SAM website, as a new table in the database as described below:
   1. Choose the comma as the delimiter.
   2. Use the names in the first row as field names.
   3. Make sure all nine fields use the Short Text data type, including the NonprofitID field. (Hint: You will need to scroll the display to see all nine fields.)
   4. Choose the NonprofitID field as the table’s primary key.
   5. Use tblNonprofit as the name for the table. Do not save the import steps.
   6. Import the structure and data from the XML file Support_NP_AC16_8b_Events.xml, available for download from the SAM website, as a new table named tblEvents in the database. (Hint: The tblEvents table is the only table in the XML file.) Do not save the import steps.
   7. Export the tblLocation table as an HTML document, following the steps below:
      1. Use the file name tblLocation.html for the exported file.
      2. Export the data with formatting and layout.
      3. Do not change the default HTML Output Options.
      4. Save the export steps with the name Export-Location and with the description HTML file with Location information. (Hint: Be sure to include the period at the end of the description.)
      5. Export the tblTrainer table as an XML file, following the steps below:
         1. Use the file name tblTrainer.xml for the exported file.
         2. Embed the schema (XSD) in the exported XML data document, and confirm the Export Presentation (XSL) option is not selected. (Hint: Remember that you need to click More Options to embed the schema.)
         3. Save the export steps with the name Export-tblTrainer and the description XML file with Trainer information. (Hint: Be sure to include the period at the end of the description.)
         4. Link to the data in the workbook Support_NP_AC16_8b_Offsite.xlsx, available for download from the SAM website, using tblOffsite as the table name. (Hint: The first row in this workbook contains the column heading.)
         5. Open the frmTrainersBilling form in Design View, and then add a tab control to the form by completing the following steps:
            1. Cut the existing subform control (located below the Accepts Minors label in the Detail section).
            2. Add a tab control below the Accepts Minors label in the Detail section, and place it so the left edge of the tab control is aligned with the left edge of the Interests label at approximately the 0.25” mark on the horizontal ruler, and the top edge is located at approximately the 3" mark on the vertical ruler.
            3. Paste the subform cut from the frmTrainersBilling form on the first (left) page of the tab control.
            4. With the frmTrainersBilling form still open in Design View, change the caption for the main form’s navigation buttons to Trainer. (Hint: Do not type the period.)
            5. With the frmTrainersBilling form still open in Design View, modify the tab control as described below:
               1. Change the caption for the left tab to Billing Data. (Hint: Do not type the period.)
               2. Change the caption for the right tab to Billing Chart. (Hint: Do not type the period. This tab page should be blank.)

Save, but do not close, the form.

1. Add a chart to the Billing Chart page of the tab control and include the following options:
   1. Insert the chart near the upper-left corner of the black portion of the Billing Chart page.
   2. Use the tblBilling table as the record source.
   3. Select BillingID and Amount as the fields for the chart.
   4. Use 3-D Cylinder Bar Chart (3^rd column, 2^nd row) as the chart type.
   5. Do not change the default chart layout or the default fields to link records to the chart options.
   6. Use Amount by Billing as the chart title.
   7. Do not display a legend in the chart.

Refer to Figure 1 below and Figure 2 on the following page. Save and close the frmTrainersBilling form. (Hint: Your graph may look incorrect until you save the form and view the chart in Form View.)

 

Figure 1: Billing Data (Left) Tab of the Subform in Form View

 

Figure 2: Billing Chart (Right) Tab of the Subform in Form View

 

1. Open the frmLocationSessions form in Design View. Modify the Sessions by Trainer chart on the Session Chart page of the tab control by removing the legend from the chart.
2. Further modify the Sessions by Trainer chart on the Sessions Chart page of the tab control by changing the chart type to a 3-D Column chart.
3. Change the blue data markers on the chart to Pink (1^st column, 4^th row of the Color palette). Save the form, switch to Form View, and then view the Session Chart page. Confirm the chart matches Figure 3 on the following page, and then close the frmLocationSessions form.

 

Figure 3: Record 1 on the Session Chart Tab in the Sessions by Trainer Chart

 

1. Create an application part and use it to create a new table in the NP_AC16_8b_FirstLastName_2.accdb file by following the steps below:
   1. Open the file Support_NP_AC16_8b_Venues.accdb, available for download from the SAM website, and then save it as a template.
   2. In the Create New Template from This Database dialog box, create an application part with the name Venues and the description Additional Venues. (Hint: Do not type the period.) Do not modify any other options in the dialog box.
   3. Close the Support_NP_AC16_8b_Venues.accdb database.
   4. Activate the NP_AC16_8b_FirstLastName_2.accdb database, and then use the Venues application part to create a new table. The table created by the application part should have no relationship to any of the other tables.

Open the tblVenues table to verify that the table has been imported, and then close the tblVenues table.

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.