New Perspectives Access 2013
Tutorials 1–4: SAM Capstone Project 1a
Northwestern Swimming
CREATING TABLES, QUERIES, FORMS, AND REPORTS IN A DATABASE
PROJECT DESCRIPTION
Northwestern Swimming (NWS) manages athlete and coach data for swim teams located throughout Washington. Athletes include both developmental swimmers who are learning to swim and athletes who have competed at the Olympic and world-championship levels. Julia Montoya is the general chairperson of NWS, and she has just received approval from the board of directors to replace NWS's manual system of managing data about athletes, coaches, and teams with a database. She asks you to help her to create objects in the database that she and other staff members can use to manage data about the different teams.
GETTING STARTED
· Download the following file from the SAM website:
o NP_Access2013_CS_T1-4_P1a_FirstLastName_1.accdb
· Open the file you just downloaded and save it with the name:
o NP_Access2013_CS_T1-4_P1a_FirstLastName_2.accdb
o 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.
· To complete this Project, you will also need to download and save the following support files from the SAM website:
o support_NP_A13_CS_T1-4_P1a_nws.accdb
o support_NP_A13_CS_T1-4_P1a_teams.txt
o support_NP_A13_CS_T1-4_P1a_practice.txt
o support_NP_A13_CS_T1-4_P1a_athletes.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
1. Open the Coach table in Design View and make the following changes:
a. Move the GroupID field to follow the CoachID field.
b. Delete the CertificationLevel field from the table.
c. Save the table.
2. Switch to Datasheet View, enter the records shown in Table 1 below into the Coach table, and then close the table.
Table 1: Coach Table
| Coach ID | Group ID | Coach First Name | Coach Last Name | BGC Expiration | Certification Expiration |
| 901900 | DEV2 | Janice | Buckingham | 12/31/2017 | 12/31/2017 |
| 901901 | DEV1 | Whitney | Long | 12/31/2017 | 12/31/2017 |
3. Create a new table in Datasheet View. Save the table as Athlete, 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: TeamID, FirstName, LastName, Address, City, State, Zip, GroupID, and Gender.
c. Save the table.
4. Switch to Design View for the Athlete table, and then set the field properties shown in Table 2 below. The AthleteID field should be set as the primary key for the table.
Table 2: Athlete Table
| Field Name | Data Type | Description | Field Size | Other |
| AthleteID | Short Text | Primary key | 5 | Caption: Athlete ID |
| TeamID | Short Text | Foreign key | 4 | Caption: Team 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: WA |
| Zip | Short Text |
| 10 |
|
| GroupID | Short Text | Foreign key | 4 | Caption: Group ID |
| Gender | Short Text | M, F | 1 |
|
5. Add a new field to the Athlete table 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 GroupID field so it follows the AthleteID field. Save and close the table.
7. Use the Import Spreadsheet Wizard to add data to the Athlete table from an Excel spreadsheet as follows:
a. Specify support_NP_A13_CS_T1-4_P1a_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 Athlete table.
c. In the Import Spreadsheet Wizard dialog boxes, choose the Athlete worksheet, and import to the Athlete table. Do not save the import steps.
d. Open the Athlete table in Datasheet View, and then resize each column to best fit the data it contains.
e. Save and close the Athlete table.
8. Import the structure of the Team table in the database support_NP_A13_CS_T1-4_P1a_nws.accdb, available for download from the SAM website, into the current database. Do not save the import steps.
9. Open the Team table in Design View, and then update the field properties shown in Table 3 below. Save and close the Team table after you finish updating the field properties. (Hint: The TeamID field should be the primary key for the table.)
Table 3: Team Table
| Field Name | Data Type | Description | Field Size | Other |
| TeamID | Short Text | Primary key | 4 | Caption: Team ID |
| TeamName | Short Text |
| 50 | Caption: Team Name |
10. Julia exported her existing team data to a text file, and she asks you to add this data to the Team table. Import the data as instructed below:
a. Specify support_NP_A13_CS_T1-4_P1a_teams.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 Team 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 Team table. Do not save the import steps.
d. Open the Team table in Datasheet View, and then resize the columns to best fit the data they contain.
e. Save and close the Team table.
11. Create a new table in Design View, and then set the field properties shown in Table 4 below. The GroupID field should be the primary key for the table. Save the table as Practice, and then close the table.
Table 4: Practice Table
| Field Name | Data Type | Description | Field Size | Other |
| GroupID | Short Text | Primary key | 4 | Caption: Group ID |
| GroupName | Short Text |
| 30 | Caption: Group Name |
| MonthlyFee | Currency |
|
| Caption: Monthly Fee Decimal Places: 0 |
| RegistrationFee | Currency |
|
| Caption: Registration Fee Decimal Places: 0 |
12. Julia exported her practice group data to a text file, and she asks you to add this data to the Practice table. Import the data as instructed below:
a. Specify support_NP_A13_CS_T1-4_P1a_practice.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 Practice 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 Practice table. Do not save the import steps.
d. Open the Practice table in Datasheet View, and then resize the columns to best fit the data they contain.
e. Save and close the Practice table.
13. Add the Coach, Practice, Athlete, and Team tables, in that order, to the Relationships window. Resize the field list for the Athlete table so all fields are visible in the field list. Create the relationships in the database as instructed below:
a. Define a one-to-many relationship between the primary Practice table and the related Coach table using the GroupID field. Choose the options to enforce referential integrity and to cascade updates to related fields.
b. Define a one-to-many relationship between the primary Practice table and the related Athlete table using the GroupID field. Choose the options to enforce referential integrity and to cascade updates to related fields.
c. Define a one-to-many relationship between the primary Team table and the related Athlete table using the TeamID 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
d.
e.
f.
| Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2014 Cengage Learning. All Rights Reserved. |
14. Use the Simple Query Wizard to create a query based on the Athlete table. Include all fields in the query, and use the title AthletesYoungerThan2008. Make the following changes to the query:
a. Add a condition to the BirthDate field to select records for athletes who were born on or after 1/1/2008.
b. Sort the records in ascending order by LastName.
c. Change the BirthDate field so it remains in the query design but does not appear in the query results.
d. Save and run the query, and then close it.
15. Create a new query in Design View that is based on the Practice and Athlete tables. Add the GroupName field from the Practice table to the query design. Add the FirstName, LastName, BirthDate, and Gender fields, in that order, from the Athlete table to the query design. Save the query as GroupsAndAthletes, 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 swim for any Senior group. (Hint: Use the Begins with Senior option.) Save and close the query.
16. Create a new query in Design View that is based on the Coach and Practice tables. Add the GroupName field from the Practice table to the query design. Add the CoachFirst, CoachLast, BGCExp, and CertificationExp fields, in that order, from the Coach table. Save the query as CoachBGCExpirations. Add a condition to the BGCExp field to select records with BGCExp dates that occur on or before 12/31/2016. Save and run the query, and then close it.
17. In the Navigation Pane, copy the CoachBGCExpirations query, rename the copied query as Coach2016CertificationExpirations, and then change the query design to select a record with a BGCExp date that occurs on or before 12/31/2016 and that contains a CertificationExp date that occurs on or before 12/31/2016. (Hint: This query should only return records that meet both of the query conditions.) Save and run the query, and then close it.
18. In the Navigation Pane, copy the Coach2016CertificationExpirations query, rename the copied query as Coach2016BGCOrCertificationExpirations, and then change the query design to select a record with a BGCExp date that occurs on or before 12/31/2016 or a record that contains a CertificationExp date that occurs on or before 12/31/2016. (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 Athlete and Practice tables. Add the GroupName field from the Practice table to the query design. Add the FirstName and LastName fields from the Athlete table. Add the MonthlyFee field from the Practice table. Save the query as AnnualDuesByAthlete, run the query, and then complete the following tasks:
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 swimming dues for each athlete. The expression should multiply the monthly dues amount by 12 months. Set the Caption property for the calculated field to Annual Dues.
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.
20. Use the Form Wizard to create a form based on the Practice table. Include all fields in the form, use the Columnar layout, and name the form PracticeGroups. Make the following changes to the form:
a. Change the form title to Practice Groups, and then change the font color of the form title to Dark Blue (4th column, 1st row in the Standard Colors palette).
b. Use the Practice Groups form to enter a new record in the Practice table with the Group ID FISH, the Group Name Fish, a Monthly Fee of $95, and a Registration Fee of $50. Save and close the form.
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 Practice table for the main form.
b. Select the AthleteID, FirstName, and LastName fields from the Athlete table.
c. Choose the option to view the data by practice.
d. Select the Datasheet layout for the subform.
e. Specify the titles GroupsWithAthletes for the main form and AthleteSubform for the subform.
f. Change the title in the main form to Groups with Athletes, and then change the font color of the title to Dark Blue (4th column, 1st row in 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. Resize the width of the subform as shown in Figure 2 below.
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. 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.
Figure 2: GroupsWithAthletes Form
i.
22. Use the Report Wizard to create a report based on the primary Practice table and the related Athlete table, as instructed below:
a. Select the GroupName, MonthlyFee, and RegistrationFee fields from the Practice table.
b. Select the TeamID, FirstName, LastName, BirthDate, and Gender fields from the Athlete table.
c. View the data by practice and do not select any additional grouping levels for the report.
d. Sort the details records in ascending order by TeamID.
e. Select the Outline layout and Portrait orientation for the report.
f. Specify the report title AthleteListing.
23. Apply the Slice theme to the AthleteListing report only. Change the report title for the AthleteListing report to Athlete Listing (two words).
24. Resize and reposition the following objects in the Athlete Listing report in Layout View, and then scroll through the report to make sure all field labels and field values are fully displayed. Refer to Figure 3 on the following page as you complete your work.
a. Resize the Group Name, Monthly Fee, and Registration Fee field label boxes, decreasing their widths so that the field value boxes are as wide as necessary to display the values they contain.
b. Resize the Monthly Fee and Registration Fee field value boxes, decreasing their widths so that the field value boxes are as wide as the values they contain.
c. Move the Group Name, Monthly Fee, and Registration Fee field value boxes to the left, so they are closer to their associated field label boxes.
d. Resize the Last Name column on its right side, decreasing its width so that the field value box is as wide as the values it contains.
e. Resize the Gender field label box on its right side to increase its width so the column heading is completely displayed.
f. Move the Birth Date and Gender columns to the left, as shown in Figure 3, to decrease the amount of space between the columns.
g. Scroll to the bottom of the report, and then resize the control that contains the page number on its right side to decrease its width so that it is only as wide as the "Page 1 of 1" value it contains. Use an arrow key to move the page number control so that its right edge aligns with the right edge of the Gender column in the report.
Save the report.
Figure 3: AthleteListing Report
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
25. Use conditional formatting in the AthleteListing report to format birth dates that are equal to or earlier than 12/31/2005 in a bold, Red font (2nd column, last row in the Standard Colors palette), as shown in Figure 4 on the following page. Display the report in Print Preview and review its pages, and then save and close the report.
Figure 4: AthleteListing Report with Conditional Formatting
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
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.
..............
There are 25 steps in this assignment.
You need to update name and other details in _Gradinginfo table.
