Expert answer:Please follow the instructions below to complete e
Expert answer:Follow the instructions on the word file. All files attatched below.
instructions_np_ac16_cs1_4a.docx
support_np_ac16_cs1_4a_athletes.xlsx
support_np_ac16_cs1_4a_levels.txt
support_np_ac16_cs1_4a_locations.txt
instructions_np_ac16_cs1_4a.docx
support_np_ac16_cs1_4a_athletes.xlsx
support_np_ac16_cs1_4a_levels.txt
support_np_ac16_cs1_4a_locations.txt
Unformatted Attachment Preview
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”.
o
•
•
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:
o
Support_NP_AC16_CS1-4a_Athletes.xlsx
o
Support_NP_AC16_CS1-4a_SCSC.accdb
o
Support_NP_AC16_CS1-4a_Locations.txt
o
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
1.
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.
New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a
2.
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
901900
Level
ID
BEG2
Coach First
Name
Angela
Coach Last
Name
Pearson
BGC
Expiration
12/31/2020
Certification
Expiration
12/31/2020
901901
BEG1
Nora
Keller
12/31/2020
12/31/2020
3.
4.
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
AthleteID
LocationID
Data Type
Short Text
Short Text
FirstName
LastName
Address
City
State
Zip
LevelID
Gender
Short
Short
Short
Short
Short
Short
Short
Short
Text
Text
Text
Text
Text
Text
Text
Text
Description
Primary key
Foreign key
Field Size
5
4
Foreign key
M, F
20
30
35
35
2
10
4
1
Other
Caption:
Caption:
ID
Caption:
Caption:
Athlete ID
Location
First Name
Last Name
Default Value: PA
Caption: Level ID
5.
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.
6.
Move the LevelID field so that it is located immediately after the AthleteID field.
Save and close the table.
New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a
7.
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.
8.
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.
9.
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.
Table 3: Field Properties for the tblLocation Table
Field Name
LocationID
Data Type
Short Text
LocationName
Short Text
10.
11.
Description
Primary key
Field Size
4
50
Other
Caption:
Location ID
Caption:
Location Name
Sandra 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 instructed below:
a.
Specify the file Support_NP_AC16_CS1-4a_Locations.txt, 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 tblLocation
table.
c.
In the Import Text Wizard dialog boxes, choose the options to import
delimited data, to use a comma delimiter, and to import the data
into the tblLocation table. Do not save the import steps.
d.
Open the tblLocation table in Datasheet View, and then resize the columns
to best fit the data they contain.
e.
Save and close the tblLocation table.
Create a new table in Design View, and then set the field properties shown in
Table 4 on the following page. The LevelID field should be the primary key for
the table. Save the table with the name tblLevel, and then close the table.
New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a
Table 4: Field Properties for the tblLevel Table
Field Name
Data Type
Description
LevelID
Primary
key
MonthlyFee
Short
Text
Short
Text
Currency
RegistrationFee
Currency
LevelName
12.
13.
Field
Size
4
Other
30
Caption: Level Name
Caption: Level ID
Caption: Monthly Fee
Decimal Places: 0
Caption: Registration Fee
Decimal Places: 0
Sandra exported her levels data to a text file, and she asks you to add this data
to the tblLevel table. Import the data as instructed below:
a.
Specify the file Support_NP_AC16_CS1-4a_Levels.txt, 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 tblLevel table.
c.
In the Import Text Wizard dialog boxes, choose the options to import
delimited data, to use a comma delimiter, and to import the data
into the tblLevel table. Do not save the import steps.
d.
Open the tblLevel table in Datasheet View, and then resize the columns to
best fit the data they contain.
e.
Save and close the tblLevel table.
Add the tblCoach, tblLevel, tblAthlete, and tblLocation tables, in that order, to
the Relationships window. Resize the field list for the tblAthlete table so all
fields are visible. Create the relationships in the database as instructed below:
a.
Define a one-to-many relationship between the primary tblLevel table and
the related tblCoach table using the LevelID field. Choose the options to
enforce referential integrity and to cascade update related fields.
b.
Define a one-to-many relationship between the primary tblLevel table and
the related tblAthlete table using the LevelID field. Choose the options to
enforce referential integrity and to cascade update related fields.
New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a
c.
Define a one-to-many relationship between the primary tblLocation table
and the related tblAthlete table using the LocationID field. Choose the
options to enforce referential integrity and to cascade update
related fields. Figure 1 below shows the completed Relationships
window. Save and close the Relationships window.
Figure 1: Relationships Window
d.
14.
15.
Sandra needs to be able to find information on athletes that qualify for the
children’s classes, so you decide to create a query to allow her to quickly locate
athletes born during or after 2011. Use the Simple Query Wizard to create a
query based on the tblAthlete table. Include all fields in the query, and
use qryAthletesBorn2011AndYounger as the title. Make the following
changes to the query:
a.
Add criteria to the BirthDate field to select records for athletes who were
born on or after 1/1/2011.
b.
Sort the records in ascending order by LastName.
c.
Hide the State field so that it does not appear in the query results. (Hint:
Make sure to hide this field in Design View, not in Datasheet View.)
d.
Save and run the query, and then close it.
Create a new query in Design View that is based on the tblLevel and tblAthlete
tables. Add the LevelName field from the tblLevel table to the query design.
Add the FirstName, LastName, BirthDate, and Gender fields, in that order, from
the tblAthlete table to the query design. Save the query with the name
qryLevelsAndAthletes, run the query, and then complete the following tasks
in Datasheet View:
a.
Sort the records by BirthDate, so the oldest athletes are listed first.
b.
Use Filter By Selection to select only those athletes who skate for the
Collegiate level (Hint: Use the Equals “Collegiate” option.) Save and
close the query.
New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a
16.
Create a new query in Design View that is based on the tblCoach and tblLevel
tables. Add the LevelName field from the tblLevel table to the query design.
Add the CoachFirst, CoachLast, BGCExp, and CertificationExp fields, in that
order, from the tblCoach table. Use qryCoachBGCExpirations to save query.
Add criteria to the BGCExp field to select records with BGCExp dates that occur
on or before 12/31/2019. Save and run the query, and then close it.
17.
In the Navigation Pane, copy the qryCoachBGCExpirations query, rename the
copied query qryCoach2019CertificationExpirations, and then add criteria
to only select records with a BGCExp date that occurs on or before
12/31/2019 and that contains a CertificationExp date that occurs on or
before 12/31/2019. (Hint: This query should only return records that meet
both of the query conditions. The criteria for the BGCExp date should already
be present in the copied query.) Save and run the query, and then close it.
18.
In the Navigation Pane, copy the qryCoach2019CertificationExpirations query,
rename the copied query qryCoach2019BGCOrCertificationExpirations, and
then edit the criteria to only select records with a BGCExp date that occurs on
or before 12/31/2019 or a record that contains a CertificationExp date that
occurs on or before 12/31/2019. (Hint: This query should return records
that meet one or more of the query conditions.) Save and run the query, and
then close it.
19.
Create a new query in Design View that is based on the tblAthlete and tblLevel
tables. Add the LevelName field from the tblLevel table to the query design.
Add the FirstName and LastName fields from the tblAthlete table. Add the
MonthlyFee field from the tblLevel table. Save the query with the name
qryAnnualDuesByAthlete, run the query, and then complete the following
tasks:
20.
a.
In Design View, add a calculated field named AnnualDues to the query
design in the fifth column of the design grid that determines the total
annual cost of skating dues for each athlete. The expression should
multiply the monthly fee amount by 12 months. Set the Caption property
Annual Dues for the calculated field.
b.
Save and run the query. Resize the Annual Dues column to best fit the
data it contains.
c.
Add the Total row to the query datasheet, and then use a function to
calculate the average monthly fee and the total annual dues.
d.
Save and close the query.
Use the Form Wizard to create a form based on the tblLevel table. Include all
fields in the form, use the Columnar layout, and name the form frmLevels.
Make the following changes to the form:
a.
In Layout view, change the form title to Levels, and then change the font
color of the form title to Dark Blue (4th column, 1st row of the Standard
Colors palette).
b.
Switch to Form view, and use the frmLevels form to enter a new record in
the tblLevel table with the Level ID TODD, the Level Name Toddler, a
Monthly Fee of $95, and a Registration Fee of $50. Save and close the
form.
New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a
21.
Use the Form Wizard to create a form containing a main form and a subform by
following the instructions below:
a.
Select all fields from the tblLevel table for the main form.
b.
Select the AthleteID, FirstName, and LastName fields from the tblAthlete
table.
c.
Choose the option to view the data by tblLevel.
d.
Select the Datasheet layout for the subform.
e.
Specify the titles frmLevelsWithAthletes for the main form and
frmAthleteSubform for the subform.
f.
Switch the form to Layout view and change the title in the main form to
Levels with Athletes, and then change the font color of the title to Dark
Blue (4th column, 1st row of the Standard Colors palette).
g.
Resize the Athlete ID, First Name, and Last Name columns in the subform
to best fit the data they contain, or a width of at least 0.5” for each
column. Resize the width of the subform to approximately 3.5” as shown
in Figure 2 on the following page.
h.
Use the navigation buttons to view each record in the main form, checking
to make sure that all data is displayed in the columns in the datasheet’s
subform. (Hint: The order of records in your subform may be different
than shown in Figure 2.) If necessary, resize the datasheet columns and
the subform to display the data so it is fully visible. When you are
finished, save and close the form.
New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a
Figure 2: frmLevelsWithAthletes Form
i.
22.
23.
You decide that a report showing a list of all the athletes would be useful. Use
the Report Wizard to create a report based on the primary tblLevel table and
the related tblAthlete table, as instructed below:
a.
Select the LevelName, MonthlyFee, and RegistrationFee fields from the
tblLevel table.
b.
Select the LocationID, FirstName, LastName, BirthDate, and Gender fields
from the tblAthlete table.
c.
View the data by tblLevel, and do not select any additional grouping levels
for the report.
d.
Sort the records in ascending order by LocationID.
e.
Select the Outline layout and Portrait orientation for the report.
f.
Use rptAthleteListing as the report title.
Switch the report to Layout View, and change the report title for the
rptAthleteListing report to Athlete Listing (two words).
New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a
24.
25.
You decide to modify the layout of the rptAthleteListing report to make it easier
to read. With the report still open in Layout View, complete the following steps:
a.
Resize the Level Name, Monthly Fee, and Registration Fee field labels by
using the mouse to drag their right edges, decreasing their widths so the
boxes are as wide as necessary to display their contents (approximately
1.25”).
b.
Resize the LevelName, MonthlyFee and RegistrationFee field value controls
by using the mouse to drag their right edges, decreasing their widths so
the boxes are as wide as their contents (approximately 1.2”).
c.
Right-align the contents of the LevelName, MonthlyFee, and
RegistrationFee field value controls.
With the rptAthleteListing report still open in Layout View, complete the
following steps:
a.
Resize the LocationID field label and field value controls by dragging their
left edges with the mouse to make all the contents visible (they should be
approximately 0.8” wide).
b.
Resize the Last Name field label and field value controls to approximately
1.5” wide by dragging their right edge with the mouse.
c.
Move the Birth Date and Gender field labels and field value controls to the
left to decrease the amount of space between the columns. The left edge
of the Birth Date label and controls should be located at approximately
4.5”, and the left edge of the Gender label and controls should be located
at approximately 5.6”. (Hint: The location of a control’s left edge can be
viewed in the Property Sheet.)
d.
Resize the control that contains the page number to approximately 1”
wide. Use an arrow key to move the page number control so its right edge
aligns with the right edge of the Gender control in the report.
Switch to Report View, and scroll through the report to confirm that all
values are visible. Save the report.
26.
Switch the rptAthleteListing report to Layout view, and use conditional
formatting to format birth dates that are greater than or equal to 1/1/2008
in a bold, Green font color (6th column, 7th row of the Standard Colors
palette). Display the report in Print Preview and confirm that it matches Figure
3 on the next page, and then save and close the report. (Hint: The records may
appear in a different order in your report.)
New Perspectives Access 2016 | Modules 1–4: SAM Capstone Project 1a
Figure 3: rptAthleteListing 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.
AthleteID
50443
50499
50744
51248
51249
51250
51320
51321
51396
51436
51623
52164
52316
53421
53460
53465
53526
54328
54785
54786
57154
57158
57925
58613
58632
58643
58842
59530
59800
59801
59842
59843
59874
50120
50121
50122
50432
50532
50632
50867
50986
54320
54382
57143
57332
57741
LevelID
BEG2
BEG2
BEG2
A2
A2
A2
A2
A2
CJR
A2
A2
COLL
COLL
COLL
COLL
COLL
COLL
A3
COLL
A3
A3
A3
A3
CINT
CINT
CINT
A1
A3
A3
A3
CINT
A3
A3
BEG1
BEG1
BEG1
BEG1
BEG1
BEG1
BEG1
BEG1
CINT
CINT
A1
A1
A1
LocationID
JUMP
JUMP
JUMP
CPSC
CPSC
CPSC
CPSC
CPSC
CPSC
CPSC
CPSC
CPSC
CPSC
CPSC
CPSC
CPSC
JUMP
JUMP
CPSC
JUMP
JUMP
JUMP
JUMP
JUMP
JUMP
JUMP
CPSC
JUMP
JUMP
JUMP
JUMP
JUMP
JUMP
SPIN
SPIN
SPIN
SPIN
SPIN
SPIN
SPIN
SPIN
SPIN
SPIN
SPIN
SPIN
SPIN
FirstName
Aiden
Lucas
Alex
Dylan
Joe
Angela
Ashley
Sophie
Mary
Mark
Brian
Sydney
Andrew
Hailey
Emily
Ryan
Andrew
Julia
Luke
Rachel
Isaiah
Justin
Leah
Grace
Benjamin
James
Evan
Elizabeth
David
Natalie
Dominic
Anna
Ryan
Tom
Chris
Angela
Michelle
Noah
Ryan
Nora
Ashley
Ian
Sean
Allison
Robert
Carter
LastName
Parker
Parker
Watley
Wilken
Petrilli
Petrilli
Mulroney
Popovitch
Chen
Chen
Chen
Taylor
Spencer
Peterson
Peterson
Peterson
Ellis
Levy
Thompson
Edwards
Metzger
Metzger
Norris
Browne
Vaughn
Myers
Lee
Reed
Hawkins
Hawkins
Sanchez
Rivera
Rivera
Burwell
Burwell
Burwell
Graham
Baker
Lew
Weston
Berman
McBrinn
McBrinn
Jenkins
Jenkins
McFadyen
Address
3104 Hamilton Blvd
3104 Hamilton Blvd
3104 Hamilton Blvd
1243 Tilghman St
515 N 18th St
515 N 18th St
1945 W Columbia St
2805 Lehigh St
2249 Walnut St
2249 Walnut St
2249 Walnut St
651 Union Blvd
1860 Catasauqua Rd
1826 Hanover Ave
1826 Hanover Ave
1826 Hanover Ave
1502 Chew St
845 W Linden St
921 Hamilton St
714 Grape St
2702 Walbert Ave
2702 Walbert Ave
2702 Walbert Ave
813 St John St
39 S 9th St
1433 W Allen S
2720 S Pike Ave
1234 MacArthur Rd
1190 Mickley Rd
1190 Mickley Rd
1658 Hausman Rd
4102 Chestnut St
4102 Chestnut St
2641 Myrtle St
2641 Myrtle St
2641 Myrtle St
2642 W 12th St
2642 W 12th St
2202 Ash St
4108 Peach St
2705 Elm St
3740 Peach St
3740 Peach St
3866 Peach St
3866 Peach St
1801 Liberty St
City
Allentown
Allentown
Allentown
Allentown
Allentown
Allentown
Allentown
Allentown
Allentown
Al …
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades