New Perspectives Access 2016 | Module 1: SAM Project 1a
Healthy Myles Fitness Center
CREATING a table, query, form, and report

GETTING STARTED

• Open the file NP_AC16_1a_FirstLastNameaccdb, available for download from the SAM website.
• Save the file as NP_AC16_1a_FirstLastNameaccdb 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:
  • 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. You are employed at Healthy Myles Fitness Center and are in charge of consolidating the company’s records and other important information into a database. The owners expect that the database will help them run the business more smoothly. You start by setting up the basic structure of the database.

Create a new table in Datasheet View:

1. Save the table as (Hint: Do not type the period.)
2. Change the name of the ID field in the tblClient table to ClientID. (Hint: Do not type the period.)
3. Change the data type of the ClientID 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. (Hint: Do not type the period.)

2. With the tblClient table still open in Datasheet View, enter the records shown in Table 1 on the next page. (If necessary, resize the Address field to best fit the data it contains.) Close the tblClient table, saving if necessary.

 
 
Table 1: tblClient Table Records

ClientID	FirstName	LastName	Address	City	State	Zip
A10027	Jenny	Lin	220 Delaware Ave.	Buffalo	NY	14202
B18423	Adam	Smith	266 Elmwood Ave.	Buffalo	NY	14222

 

3. 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 Close the tblLocation table.

 
Table 2: tblLocation Table Records
 

LocationID	LocationName	LocationOpened	Address	City	State	ZipCode	Phone
301	Elmwood	5/2/2014	1400 Elmwood Ave.	Buffalo	NY	14216	716-555-1212
308	Sheridan	1/5/2015	5200 Sheridan Dr.	Buffalo	NY	14221	716-555-7621

 

6. You have been provided with a support file that contains a table with additional equipment data. The tblEquipment table has the same design as the tblGym table in the support file. Copy all the records from the tblGym table in the file accdb, available for download from the SAM website, and paste them at the end of the tblEquipment table in the current database. Close the tblEquipment table.
7. Use the Simple Query Wizard to create a query based on the tblTrainer table as described below:
   1. Include the FirstName, LastName, and Specialty fields (in that order) from the tblTrainer table in the query.
   2. Name the query qrySpecialties, and then close the query.
8. You now want to create a simple form to allow employees to update information on the trainers employed at Healthy Myles Fitness Center.
   1. Use the Form tool to create a simple form based on the tblTrainer
   2. Save the form with the name frmTrainer. (Hint: Do not type the period.)
9. Switch the frmTrainer form to Form View, use the navigation buttons to navigate to the third record (with the TrainerID field value 454), and then change the Certification field value to ACSM. (Hint: Do not type the period.)
10. With the frmTrainer form still open in Form View, navigate to the last record (with the TrainerID field value 709) in the frmTrainer form, and then change the Specialty field value to Strength Training. (Hint: Do not type the period.) Navigate back to the first record in the tblTrainer table, and then close the form.
11. Use the Report tool to create a report based on the tblEquipment Save the report as rptEquipmentList, then close the report.
12. You decide to adjust the formatting of the rptTrainer report to make it easier to read and to make it fit on one page.
    1. Open the rptTrainer report in Layout View, and then reduce the width of the TrainerID column so it is approximately as wide as the text “TrainerID” in the column heading, or 8“.
    2. Select the text box that appears below the TrainerID column (and contains the number 9) in the rptTrainer 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, or approximately 0.27” high, as shown in Figure 1 on the next page. Save the report.
    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 left edge vertically aligns with the left edge of the Specialty column above it. Right-align the text. View the report in Print Preview to see the changes you made. Save and close the report.

Figure 1: rptTrainer Report
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.