Stanley EcoTours Janice and Bill Stanley need your help to maintain and analyze data about the clients, reservations, and tours for their ecotourism business. Additionally, you’ll troubleshoot some problems in another database containing tour information. Complete the following:
1. Open the Stanley database you created and worked with in Tutorials 1 and 2, and then click the Enable Content button next to the security warning, if necessary. 2. In the Guest table, change the phone number for Paul Barry to 406-497-1068, and then close the table. 3. Create a query based on the Tour table that includes the TourName, Location, Country, PricePerPerson, and TourType fields, in that order. Sort in ascending order based on the PricePerPerson field values. Save the query as ToursByPrice, and then run the query. 4. Use the ToursByPrice query datasheet to display the total Price Per Person for the tours. Save and close the query.
5. Create a query that displays the GuestLast, City, and State/Prov fields from the Guest table, and the ReservationID, StartDate, and People fields from the Reservation table. Save the query as GuestTourDates, and then run the query. Change the alternate row color in the query datasheet to the Theme Color Green, Accent 6, Lighter 40%. In Datasheet view, use an AutoFilter to sort the query results from oldest to newest Start Date. Save and close the query.
6. Create a query that displays the GuestFirst, GuestLast, City, ReservationID, TourID, and StartDate fields for all guests from Montana (MT). Do not include the State/Prov field in the query results. Sort the query in ascending order by the guest’s last name. Save the query as MontanaGuests and then run it. Close the query.
7. Create a query that displays data from all three tables in the database, as follows: the GuestLast, City, State/Prov, and Country fields from the Guest table; the StartDate field from the Reservation table; and the TourName, Location, and TourType fields from the Tour table. Specify that the query select only those records for guests from Canada or guests who are taking Jeep tours. Sort the query in ascending order by Location. Save the query as CanadaOrJeep, and then run the query. Resize datasheet columns to their best fit, as necessary, and then save and close the query.
8. Copy and paste the CanadaOrJeep query to create a new query named IdahoAndJuly.
9. Modify the IdahoAndJuly query to select all guests from Idaho who are taking a tour starting sometime in the month of July 2016. Do not include the State/Prov field values or the Country field values in the query results. Run the query. Resize datasheet columns to their best fit, as necessary, and then save and close the query.
10. Create a query that displays the ReservationID, StartDate, and People fields from the Reservation table, and the TourName, Location, Country, PricePerPerson, and SingleSupplement fields from the Tour table for all reservations with a People field value of 1. Save the query as SingleReservations. Add a field to the query named TotalCost that displays the results of adding the SingleSupplement field values to the PricePerPerson field values. Set the Caption property Total Cost for the calculated field. Display the results in descending order by TotalCost. Do not include the People field values in the query results. Run the query. Modify the query by formatting the TotalCost field to show 0 decimal places. Run the query, resize datasheet columns to their best fit, as necessary, and then save and close the query.
11. Create a query based on the Tour table that determines the minimum, average, and maximum price per person for all tours. Then complete the following: a. Specify field names of LowestPrice, AveragePrice, and HighestPrice. b. Set the Caption property for each field to include a space between the two words in the field name. c. Save the query as PriceStatistics, and then run the query. d. In Design view, specify the Standard format and two decimal places for each column. e. Run the query, resize all the datasheet columns to their best fit, save your changes, and then close the query. f. Create a copy of the PriceStatistics query named PriceStatisticsByTourType. g. Modify the PriceStatisticsByTourType query to display the price statistics grouped by TourType, with TourType appearing as the first field. Save your changes and then run and close the query.
12. Compact and repair the Stanley database, and then close it.
13. Open the Tour database located in the Access1 Case4 folder provided with your Data Files, and then click the Enable Content button next to the security warning, if necessary. Run the BookingByDateAndState query in the Tour database. The query is not producing the desired results. Fix the query so that the data from the Booking table is listed first, the data is sorted only by StartDate in ascending order, and the results do not display country values. Save and close the corrected query.
14. Run the WYGuestsFewerPeople query, which displays no records in the results. This query is supposed to show data for guests from Wyoming with fewer than four people in their booking. Find and correct the error in the query design, run the query, and then close it. 15. Run the CanadaOrSeptStart query. This query should display the records for all guests who are from Canada or whose booking start date is on or after 9/1/2016. Find and correct the errors in the query design, run the query, and then close it. Compact and repair the Tour database, and then close it.
