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

GRMH Recreation Centers

creating a table, query, form, and report to manage recreation data

GETTING STARTED

• Open the file NP_AC16_1b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC16_1b_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 file from the SAM website:
    • Support_NP_AC16_1b_Equipment.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. Samantha Cooper is the general manager of the Grand Rapids-Muskegon-Holland (GRMH) Recreation Centers in Grand Rapids, MI. The recent expansion of programs and staff at several locations has necessitated a change in how she manages the centers’ operations. She has selected Access 2016 to manage the business data. You’ll help Samantha create and maintain an Access database to store data about trainers, members, and equipment.
   
   Create a new table in Datasheet View as described below:
   1. Save the table using tblMember as the name.
   2. Change the name of the ID field in the tblMember table to MemberID. (Hint: Do not type the period.)
   3. Change the data type of the MemberID field to Short Text.
   4. Create the following fields in the table, using the Short Text data type, in the order shown: FirstName, LastName, Address, City, State, and Zip.

1. With the tblMember table still open in Datasheet view, enter the records shown in Table 1 below. (Hint: If necessary, resize the Address field to best fit the data it contains.) Close the tblMember table, saving it if necessary.

Table 1: tblMember Table

1. Open the tblLocation table in Datasheet View, and then update the table as described below:
   1. Change the name of the ID field to LocationID. (Hint: Do not type the period.)
   2. Change the data type of the LocationID field to Short Text.
   3. Save the table, but do not close it.
   4. With the tblLocation table still open in Datasheet View, change the data type of the LocationOpened field to Date/Time. Save but do not close the table.
   5. With the tblLocation table still open in Datasheet View, enter the records shown in Table 2 below into the tblLocation table. Close the table.

Table 2: tblLocation Table

1. Samantha created a database that contains a table with additional equipment data. Copy all the records from the tblGym table in the Support_NP_AC16_1b_Equipment.accdb database, available for download from the SAM website, and then paste them at the end of the tblEquipment table in the current database. (Hint: The tblEquipment table has the same design as the tblGym table.) Close the tblEquipment table.
2. Use the Simple Query Wizard to create a query based on the tblTrainer table. Include the FirstName, LastName, and Specialty fields from the tblTrainer table in the query, in that order. Name the query qrySpecialties, and then close the query.
3. You now want to create a simple form to allow employees to update information on the trainers employed at GMRH Recreation Centers.

Use the Form tool to create a simple form based on the tblTrainer table. Save the form using frmTrainer as the name.

1. Switch the frmTrainer form to Form view, then use the navigation buttons to navigate to the third record (for TrainerID 454), and then change the Certification field value to RYT. (Hint: Do not type the period.)
2. Navigate to the last record (for TrainerID 709) in the frmTrainer form, and then change the Specialty field value to Strength Training. (Hint: Do not include the period.) Navigate back to the first record in the frmTrainer form, and then close the form.
3. Use the Report tool to create a report based on the tblEquipment table. Save the report as rptEquipmentList, then close it.
4. You decide to adjust the formatting of the rptTrainer report to make it easier to read and to ensure that all information is visible.
   1. In Layout View for the rptTrainer report, use the mouse to reduce the width of the TrainerID column so it is approximately as wide as the text “TrainerID” in the column heading, or approximately 0.8”.
   2. Select the text box that appears below the TrainerID column (and contains the number 9) in the rptTrainer report. Then use the mouse to resize the height of the text box slightly so that it is tall enough to completely display the value it contains, as shown in Figure 1 on the next page (approximately 0.2”).
   3. Select the text box that appears below the Specialty column and that contains the text “Page 1 of 1”. Use an arrow key to move the text box to the left so that its right edge vertically aligns with the right edge of the column above it, then right-align the contents of the text box. View the report in Print Preview to view the changes you made. Save and close the report.

Figure 1: rptTrainer Report

Save and close any open database 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.