UPDATING TABLES, ADDING RECORDS, AND CREATING RELATIONSHIPS
GETTING STARTED
· Open the file NP_AC16_2b_FirstLastName_1.accdb, available for download from the SAM website.
· Save the file as NP_AC16_2b_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_2b_Members.accdb
· Support_NP_AC16_2b_Location.txt
· Support_NP_AC16_2b_Billing.xlsx
· 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
You are an employee at the GRMH Recreation Centers in Grand Rapids, MI. Your supervisor, Samantha Cooper, had decided to create an Access database to more easily track information such as trainer, member, and billing data, and has asked for your help creating it. In this project, you’ll work with updating tables, importing data, and creating relationships.
Open the tblTrainer table in Design View, and then set the field properties shown in Table 1 on the following page. Be sure to set the TrainerID field as the primary key for the table.
Table 1: tblTrainer Table – Field Properties
| Field Name | Data Type | Description | Field Size | Other |
| TrainerID | Short Text | Primary Key | Caption: Trainer ID | |
| FirstName | Short Text | 20 | Caption: First Name | |
| LastName | Short Text | 25 | Caption: Last Name | |
| Interests | Long Text |
Add a new field to the table, so it appears after the Interests field. Use the following options (some of which may already default to the values shown below):
a. Field Name: Minors
b. Data Type: Yes/No
c. Format Property: Yes/No
d. Caption Property: Accepts Minors
e. Default Value: No
Delete the College field from the table. Switch to viewing the tblTrainer table in Datasheet View, and then save the table when prompted. (Hint: Click the Yes button when warned that you are about to delete one record, and click Yes again if you are warned about potential data loss.)
While viewing the tblTrainer table in Datasheet View, move the Interests field to the left of the HireDate field.
While viewing the tblTrainer table in Datasheet View, update the following records to indicate that these trainers will accept clients who are minors: Tom Fowler (TrainerID 312), Andrea Hayward (TrainerID 612), and Kathy Pearce (TrainerID 708). Close the table, saving it if prompted.
Create a new table in the database by importing the structure of the tblMember table in the file Support_NP_AC16_2b_Members.accdb, available for download from the SAM website, into the current database. Do not save the import steps.
Open the tblMember table in Design View, and then update the field properties using the information in Table 2 below. Be sure to set the MemberID field as the primary key for the table. When you have finished, save the tblMember table.
Table 2: tblMember Table – Field Properties
| Field Name | Data Type | Description | Field Size | Other |
| MemberID | Short Text | Primary Key | 6 | Caption: Member ID |
| State | Short Text | 2 | Default Value: MI | |
| Zip | Short Text | 10 | ||
| Phone | Short Text | 15 | ||
| Gender | Short Text | F (Female), M (Male) | 1 |
Switch the tblMember table to Datasheet view, then add the records shown in Table 3 below to the tblMember table. Close the table when you are finished.
Table 3: tblMember Table – Records
| Field Name | Record 1 | Record 2 |
| Member ID | A10025 | B10216 |
| FirstName | Nikki | Bill |
| LastName | Snyder | Plank |
| Address | 11 Munroe Ave. NW | 255 28th St. SW |
| City | Grand Rapids | Grand Rapids |
| State | MI | MI |
| Zip | 49503 | 49548 |
| Phone | 616-555-4815 | 616-555-9855 |
| Birth Date | 12/15/1964 | 2/28/1971 |
| Gender | F | M |
Samantha exported her existing location data to a text file, and she asks you to add this data to the tblLocation table. Import the data as follows:
f. Specify the file Support_NP_AC16_2b_Location.txt, available for download from the SAM website, as the source of the data.
g. Select the option to append a copy of the records to the tblLocation table.
h. In the Import Text Wizard dialog boxes, choose the option to import delimited data, to use a comma delimiter, and to import the data into the tblLocation table. Do not save the import steps.
Use the Import Spreadsheet Wizard to add data to the tblBilling table from an Excel spreadsheet as follows:
i. Specify the file Support_NP_AC16_2b_Billing.xlsx, available for download from the SAM website, as the source of the data.
j. Select the option to append a copy of the records to the tblBilling table.
k. In the Import Spreadsheet Wizard dialog boxes, choose the Sheet1 worksheet, and then import to the tblBilling table. Do not save the import steps.
Add the tblTrainer, tblBilling, and tblMember tables to the Relationships window. Resize the field list for each table shown in the Relationships window so all fields are visible in the field list. Define a one-to-many relationship between the primary tblTrainer table and the related tblBilling table using the TrainerID field. Choose the options to enforce referential integrity and to cascade updates to related fields.
Define a one-to-many relationship between the primary tblMember table and the related tblBilling table using the MemberID field. Choose the options to enforce referential integrity and to cascade updates to related fields. Figure 1 below shows the completed Relationships window. Save and close the Relationships window.
Figure 1: Relationships Window
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.
Support_NP_AC16_2b_Location.txt
| 321 | Walker | 7/2/2015 0:00:00 | 358 Cedar Ridge Dr. | Walker | MI | 49544 | 616-555-9427 |
| 335 | Downtown | 4/8/2014 0:00:00 | 300 Pearl St. NW | Grand Rapids | MI | 49504 | 616-555-2154 |
| 318 | North Walker | 2/7/2015 0:00:00 | 2151 Holton Ct. NW | Walker | MI | 49544 | 616-555-4455 |
Sheet1
| BillingID | MemberID | TrainerID | StartDate | EndDate | Sessions | Amount |
| A80565 | A10025 | 585 | 2/1/16 | 2/17/16 | 6 | 600 |
| A80643 | B10216 | 312 | 2/2/16 | 2/18/16 | 6 | 800 |
| A80675 | A10025 | 612 | 3/3/16 | 5/19/16 | 12 | 1550 |
| A80342 | A10025 | 704 | 3/7/16 | 3/28/16 | 10 | 1000 |
| B80671 | B10216 | 585 | 3/1/16 | 3/2/16 | 2 | 250 |
| B80699 | B10216 | 585 | 3/8/16 | 3/29/16 | 4 | 450 |
| B85654 | A10025 | 146 | 3/2/16 | 3/17/16 | 12 | 1550 |
| A80399 | B10216 | 774 | 3/16/16 | 4/27/16 | 6 | 600 |
| A84575 | A10025 | 146 | 3/9/16 | 3/21/16 | 6 | 750 |
| A84975 | B10216 | 709 | 3/22/16 | 3/24/16 | 2 | 150 |
| B80345 | A10025 | 312 | 3/28/16 | 4/18/16 | 10 | 1000 |
| A80610 | B10216 | 585 | 4/1/16 | 5/17/16 | 12 | 1200 |
