Save sam lab answer key

New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a

New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a

Sandra Coates Skating Club

Creating Tables, Queries, Forms, and Reports in a Database

GETTING STARTED
· Open the file NP_AC16_CS1-4a_FirstLastName_1.accdb, available for download from the SAM website.

· Save the file as NP_AC16_CS1-4a_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_CS1-4a_Athletes.xlsx

· Support_NP_AC16_CS1-4a_SCSC.accdb

· Support_NP_AC16_CS1-4a_Locations.txt

· Support_NP_AC16_CS1-4a_Levels.txt

· 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
Sandra Coates Skating Club (SCSC) is committed to the development of skaters of all ages. It manages athlete and coach data for skating athletes located at various skating clubs throughout Pennsylvania. Sandra Coates is the founder and general chairperson of SCSC, and she has just received approval from the board of directors to replace SCSC’s manual system of managing data about athletes, coaches, and locations with a database. She has asked you to help her create objects in the database that she and other staff members can use to manage data about athletes, coaches, and their locations. Open the tblCoach table in Design View, and then make the following changes:

a. Move the LevelID field immediately after the CoachID field.

b. Delete the CertificationLevel field from the table. (Hint: If a warning message appears asking if you want to permanently delete the field and all the data it contains, click Yes.)

c. Save the table.

Switch to Datasheet View, enter the records shown in Table 1 below into the tblCoach table, and then close the table.

Table 1: New Records for the tblCoach Table

Coach ID

Level ID

Coach First Name

Coach Last Name

BGC Expiration

Certification Expiration

901900

BEG2

Angela

Pearson

12/31/2020

12/31/2020

901901

BEG1

Nora

Keller

12/31/2020

12/31/2020

Create a new table in Datasheet View. Save the table as tblAthlete, and then make the following changes in Datasheet View:

a. Change the data type of the ID field to Short Text, and then change the field name to AthleteID.

b. Add the following fields to the table in the order listed, and choose the Short Text data type for each field: LocationID, FirstName, LastName, Address, City, State, Zip, LevelID, and Gender.

c. Save the table.

Switch to Design View for the tblAthlete table, and then set the field properties shown in Table 2 below.

Table 2: Field Properties for the tblAthlete Table

Field Name

Data Type

Description

Field Size

Other

AthleteID

Short Text

Primary key

5

Caption: Athlete ID

LocationID

Short Text

Foreign key

4

Caption: Location ID

FirstName

Short Text

20

Caption: First Name

LastName

Short Text

30

Caption: Last Name

Address

Short Text

35

City

Short Text

35

State

Short Text

2

Default Value: PA

Zip

Short Text

10

LevelID

Short Text

Foreign key

4

Caption: Level ID

Gender

Short Text

M, F

1

Add a new field to the tblAthlete table, immediately after the Zip field. Use the field name BirthDate, the Date/Time data type, the Short Date format, and the caption Birth Date.

Move the LevelID field so that it is located immediately after the AthleteID field. Save and close the table.

Much of the data that Sandra needs in the database is currently stored in other formats and locations, so you need to import it into the database. Use the Import Spreadsheet Wizard to add data to the tblAthlete table from an Excel spreadsheet as follows:

a. Specify the file Support_NP_AC16_CS1-4a_Athletes.xlsx, available for download from the SAM website, as the source of the data.

b. Select the option to append a copy of the records to the tblAthlete table.

c. In the Import Spreadsheet Wizard dialog boxes, choose the Athlete worksheet, and then import to the tblAthlete table. Do not save the import steps.

Import the structure of the tblLocation table in the database Support_NP_AC16_CS1-4a_SCSC.accdb, available for download from the SAM website, into the current database. Do not save the import steps.

Open the tblLocation table in Design View, and then update the field properties shown in Table 3 below. Set the LocationID field as the Primary Key, then save and close the tblLocation table.