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 Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Erie Erie Erie Erie Erie Erie Erie Erie Erie Erie Erie Erie Erie State PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA 57743 50012 50013 50017 50021 50022 50023 50072 50762 50865 51740 51743 51766 51830 51873 53469 53865 54321 54732 54822 54865 54896 54988 55744 56431 56711 57321 57985 57988 57992 58366 58736 58825 59330 59774 59975 59998 50736 50774 50778 50876 50996 51234 52231 53468 53653 53681 A1 CJR CJR BEG3 BEG3 BEG3 BEG3 BEG3 BEG3 BEG2 A2 CJR CJR A2 BEG3 CJR CJR COLL CJR BEG3 BEG3 A1 BEG3 CJR A1 A1 COLL COLL COLL BEG3 CJR CINT BEG3 BEG3 CJR CJR CJR BEG2 A1 BEG2 BEG2 CINT CINT CSR CSR CSR CSR SPIN SFIT NESC NESC NESC NESC NESC NESC NESC SFIT NESC SFIT SFIT NESC NESC SFIT SFIT NESC SFIT NESC NESC NESC NESC SFIT NESC NESC NESC NESC NESC NESC SFIT NESC NESC NESC SFIT SFIT SFIT PMSC PMSC HASC PMSC PMSC PMSC PMSC HASC PMSC HASC Nicole Josh Sandra Mike Brian Corey Lauren James Jack Vinnie Pawan Ian Julia Louis Molly Michael Alyssa Kayla Michael Anna Carson Kate Angela Colin Andrew Brooke Liam Jim Patrick Michael Ryan Alexis Dylan Joshua Rachel Megan Jose Charlotte Emma David John Natalie Amelia Chris Alyssa Connor Zachary McFadyen Chapman Chapman Weis Corbett Corbett Corbett Whalen Haden Covelli Singh Wu Wu Dimaggio Sullivan Adams Tyson Simons Schmidt Koszalka Davis McCoy McGovern Heston Rodriguez Rodriguez Gladstone Kennard Kennard Nelsen Scott Boekel Benton Mabery Booker Davenport Fuentes Patel Livingstone Mahony Mosley McNabb Camp Hayward Barrow Dixon Gottlieb 1801 Liberty St 1150 Smallman St 1150 Smallman St 1411 Grandview Ave 301 Fifth Ave 301 Fifth Ave 301 Fifth Ave 1473 Banksville Rd 537 Suismon St 856 Western Ave 3525 Liberty Ave 500 Grant St 500 Grant St 510 Market St 2228 E Carson St 1 Boggs Ave 1100 Galveston Ave 3615 Butler St 337 N Shore Dr 19 S 27th St 321 Bigham St 247 N Shore Dr 308 Forbes Ave 917 Western Ave 123 Shiloh St 123 Shiloh St 2339 E Carson St 411 Cherry Way 411 Cherry Way 203 Brownsville Rd 333 Mansfield Ave 110 Beverly Rd 1208 Brighton Rd 16 Southern Ave 476 McMasters Way 476 McMasters Way 310 Ross St 244 Adams Ave 731 Pittston Ave 522 Moosic St 1829 Bundy St 427 Cedar Ave 917 Wyoming Ave 615 Luzerne St 126 Franklin Av 523 Linden St 1918 Ash St Erie Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA 53745 54833 57710 57864 57866 58012 58043 59762 59765 59784 CSR A1 A1 CSR A1 CSR CSR CSR CSR CSR PMSC HASC PMSC HASC PMSC PMSC PMSC HASC PMSC HASC Sarah Rob Jinyao Erin Eric Kaylee Joshua Sarah Allison Chris Palm Shaffer Yang Slater Weltz Knebels Phillips Coulter Kriby Swanson 969 Prescott Ave 315 Green Ridge St 1416 Mulberry St 222 Wyoming Ave 119 Jefferson Ave 1802 Cedar Ave 300 Spruce St 1210 Mulberry St 930 Capouse Ave 419 Poplar St Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton PA PA PA PA PA PA PA PA PA PA Zip 18103 18103 18103 18102 18104 18104 18104 18103 18104 18104 18104 18109 18109 18109 18109 18109 18102 18101 18101 18052 18104 18104 18104 18103 18102 18102 18103 18052 18052 18052 18104 18049 18049 16508 16508 16508 16505 16505 16503 16509 16504 16508 16508 16509 16509 16502 BirthDate 12/8/2013 10/14/2011 12/8/2013 2/29/2004 4/7/2011 8/4/2008 3/21/2002 2/28/2004 9/1/2011 8/3/2009 8/3/2009 6/5/2003 5/30/2002 5/31/2002 1/30/2003 5/3/2004 4/24/2002 8/3/2008 12/31/2007 7/13/2005 5/31/2009 4/13/2006 7/14/2008 4/24/2011 1/31/2010 12/12/2011 6/23/2008 12/10/2008 9/21/2008 4/24/2008 6/30/2009 10/30/2009 11/15/2007 5/3/2012 9/4/2011 10/14/2009 4/27/2012 5/14/2012 7/3/2008 12/7/2008 11/14/2008 5/3/2012 8/7/2011 2/28/2004 4/7/2007 6/14/2007 Gender M M M M M F F F F M M F M F F M F F M F M M F F M M M F M F M F M M M F F M M F F M M F M M 16502 15222 15222 15222 15222 15222 15222 15216 15212 15233 15201 15219 15219 15222 15203 15211 15233 15201 15212 15203 15211 15212 15222 15233 15211 15211 15203 15219 15219 15210 15220 15216 15233 15211 15222 15222 15219 18503 18505 18505 18508 18505 18505 18504 18503 18503 18510 1/21/2004 4/24/2010 4/24/2010 1/7/2009 11/7/2008 4/4/2006 6/14/2006 9/5/2007 3/7/2010 1/31/2012 7/4/2008 6/7/2012 10/23/2010 4/5/2009 3/6/2008 12/24/2009 12/1/2007 3/7/2005 11/3/2008 12/14/2005 7/3/2005 1/15/2004 12/14/2005 7/7/2008 2/27/2008 4/25/2006 5/8/2007 3/4/2008 3/4/2008 4/6/2006 6/7/2008 12/31/2009 7/18/2004 8/18/2004 4/25/2011 6/4/2009 5/25/2011 10/12/2012 1/7/2007 2/27/2009 3/24/2012 1/28/2012 2/26/2007 4/30/2008 2/15/2008 6/14/2006 9/22/2007 F M F M M F F M M M F M F M F M F F M F M F F M M F M M M M M F M M F F M F F M M F F M F M M 18510 18509 18510 18503 18503 18505 18503 18504 18509 18509 8/30/2008 3/7/2004 3/15/2007 8/30/2008 7/3/2009 8/6/2009 6/11/2011 8/31/2009 8/30/2008 5/7/2007 F M M F M F M F F M "BEG1","Beginner Level 1",$125.00,$100.00 "BEG2","Beginner Level 2",$150.00,$100.00 "BEG3","Beginner Level 3",$175.00,$175.00 "COLL","Collegiate",$105.00,$150.00 "A1","Adult Level 1",$120.00,$100.00 "A2","Adult Level 2",$120.00,$100.00 "A3","Adult Level 3",$120.00,$100.00 "CJR","Competitive Junior",$225.00,$220.00 "CINT","Competitive Intermediate",$255.00,$220.00 "CSR","Competitive Senior",$355.00,$220.00 "SPIN","Spin Skating Club" "JUMP","Jump High Skating Club" "SFIT","Skate Fit" "CPSC","Cedar Pines Skating Club" "PMSC","Penn Mountain Skating Club" "NESC","Northeastern Area Skating Club" "HASC","High Achievers Skating Club" 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 Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Allentown Erie Erie Erie Erie Erie Erie Erie Erie Erie Erie Erie Erie Erie State PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA 57743 50012 50013 50017 50021 50022 50023 50072 50762 50865 51740 51743 51766 51830 51873 53469 53865 54321 54732 54822 54865 54896 54988 55744 56431 56711 57321 57985 57988 57992 58366 58736 58825 59330 59774 59975 59998 50736 50774 50778 50876 50996 51234 52231 53468 53653 53681 A1 CJR CJR BEG3 BEG3 BEG3 BEG3 BEG3 BEG3 BEG2 A2 CJR CJR A2 BEG3 CJR CJR COLL CJR BEG3 BEG3 A1 BEG3 CJR A1 A1 COLL COLL COLL BEG3 CJR CINT BEG3 BEG3 CJR CJR CJR BEG2 A1 BEG2 BEG2 CINT CINT CSR CSR CSR CSR SPIN SFIT NESC NESC NESC NESC NESC NESC NESC SFIT NESC SFIT SFIT NESC NESC SFIT SFIT NESC SFIT NESC NESC NESC NESC SFIT NESC NESC NESC NESC NESC NESC SFIT NESC NESC NESC SFIT SFIT SFIT PMSC PMSC HASC PMSC PMSC PMSC PMSC HASC PMSC HASC Nicole Josh Sandra Mike Brian Corey Lauren James Jack Vinnie Pawan Ian Julia Louis Molly Michael Alyssa Kayla Michael Anna Carson Kate Angela Colin Andrew Brooke Liam Jim Patrick Michael Ryan Alexis Dylan Joshua Rachel Megan Jose Charlotte Emma David John Natalie Amelia Chris Alyssa Connor Zachary McFadyen Chapman Chapman Weis Corbett Corbett Corbett Whalen Haden Covelli Singh Wu Wu Dimaggio Sullivan Adams Tyson Simons Schmidt Koszalka Davis McCoy McGovern Heston Rodriguez Rodriguez Gladstone Kennard Kennard Nelsen Scott Boekel Benton Mabery Booker Davenport Fuentes Patel Livingstone Mahony Mosley McNabb Camp Hayward Barrow Dixon Gottlieb 1801 Liberty St 1150 Smallman St 1150 Smallman St 1411 Grandview Ave 301 Fifth Ave 301 Fifth Ave 301 Fifth Ave 1473 Banksville Rd 537 Suismon St 856 Western Ave 3525 Liberty Ave 500 Grant St 500 Grant St 510 Market St 2228 E Carson St 1 Boggs Ave 1100 Galveston Ave 3615 Butler St 337 N Shore Dr 19 S 27th St 321 Bigham St 247 N Shore Dr 308 Forbes Ave 917 Western Ave 123 Shiloh St 123 Shiloh St 2339 E Carson St 411 Cherry Way 411 Cherry Way 203 Brownsville Rd 333 Mansfield Ave 110 Beverly Rd 1208 Brighton Rd 16 Southern Ave 476 McMasters Way 476 McMasters Way 310 Ross St 244 Adams Ave 731 Pittston Ave 522 Moosic St 1829 Bundy St 427 Cedar Ave 917 Wyoming Ave 615 Luzerne St 126 Franklin Av 523 Linden St 1918 Ash St Erie Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA 53745 54833 57710 57864 57866 58012 58043 59762 59765 59784 CSR A1 A1 CSR A1 CSR CSR CSR CSR CSR PMSC HASC PMSC HASC PMSC PMSC PMSC HASC PMSC HASC Sarah Rob Jinyao Erin Eric Kaylee Joshua Sarah Allison Chris Palm Shaffer Yang Slater Weltz Knebels Phillips Coulter Kriby Swanson 969 Prescott Ave 315 Green Ridge St 1416 Mulberry St 222 Wyoming Ave 119 Jefferson Ave 1802 Cedar Ave 300 Spruce St 1210 Mulberry St 930 Capouse Ave 419 Poplar St Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton Scranton PA PA PA PA PA PA PA PA PA PA Zip 18103 18103 18103 18102 18104 18104 18104 18103 18104 18104 18104 18109 18109 18109 18109 18109 18102 18101 18101 18052 18104 18104 18104 18103 18102 18102 18103 18052 18052 18052 18104 18049 18049 16508 16508 16508 16505 16505 16503 16509 16504 16508 16508 16509 16509 16502 BirthDate 12/8/2013 10/14/2011 12/8/2013 2/29/2004 4/7/2011 8/4/2008 3/21/2002 2/28/2004 9/1/2011 8/3/2009 8/3/2009 6/5/2003 5/30/2002 5/31/2002 1/30/2003 5/3/2004 4/24/2002 8/3/2008 12/31/2007 7/13/2005 5/31/2009 4/13/2006 7/14/2008 4/24/2011 1/31/2010 12/12/2011 6/23/2008 12/10/2008 9/21/2008 4/24/2008 6/30/2009 10/30/2009 11/15/2007 5/3/2012 9/4/2011 10/14/2009 4/27/2012 5/14/2012 7/3/2008 12/7/2008 11/14/2008 5/3/2012 8/7/2011 2/28/2004 4/7/2007 6/14/2007 Gender M M M M M F F F F M M F M F F M F F M F M M F F M M M F M F M F M M M F F M M F F M M F M M 16502 15222 15222 15222 15222 15222 15222 15216 15212 15233 15201 15219 15219 15222 15203 15211 15233 15201 15212 15203 15211 15212 15222 15233 15211 15211 15203 15219 15219 15210 15220 15216 15233 15211 15222 15222 15219 18503 18505 18505 18508 18505 18505 18504 18503 18503 18510 1/21/2004 4/24/2010 4/24/2010 1/7/2009 11/7/2008 4/4/2006 6/14/2006 9/5/2007 3/7/2010 1/31/2012 7/4/2008 6/7/2012 10/23/2010 4/5/2009 3/6/2008 12/24/2009 12/1/2007 3/7/2005 11/3/2008 12/14/2005 7/3/2005 1/15/2004 12/14/2005 7/7/2008 2/27/2008 4/25/2006 5/8/2007 3/4/2008 3/4/2008 4/6/2006 6/7/2008 12/31/2009 7/18/2004 8/18/2004 4/25/2011 6/4/2009 5/25/2011 10/12/2012 1/7/2007 2/27/2009 3/24/2012 1/28/2012 2/26/2007 4/30/2008 2/15/2008 6/14/2006 9/22/2007 F M F M M F F M M M F M F M F M F F M F M F F M M F M M M M M F M M F F M F F M M F F M F M M 18510 18509 18510 18503 18503 18505 18503 18504 18509 18509 8/30/2008 3/7/2004 3/15/2007 8/30/2008 7/3/2009 8/6/2009 6/11/2011 8/31/2009 8/30/2008 5/7/2007 F M M F M F M F F M "BEG1","Beginner Level 1",$125.00,$100.00 "BEG2","Beginner Level 2",$150.00,$100.00 "BEG3","Beginner Level 3",$175.00,$175.00 "COLL","Collegiate",$105.00,$150.00 "A1","Adult Level 1",$120.00,$100.00 "A2","Adult Level 2",$120.00,$100.00 "A3","Adult Level 3",$120.00,$100.00 "CJR","Competitive Junior",$225.00,$220.00 "CINT","Competitive Intermediate",$255.00,$220.00 "CSR","Competitive Senior",$355.00,$220.00 "SPIN","Spin Skating Club" "JUMP","Jump High Skating Club" "SFIT","Skate Fit" "CPSC","Cedar Pines Skating Club" "PMSC","Penn Mountain Skating Club" "NESC","Northeastern Area Skating Club" "HASC","High Achievers Skating Club"
Purchase answer to see full attachment