Open the School database located in the Access2 > Case1 folder provided with your Data Files.

2. Modify the first record in the tblStudent table datasheet by changing the First Name and Last Name column values to your first and last names, if necessary. Close the table.

3. Create a query to find all records in the tblStudent table in which the LastName field begins with H. Display the FirstName, LastName, City, and Phone fields in the query recordset, and sort in ascending order by LastName. Save the query as qryLastNameH, run the query, and then close it.

4. Create a query that finds all records in the tblCourse table with a Title value of Computer Science or Data Science. Use a list-of-values criterion and include the fields Title, StartDate, and HoursPerWeek in the query recordset, sorted in ascending order by StartDate. Save the query as qryCompOrDataSci, run the query, and then close it.

5. Create a query that finds all records in the tblStudent table in which the City field value is not equal to Denver. Display the FirstName, LastName, City, and Email fields in the query recordset, and sort in ascending order by City. Save the query as qryNonDenver, run the query, and then close it.

6. Create a query to display the InstanceID, TotalCost, and BalanceDue fields from the tblRegistration table and the Phone and Email fields from the tblStudent table. Find all records for which the BalanceDue value is greater than 0. Add a calculated field named Payer as the first column that concatenates FirstName, a space, LastName, and (student) if the BillingLastName field is null. Otherwise, the calculated field should concatenate BillingFirstName, a space, BillingLastName, and (billing). Sort the results on the calculated field in ascending order. Save the query as qryBalanceContacts, run the query, resize all columns to their best fit, and then save and close the query.

7. Create a parameter query to select the tblStudent table records for a City field value that the user specifies, using Enter the city: as the prompt text. If the user doesnt enter a City field value, select all records from the table. Display all fields from the tblStudent table in the query recordset. Save the query as qryStudentCityParameter. Run the query and enter no value as the City field value, and then run the query again and enter Littleton as the City field value. Close the query.

8. Create a find duplicates query based on the tblRegistration table. Select StudentID as the field that might contain duplicates, and select all other fields in the table as additional fields in the query recordset. Save the query as qryDuplicateStudentRegistrations, run the query, and then close it. Because the tblRegistration contains one student registered for two different courses, running this query should show that two records are found containing a duplicate StudentID.

9. Create a find unmatched query that finds all records in the tblStudent table for which there is no matching record in the tblRegistration table. Select the FirstName, LastName, Phone, and Email fields from the tblStudents table. Save the query as qryUnregisteredStudents, run the query, and then close it. Running this query should find five unmatched records.

10. Create a new query based on the tblStudent and tblRegistration tables. In the query recordset, display the FirstName, LastName, BillingFirstName, BillingLastName, Phone, and Email fields from the tblStudent table and the TotalCost and BalanceDue fields from the tblRegistration table. Sort in descending order by the BalanceDue field, and then use the Top Values property to select the top 5% of records. Save the query as qryTopOutstandingBalances, run the query, and then close it.

11. Use the Input Mask Wizard to add an input mask to the Phone field in the tblStudent table. The input mask should use periods as separators, as in 987.654.3210, with only the last seven digits required. Do not store the literal display characters if you are asked to do so, and apply the updated image mask everywhere it is used within the database. Resize the Phone column to its best fit, and then test the input mask by typing over an existing Phone column value, being certain not to change the value by pressing ESC after you type the last digit in the Phone column. Save and close the table.

12. Create a crosstab query based on the tblCourse table. Specify the HoursPerWeek values as the row headings and the Title field values as the column headings, calculate the InstanceID count, and include row sums. Save the query as qryFullPartTimeCrosstab, view the results, resize the columns as necessary, then save and close the query.

13. In the tblRegistration table, change the InstanceID field data type to Lookup Wizard. Select the Title, StartDate, and HoursPerWeek fields from the tblCourse table, sort in ascending order by Title, do not show the key column, resize the lookup columns to their best fit, select InstanceID as the field to store in the table, and accept the default label for the lookup column. View the tblRegistration datasheet, resize the InstanceID column to its best fit, test the lookup field without changing a field value permanently, and then close the table.

14. Define a field validation rule for the HoursPerWeek field in the tblCourse table. Acceptable field values for the HoursPerWeek field are values less than or equal to 40. Enter the message Hours per week cannot be greater than 40 so it appears if a user enters an invalid HoursPerWeek field value. Save your table changes, and then test the field validation rule for the HoursPerWeek field; be certain the field values are the same as they were before your testing, and then close the table.

15. Define a table validation rule for the tblCourse table to verify that StartDate field values precede EndDate field values in time. Use The course start date must come before the course end date as the validation message. Save your table changes, and then test the table validation rule, making sure any tested field values are the same as they were before your testing.

16. Designate the Access2 > Case1 folder as a trusted folder. (Note: Check with your instructor before adding a new trusted location.)

17. Make a backup copy of the database, compact and repair the database, and then close it.