I need assistance with Access.aab file homework. I have attached all files and instructions.
>Sheet1
5
025
6 3
6 12 A10025 10 B10216 585 2 B10216 585 4 A10025 3/2/16 12 1550 B10216 6 600 A10025 146 6 B10216 2 A10025 3/28/16 10 1000 B10216 585 12 Buffalo NY 14202 Buffalo NY
New Perspectives Access 2016 | Module 2: SAM Project 1a New Perspectives Access 2016 | Module 2: SAM Project 1a Healthy Myles Fitness Center UPDATING TABLES, ADDING RECORDS, AND CREATING RELATIONSHIPS · Open the file NP_AC16_2a_FirstLastName_1.accdb, available for download from the SAM website. · Save the file as NP_AC16_2a_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_2a_Clients.accdb
· Support_NP_AC16_2a_Location.txt
· Support_NP_AC16_2a_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. 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. You have already set up the basic structure of the database, and you are now updating some information and creating relationships to make information more useful and easier to locate. Open the tblTrainer table in Design View, and then set the field properties shown in Table 1 on the next 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 after the Interests field. Use the following options (some of which may already default to the values shown below): Table 2: tblClient Table – Field Properties Field Name Data Type Description Field Size Other ClientID Short Text Primary Key 6 Caption: Client ID State Short Text 2 Default Value: NY Zip Short Text 10 Phone Short Text 15 Gender Short Text F (Female), M (Male) 1 Switch the tblClient table to Datasheet view, then add the records shown in Table 3 below. Close the tblClient table when you are finished. Client ID FirstName LastName Address City State Zip Phone Birth Date Gender A10025 Alfred Pineda 465 Grant St. Buffalo NY 14213 716-555-4815 12/15/1964 M B10216 Maria Johnston 701 Washington St. Buffalo NY 14203 716-555-9855 2/28/1971 F Your supervisor exported his existing location data to a text file, and he asks you to add this data to the tblLocation table. Import the data as follows: 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. 2 _Status
65E-5C17-4DCC-A541-A98C96A3D113}
{7C30865E-5C17-4DCC-A541-A98C96A3D113} Score IfSuccessful Buffalo NY Buffalo NY Sports Medicine Personal Trainer Personal Trainer Strength Training NSCA-CSCS Personal Trainer NSCA-CPT Sports Medicine NASM Personal Trainer NSCA-CPT SUNY Buffalo SELECT [FirstName] & ” ” & [LastName] AS StudentName, [_GradingReport].ProjectName, “Submission #” & [SubmissionNum] AS SubmissionCt, [_GradingReport].Score, [_GradingReport].MaxScore, “Score is: ” & [Score] & ” out of ” & [MaxScore] AS Grade, [_GradingReportSteps].[StepNumber] & “. ” & [Description] AS Step, [_GradingReportSteps].[StepScore] & “/” & [_GradingReportSteps].[StepMaxScore] AS StepGrade, [_GradingReportSteps].ActionName, IIf([ifsuccessful]=True,Null,[ErrorText]) AS Feedback, [_GradingReportSteps].IfSuccessful, [_GradingIcons].GA_Status_Icon, [_GradingReportSteps].StepNumber, [_GradingReportSteps].StepActionOrder, [_GradingReportSteps].ID, [_GradingIcons].SAM_Logo, [_GradingReportSteps].Description, [_GradingReport].EngineVersion2
BillingID
ClientID
TrainerID
StartDate
EndDate
Sessions
Amount
A805
6
A
10
585
2/1/16
2/17/16
600
A806
4
B10216
3
12
2/2/16
2/18/16
800
A80675
A10025
612
3/3/16
5/19/16
1550
A80342
704
3/7/16
3/28/16
1000
B80671
3/1/16
3/2/16
250
B80699
3/8/16
3/29/16
450
B85654
146
3/17/16
A80399
774
3/16/16
4/27/16
A84575
3/9/16
3/21/16
750
A84975
709
3/22/16
3/24/16
150
B80345
312
4/18/16
A80610
4/1/16
5/17/16
1200
Sheet2
Sheet3
321
Genesee
7/2/2015 0:00:00
229 W Genesee St.
Buffalo
NY
14202
716-555-9427
335
Downtown
4/8/2014 0:00:00
300 Pearl St.
716-555-2154
318
Eastside
2/7/2015 0:00:00
883 Jefferson Ave.
14204
716-555-4455
GETTING STARTED
PROJECT STEPS
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. Save the tblTrainer table. (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.) Switch to viewing the tblTrainer table in Datasheet View.
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: Randy Lee (TrainerID 312), Raj Patel (TrainerID 612), and Carson Garner (TrainerID 708). Save and close the table.
Create a new table in the database with the name tblClient by importing the structure of the tblClient table in the file Support_NP_AC16_2a_Clients.accdb, available for download from the SAM website, into the current database. Do not save the import steps.
Open the tblClient table in Design View, and then update the field properties using the information in Table 2 on the next page. Be sure to set the ClientID field as the primary key for the table. When you have finished, save the tblClient table.
Table 3: tblClient Table – Records
f. Specify the file Support_NP_AC16_2a_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. Open the tblLocation table in Datasheet view to confirm that the data imported properly, then close the table.
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_2a_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. Open the tblBilling table in Datasheet view to confirm that the data imported properly, then close the table.
Add the tblTrainer, tblBilling, and tblClient tables to the Relationships window. Use the mouse to 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 tblClient table and the related tblBilling table using the ClientID 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
ClientID
FirstName
LastName
Address
City
State
Zip
Phone
BirthDate
Gender
IfSuccessful
GA_Status_Icon
SAM_Logo
true
false
ID
FirstName
LastName
AssignmentGUID
UserID
false
Thomas
Simpson
{7C
308
ID FirstName LastName
ProjectName
SubmissionNum
Max
Score
EngineVersion
ID
StepNumber
Description
StepScore
StepMaxScore
ErrorText
ActionName
StepActionOrder
BillingID
ClientID
TrainerID
StartDate
EndDate
Amount
Sessions
LocationID
LocationName
LocationOpened
Address
City
State
ZipCode
Phone
301
Elmwood
5/2/14
1400 Elmwood Ave.
Buffalo
NY
14216
716-555-1212
302
Riverside
6/8/15
733 Hertel Ave.
14207
716-555-8315
308
Sheridan
1/5/15
5200 Sheridan Dr.
14221
716-555-7621
TrainerID FirstName LastName
Specialty
Certification
College
HireDate
Interests
146
Jennifer
Young
Personal Trainer
ACE
University of Nebraska
3/19/14
nutrition, strength training, motivation
312
Randy
Lee
Strength Training
NSCA-CSCS
SUNY Buffalo
9/27/13
yoga, nutrition
454
Josh
Martinez
Sports Medicine
NATA
Saint Francis University
11/9/15
martial arts, strength training
585
Cam
Williams
NASM
SUNY Albany
6/10/14
nutrition, martial arts
612
Raj
Patel
NSCA-CPT
Florida State University
1/4/11
yoga, dance fit, nutrition
693
Sandy
Larson
PTA Global
Northwestern University
2/11/15
martial arts, nutrition, yoga
704
Melissa
Rogers
Cornell University
6/15/15
sports medicine, yoga
708
Carson
Garner
San Diego State University
2/25/13
nutrition, yoga, swimming
709
Richard
Sanderson
University of Tennessee
6/1/12
strength training, swimming
774
Sally
Andrews
4/12/14
nutrition, sports medicine, strength training
FROM _GradingReport, _GradingIcons INNER JOIN _GradingReportSteps ON [_GradingIcons].[IfSuccessful_Status] = [_GradingReportSteps].IfSuccessful;