Ridgedale College MODIFYING TABLES AND QUERIES GETTING STARTED Open the file NP_AC19_5b_FirstLastName_1.accdb, available for download from the SAM website. Save the file as NP_AC19_5b_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. 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. You work in the Admissions Office at Ridgedale College to help track grants awarded to professors as well as teaching assistant assignments by class. You have created an Access database to organize these details. In this project, you will improve the tables and queries of the database. In Design View of the TeachingAssistants table, add three new fields with the following specifications: a. A field named TAEmail with a Hyperlink data type. b. A field named TAPhoto with an Attachment data type. c. A field named Evaluation with a Long Text data type. d. For the Evaluation field, use Yes as the value for the Append Only property value. Save the TeachingAssistants table. 2. While still in Design View of the TeachingAssistants table, modify the following field properties: a. Enter First Name as the Caption property for the TAFirstName field and Last Name as the Caption property for the TALastName field. b. Enter 25 as the Field Size property for the TAFirstName and TALastName fields. c. Choose Medium Date as the Format property for the StartDate and EndDate fields. Save the TeachingAssistants table. If prompted that some data may be lost, click Yes to continue. (Hint: No data is lost because no values exceed 25 characters in the TAFirstName or TALastName fields.) 3. While still in Design View of the TeachingAssistants table, modify the following field properties: a. Choose Yes as the Required property for the Stipend field. b. Enter >=1000 as the Validation Rule property for the Stipend field. c. Enter Minimum is $1000 for the Validation Text property for the Stipend field, and then save the TeachingAssistants table. If prompted to test data integrity rules, click Yes. 4. While still in Design View of the TeachingAssistants table, add a new field immediately below the existing fields using the Lookup Wizard and the following information: a. Get the values from the Degrees table. b. Select the DegreeAbbreviation and DegreeName fields. c. Sort the records in ascending order by the DegreeAbbreviation field. d. Hide the key column. e. Enable data integrity, use the other default options of the Lookup Wizard, and use Degree as the label for the lookup field. Save the TeachingAssistants table as prompted. f. Use Yes for the Column Heads Lookup property for new Degree field, 0";1";2" for the Column Widths property, and 3" for the List Width property. Save the TeachingAssistants table. (Hint: If a warning message appears about the TeachingAssistants table having changed since it was last opened, click Yes.) 5. Switch to Datasheet View of the TeachingAssistants table, and select MBA (Master of Business Administration) as the Degree value for the first record (TAID 2 Lisa Lang). 6. Switch to Design View of the TeachingAssistants table and modify these table properties: a. Add [StartDate]<=[EndDate] as the Validation Rule for the table. b. Enter StartDate must be earlier than the EndDate as the Validation Text property for the table. Save the TeachingAssistants table. Click Yes if prompted to test for data integrity. 7. Switch to Datasheet View of the TeachingAssistants table, add a Total row to the datasheet, and sum the value in the Stipend field. Save and close the TeachingAssistants table. 8. In Design View of the GrantsByDepartment query, complete the following modifications: a. Enter Department as the Caption property to the DepartmentName field. b. Add the Total row to the query grid, group by the DepartmentName field, count the GrantName field, and sum the Award field. c. Save and view the query in Datasheet View, and then widen each of the three columns to 22. d. Add a Total row to the datasheet and sum the CountofGrantName and SumOfAward fields as shown in Figure 1. Save and close the GrantsByDepartment query. Figure 1: Final GrantsByDepartment Query in Datasheet View 9. Create a new query in Query Design View based on the Professors table with the following details: a. Add the ProfLastName and Salary fields in that order. b. Sort the records in descending order on the Salary field. c. Return the top 3 records. d. Add >[Enter minimum salary] as a parameter prompt for the Salary field. e. Save the query and use ProfSalary as the query name. f. Run the query using 70000 as the parameter prompt to match the results shown in Figure 2. Close the ProfSalary query. Figure 2: Final ProfSalary Query in Datasheet View 10. Create a new parameter query in Design View based on the Professors table with the following details: a. Add the ProfID, ProfFirstName, ProfLastName, and Department fields to the query grid in that order. b. Right-click the LastName criteria box, and then open the zoom dialog box. Enter the following in the zoom dialog box: Like [Enter Professor's Last Name] & "*" c. Close the Zoom dialog box by clicking OK. Save the query using ProfessorParameter as the name and run the query to test it. Enter L in the parameter prompt and note that two records are returned. Close the query. 11. Create a crosstab query based on the CreditsByDepartment query using the Query Wizard with the following options: a. Add the Department field to the Row Heading field. b. Add the DeliveryMethod field as the Column Heading field. c. Sum the Credits field as the Value field. Don't include row sums. Save the query with the name CreditsByDepartment_Crosstab and close the query. 12. Create a new query in Query Design View based on the Sections table with the following details: a. Add the Semester, SchoolYear, SectionNo and DeliveryMethod fields in that order. b. Add the Total row to the query grid and count the SectionNo field. c. Use the 'Not' keyword to add criteria to the DeliveryMethod field to return sections that are not hybrid courses. d. Save the query and use NonHybridSections as the query name. e. Run the query. Close the NonHybridSections query. 13. Create a new query in Query Design View with the following details: a. Add the ClassMasterList, Sections, and Professors tables. b. Add the Department field from the Professors table and the SectionNo, ClassNo, Semester, SchoolYear, ProfID, MeetingDay, and Time fields from the Sections table in that order. c. Set the criteria for the Department field (using the 'in' keyword) to only include the software codes CIS, ENGR. d. Using the expression builder, create a new field named IsFourCredit. e. Within the expression builder, use the iif function to populate the IsFourCredit field with 'True' if the course is worth four credits and 'False' if not. f. Save the query as FourCreditCISandENGRCourses and run the query. Close the FourCreditCISandENGRCourses query. 14. Use the Find Unmatched Query Wizard to find records in the TeachingAssistants table that contain no related records in the Sections table using the following details: a. The TAID field in each table contains matching data. b. Include the TAID, TAFirstName, and TALastName fields in the query results. c. Use TAsWithoutSections as the query name, and view the results in Datasheet View as shown in Figure 3. Figure 3: Final TAsWithoutSections Query in Datasheet View 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