Computer Science Homework Help

Guided Project 3-2

San Diego Sailing Club wants to create three queries. To ensure consistency, the starting file is provided for you. Use Design view to create, edit, add aggregate functions, and add criteria to a query to find the total dollar value of the rentals for each boat in its fleet. After saving and testing the query, create a second query that uses aggregate functions and a parameter. Finally, create a query to find which boat types have been rented.

[Student Learning Outcomes 3.2, 3.3, 3.4, 3.5, 3.6, 3.8]

File Needed: SanDiegoSailing-03.accdb (Available from the Start File link.)

Completed Project File Name: [First Name.Last Name]-SanDiegoSailing-03.accdb

Skills Covered in This Project

• Create a query using Design view.
• Add fields to a query.
• Add criteria to a query.
• Execute a query.
• Save and test a query.
• Save a copy of a query.
• Add a parameter.
• Use aggregate functions.
• Use the Unique Values property.

This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead.

• Open the SanDiegoSailing-03.accdb database start file.
• The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.
• Enable content in the database.
• Create a new summary query in Design view. The Sailing Club wants to find out the total dollar value of the full day rentals, by boat, from the boats that have been rented. If a boat has been rented, a record displays in the SDRentals table.
  • Click the Query Design button [Create tab, Queries group] to open the Add Tables task pane.
    Click the Query Design button [Create tab, Queries group] to open the Show Table dialog box.
    NOTE: The Show Table dialog in your version of Microsoft Access may appear different due to Office 365 updates.
  • Select the SailboatFleet table and click the Add Selected Tables button.
    Select the SailboatFleet table and click the Add button.
  • Select the SDRentals table and click the Add Selected Tables button.
    Select the SDRentals table and click the Add button.
  • Click the Close button in the Add Tables task pane.
    Click the Close button in the Show Table dialog box.
  • Increase the size of the table objects to display all of the fields.
  • Click the drop-down arrow in the first Field row cell of the query design grid and select FKBoatID.
  • Click the second cell in the Field row, click the drop-down arrow, and select BoatType.
  • Click the third cell in the Field row, click the drop-down arrow, and select FullDayRentalRate.
  • Click the fourth cell in the Field row, click the drop-down arrow, and select FourHourRental?.
  • Click the Totals button [Design tab, Show/Hide group].
    Click the Totals button [Query Tools Design tab, Show/Hide group].
  • Click the Run button [Design tab, Results group] to execute the query. The query should open in Datasheet view and display 16 records (Figure 3-87). This query only shows boats that have been rented. At most a Boat ID appears in two rows; one row if the Four Hour Rental? box is checked and another row if the Four Hour Rental? box is not checked.
    Click the Run button [Query Tools Design tab, Results group] to execute the query. The query should open in Datasheet view and display 16 records (Figure 3-87). This query only shows boats that have been rented. At most a Boat ID appears in two rows; one row if the Four Hour Rental? box is checked and another row if the Four Hour Rental? box is not checked.Figure 3-87 Sailboat query results
• Edit the query to add aggregate functions. Because you are looking only for rentals that were for a full day, use the “No” value on the FourHourRental? field.
  • Click the View button [Home tab, Views group] and select the Design View option to switch back to Design view of the query.
  • Click the Total row cell for the FullDayRentalRate field.
  • Click the drop-down arrow and select Sum.
  • Click the Total row cell for the FourHourRental? field.
  • Click the drop-down arrow and select Where. This causes the Show row check box for the FourHourRental? field to be deselected.
  • Click the Criteria row cell for the FourHourRental? field and enter No. The IntelliSense feature in Access may suggest the value of “Now” while you are typing. Press the Esc key to hide the list and then tab out of the field. The query window should look similar to Figure 3-88.Figure 3-88 Query design grid for the summary query with criteria
  • Click the Run button. The query should open in Datasheet view and display eight records (Figure 3-89). The Boat ID now displays only once since the criteria limits the results only to the full day rentals.Figure 3-89 Results from completed SummaryOfFullDayRentalsByBoat query
• Click the Save button and save the query as FullDayRentalsByBoatSummary.
• Verify that that query works correctly.
  • Open the SDRentals table in Datasheet view.
  • Click the drop-down arrow in the Boat ID field name cell and select the Sort A to Z option.
  • Click the drop-down arrow in the Four Hour Rental? field name cell and select the Sort Cleared to Selected You can see that Boat ID 1010 has five full day rentals. From Figure 3-87 you know that the full day rate for that boat is $179.00 and 5 × $179 = $895.00. This matches the results of your query.
  • Click the Remove Sort button [Home tab, Sort & Filter group].
  • Close the SDRentals table. If prompted, do not save the changes to the table.
• Save a copy of the query.
  • Click the Save As button [File Tab].
  • Select the Save Object As button and then click the Save As button.
  • Replace the suggested name with FullDayRentalsByBoatSummaryWithParameter. This second query will enable the Sailing Club to enter the date range for the summary query and also will count the number of rentals.
• Edit the query to add additional fields and an aggregate function and parameters.
  • Click the Design View icon in the Status bar.
  • Drag the FullDayRentalRate field from the SailboatFleet table to the fifth column in the query design grid. (Yes, this field is in the query twice!)
  • Click the Total row cell for this field, click the drop-down arrow, and select Count.
  • Drag the RentalDate field from the SDRentals table to the sixth column in the query design grid.
  • Click the Total row cell for this field, click the drop-down arrow, and select Where. This causes the Show row check box for the RentalDate field to be deselected.
  • Click the Criteria row cell of the RentalDate field.
  • Right-click and select Zoom.
  • Type Between [Enter the Start Date] And [Enter the End Date] in the Zoom box to add the two parameters.
  • Click OK. The query window should look similar to Figure 3-90.Figure 3-90 Query window with aggregate functions and a parameter
  • Click the Save button and save the changes to the query.
• Test the query.
  • Click the Run button.
  • Enter 2/1/2020 in the Enter the Start Date box of the Enter Parameter Value dialog box.
  • Click OK.
  • Enter 2/28/2020 in the Enter the End Date box of the Enter Parameter Value dialog box.
  • Click OK. The query should open in Datasheet view and display the records shown in Figure 3-91.Figure 3-91 Query results with Sum and Count aggregate functions and parameter
  • Click the Save button to save the changes made to the query.
  • Close the query.
• Review the query.
  • Reopen the query in Design view. Notice that Access has reordered the position of the fields. The two fields that use the Where option on the Total row have been moved to the right side of the query design grid. This does not affect the way the query runs.
  • Close the query.
• Create a new query in Design view. The Sailing Club wants to find out what boat types have been rented so it can decide whether to adjust pricing or marketing of its boat types. If a boat has been rented, a record of that rental exists in the SDRentals table.
  • Click the Query Design button [Create tab, Queries group] to open the Add Tables task pane.
    Click the Query Design button [Create tab, Queries group] to open the Show Table dialog box.
    NOTE: The Show Table dialog in your version of Microsoft Access may appear different due to Office 365 updates.
  • Select the SDRentals table, press and hold the Shift key, select the SailboatFleet table, and click the Add Selected Tables button.
    Select the SDRentals table, press and hold the Shift key, select the SailboatFleet table, and click the Add button.
  • Click the Close button in the Add Tables task pane.
    Click the Close button in the Show Table dialog box.
  • Click the drop-down arrow in the first Field row cell in the query design grid and select FKBoatID.
  • Click the Sort row, click the drop-down arrow, and select Ascending.
  • Click the second cell in the Field row, click the drop-down arrow, and select BoatType.
  • Click the Run button [Design tab, Results group] to execute the query. The query should open in Datasheet view and display 28 records (Figure 3-92). Notice that in a number of instances the same Boat ID displays multiple times, once for each time that boat was rented.
    Click the Run button [Query Tools Design tab, Results group] to execute the query. The query should open in Datasheet view and display 28 records (Figure 3-92). Notice that in a number of instances the same Boat ID displays multiple times, once for each time that boat was rented.Figure 3-92 BoatID query results
• Edit the query to display Unique Values.
  • Click the Design View icon in the Status bar to switch back to Design view of the query.
  • Click the Property Sheet button [Design tab, Show/Hide group] to open the Property Sheet.
    Click the Property Sheet button [Query Tools Design tab, Show/Hide group] to open the Property Sheet.
  • Click anywhere in the Query Window so that the Selection type in the Property Sheet displays Query Properties.
  • Click the Unique Values property box and select Yes. The query window should look similar to Figure 3-93.Figure 3-93 Set the Unique Values property in the Property Sheet of the query
  • Close the Property Sheet.
  • Click the Run button. The query should open in Datasheet view and display 12 records (Figure 3-94). Each Boat ID now displays only once, but the Boat Types are still repeated.Figure 3-94 Query results with repeated BoatType values
• Edit the query so the Boat Type displays only once. The Boat Type is displaying more than once because the FKBoatID field is different for each boat.
  • Click the View button [Home tab, Views group] and select the Design View option to switch back to Design view.
  • Click the Sort row of the FKBoatID field, click the drop-down arrow, and select (not sorted).
  • Deselect the Show row check box for the FKBoatID field.
  • Click the Run button. The query should display the five boat types that have been rented at least once (Figure 3-95.)Figure 3-95 Unique records for BoatType
  • Click the Save button and save the query as BoatTypesRented.
  • Close the query.
• Close the database.
• Upload and save your project file.
• Submit project for grading.