Illustrated Access 2013
Unit A: SAM Project 1a
Contact Management Database creating a table AND BUILDING A RELATIONSHIP

Project Goal
M Project Name
Project Goal

Illustrated Access 2013
Unit A: SAM Project 1a
Contact Management Database creating a table AND BUILDING A RELATIONSHIP

Project Goal
M Project Name
Project Goal

PROJECT DESCRIPTION
Your sales manager created a database with one table named Customers that stores the company name and contact information for customers. He asks you to create a new table named SalesReps to store company sales representative data. You’ll create the new table and add two records to it. Then you’ll create the relationship between the SalesReps and Customers tables.

GETTING STARTED * Download the following file from the SAM website: * IL_Access2013_UA_P1a_FirstLastName_1.accdb * Open the file you just downloaded and save it with the name: * IL_Access2013_UA_P1a_FirstLastName_2.accdb * Hint: If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access will add the file extension for you automatically. * 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
Create and save a new table in Design View with one field as follows: a. Add a field with the name SalesRepNum and the Number data type. b. Set the SalesRepNum field as the table’s primary key. c. Save the table with the name SalesReps.
In Table Design View, add another field to the SalesReps table using the field name RepFirst and the Short Text data type.
In Table Design View, add a field to the SalesReps table using the field name RepLast and the Short Text data type.
In Table Design View, add a field to the SalesReps table using the field name HireDate and the Date/Time data type.
In Table Design View, add a field to the SalesReps table using the field name StartingSalary and the Currency data type. Save the SalesReps table and switch to Datasheet View.
Enter the two records shown in Table 1 below into the SalesReps table. Save and close the SalesReps table.
Table 1: SalesReps Data
© 2014 Cengage Learning.
© 2014 Cengage Learning.

SalesRepNum | RepFirst | RepLast | HireDate | StartingSalary | 1 | Jonathan | Plank | 1/1/14 | $41,000.00 | 2 | Tia | Lee | 2/1/14 | $41,500.00 |

Open the Customers table in Table Design View. Change the name of the field named First to ContactFirst, then change the name of the field named Last to ContactLast.
In the Customers table, change the data type property of the Comment field to Long Text.
Add a new field to the Customers table after the Comment field. The field should be named SalesRepNum and have the Number data type. Save and close the Customers table.
Open the Relationships window, then add the Customers table and the SalesReps table to the Relationships window. Close the Show Table dialog box. Create a one-to-many relationship between the SalesReps and Customers tables using the common SalesRepNum field. (Hint: Resize the field list for the Customers table so that all fields are visible in the field list.) Enforce referential integrity on the relationship. Save and close the Relationships window.
Open the Customers table in Datasheet View and resize the CompanyName field to be as wide as needed to display all of the data in the field. Save the Customers table.
Find, select, and delete the record with the CompanyNum field value of R12668 (and the CompanyName field value of KBR) from the Customers table.
Navigate to the record with the CompanyNum field value of R14855 (and the CompanyName field value of Petrohawk), then add a value of 1 to the SalesRepNum field.
Navigate to the previous record (with the CompanyNum field value of R14420 and the CompanyName field value of Moody National Bank), then add a value of 1 to the SalesRepNum field. Save and close the Customers table.
Open the SalesReps table in Datasheet View, then expand the subdatasheet for SalesRepNum 1. (Hint: You should see the two related records from the Customers table.) In the subdatasheet, find the record with the CompanyNum field value of R14420. In that record, change the value of the CompanyName field to First National Bank. Close the SalesReps table.
Save and close any open objects in your database. Compact and repair your database, close it, and exit Access. Follow the directions on the SAM website to submit your completed project.

*
excel