Create a relationship between the aCCap Guides table and the aCCap Customers table using the Guide ID field as the common field. Enforce Referential Integrity, and Cascade Update Related Records. Close the relationship window, saving changes Design a query to display the customer's First Name, Last Name, Guide ID, and the Availability for all records with a Hobby of Fishing or Hunting. Add the Guide ID field from the aCCap Customers table. Do not display the Hobby field in the results. Run the query, save the query as aCCap OR Query. Close the query. Design a query to display the average Retail Price of the inventory by category. Run the query. Caption the Average column Average Price. Run the query. Apply Best Fit to both columns. Save the query as aCCap Average Query. Close the query. Design a query to update the Retail Price for all Jackets to reflect a 10% discount The new retail price will be 90% of the original price. Save the query as aCCap Update Query. Run the query only once. Close the query. Create a crosstab query using the Query Wizard; select the aCCap Inventory table for the basis. of the crosstab query. Display Sport as the row heading, Category as the column heading, and a count of Item ID. Do not display row sums. Save the query as aCCap Crosstab Query. Close the query. Create a form in Design view based on the aCCap Guides table. In the Detail section, add a tab control at the 0.25-inch mark on the vertical and horizontal rulers. Extend the right edge of the tab control to the 5-inch mark on the horizontal ruler. To the first page of the tab control, add the caption Contact Information. From the Field List, add the First Name, Last Name, Department, Phone, and Email Address fields at the 1.5- inch mark on the horizontal ruler and .75-inch mark on the vertical ruler. Apply a Stacked Layout, and then resize the text boxes so the right edge aligns with the 3.5-inch mark on the horizontal ruler. To the second page of the tab control, add a caption of Availability. Add the Availability and Overnight fields at the 1.5-inch mark on the horizontal ruler and .75-inch mark on the vertical ruler. Apply a Stacked Layout. Start Access, Download and open the file named Student Access aCCap Adventure, Club.accdb Open the aCCap Guides table in Design view. Add a new field between the Last Name and Availability fields, name it Department. Use the Lookup Wizard to create a list that includes four options: Biking, Fishing, Hiking, and Hunting. 4:16 Instructions In the Availability field, set the Validation Rule to WD or WE or ALL and the Validation Text to Entry must be WD for weekdays, WE for weekends, or ALL for both (no period). Add a new field at the bottom of the field list. Name the field Overnight and select a data type that will allow for a yes or no entry. Save the table. Switch to Datasheet view, and update the two new fields for each guide, apply Best Fit to all columns, and then close the table, saving changes. Guide Department Overnight ID GD122 Hunting GD136 Fishing GD148 Biking GD155 Fishing GD167 Hiking GD170 Hunting Yes Yes No No No Yes -2-04