Assignment Lab 4 (Module 5) Use the skills you learned to work in a database for a mall information desk. Data Files needed for this Case Problem: Ashbrook.accdb Ashbrook Mall Information Desk The Mall Operations Office is responsible for everything that happens in the Ashbrook Mall. To maintain a catalog of job openings at the mall stores and to track maintenance work done at the mall, Sam Bullard, director of the Mall Operations Office, has created the Ashbrook database. Youll help Sam create several new queries and make design changes to the tables. Complete the following steps:

1. Open the Ashbrook database, and then click the Enable Content button next to the Security Warning, if necessary.

2. Modify the first record in the tblStore table datasheet by changing the Store Contact to your name. Close the table.

3. Create a query to find all records in the tblStore table in which the Location field value starts with the letter B. Display all fields in the query recordset, and sort in ascending order by StoreID. Save the query as qryBLocations, run the query, and then close it.

4. Create a query to find all records in the tblPosition table in which the PositionDesc field value is Clerk, Salesclerk, or Stock Clerk. Use a list-of-values match for the selection criteria. Display all fields in the query recordset, and sort in ascending order by PositionID. Save the query as qryClerkPositions, run the query, and then close it.

5. Make a copy of the qryClerkPositions using the new name qryNonClerkPositions. Modify the new query to find all records in which in the tblPosition table in which the PositionDesc field values are not Clerk, Salesclerk, or Stock Clerk. Save and run the query, and then close it.

6. Create a parameter query to select the tblPosition table records for a PositionDesc field value that the user specifies. If the user doesnt enter a PositionDesc field value, select all records from the table. Display all fields from the table in the query recordset, sorting in ascending order by PositionID. Save the query as qryPositionParameter. Run the query and enter no value as the PositionDesc field value, and then run the query again and enter Clerk as the PositionDesc field value. Close the query.

7. Create a find duplicates query based on the tblMaintenanceJob table. Select JobLocation as the field that might contain duplicates, and select the JobReportedDate, JobStatus, JobCompletedDate, and JobDesc fields as additional fields in the query recordset. Save the query as qryDuplicateJobLocations, run the query, and then close it.

8. Create a find unmatched query that finds all records in the tblStore table for which there is no matching record in the tblPosition table. Display all fields from the tblStore table in the query recordset. Save the query as qryStoresWithoutMatchingPositions, run the query, and then close it.

9. Create a crosstab query based on the qryStorePositions query. Use the PositionDesc field values for the row headings, the Location field values for the column headings, the count of the LocationName field values as the summarized value, and include row sums. Save the query as qryLocationByPositionCrosstab. Change the column heading for the sum column to Total Number of Positions. Resize the columns in the query recordset to their best fit, and then save and close the query.

10. In the tblMaintenanceJobRepairer table, change the RepairerID field to a lookup field. Select the RepairerName field and then the RepairerID field from the tblRepairer table, sort in ascending order by the RepairerName field, do not hide the key column, make sure the Repairer Name column is the leftmost column, resize the lookup columns to their best fit, select RepairerID as the field to store in the table, and accept the default label for the lookup column. View the tblMaintenanceJobRepairer table datasheet, resize the Repairer ID column to its best fit, test the lookup field without changing a value permanently, and then save and close the table.

11. Define a field validation rule for the JobStatus field in the tblMaintenanceJob table. Acceptable field values for the JobStatus field are A, C, and O. Use the message Job status values must be A, C, or O to notify a user who enters an invalid JobStatus field value. Save your table changes, test the field validation rule for the JobStatus field, making sure any tested field values are the same as they were before your testing, and then close the table.

12. Add a Long Text field named StoreComments as the last field in the tblStore table. Set the Caption property to Store Comments and the Text Format property to Rich Text. In the table datasheet, resize the new column to its best fit, and then add your city and state in bold, italic font to the Store Comments column in the first record. Save and close the tblStore table datasheet.

13. Close the Ashbrook database without exiting Access, make a backup copy of the database, and compact and repair and then close the Ashbrook database. Submit the Ashbrook database to Canvas. (Do not zip the file.)