Access 2024 In Practice - Ch 3 Guided Project 3-2

Guided Project 3-2 SIMnet Project Steps to complete This Project

Mark the steps as checked when you complete them.

1.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.

2.Enable content in the database.

3.Create a new summary query in Design view. San Diego Sailing 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.

4.Click the Query Design button [Create tab, Queries group] to open the Query Design window and the Add Tables task pane.

5.Double-click the SailboatFleet table to add it to the Query Design window.

6.Double-click the SDRentals table to add it to the Query Design window.

7.Click the X in the right corner of the Add Tables task pane to close it.

8.Right-click inside each table to open the context menu and select Size to Fit to increase the size of each table object.

9.Click the drop-down arrow in the first Field row cell of the query design grid and select SDRentals.FKBoatID.

10.Click the second cell in the Field row, click the drop-down arrow, and select SailboatFleet.BoatType.

11.Click the third cell in the Field row, click the drop-down arrow, and select SailboatFleet.FullDayRentalRate.

12.Click the fourth cell in the Field row, click the drop-down arrow, and select SDRentals.FourHourRental?.

13.Click the Totals button [Query Design tab, Show/Hide group].

14.Click the Run button [Query Design tab, Results group] to execute the query. The query should open in Datasheet view and display 13 records (Figure 3-99). 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.

A screenshot shows a query table in the datasheet view.The table has four columns and thirteen rows. The column headings are Boat I D, Boat Type, Full Day Rate, and Four Hour Rental. The Boat I D column shows I Ds such as 1003, 1015, 1146, 1152, 1185, 1225, 1310, 1401, and 1419. The Boat I Ds 1003, 1015, 1152, and 1225 are displayed twice. The Boat Type column shows the model or type of sailboat, which includes Catalina 270, Hunter 33, Capri 22 M k 2, Hunter 36, and Beneteau 373. The Full Day Rate column shows rates such as $ 205.00, $ 355.00, $ 121.00, $ 395.00, and $ 455.00. The Four Hour Rental column consists of check boxes. The check boxes are checked for the Boat I Ds 1003, 1015, 1152, 1225, 1401, and 1419.

Figure 3-99 Sailboat query results

15.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.

16.Click the View button [Home tab, Views group] and select the Design View option to switch back to Design view of the query.

17.Click the Total row cell for the FullDayRentalRate field.

18.Click the drop-down arrow and select Sum.

19.Click the Total row cell for the FourHourRental? field.

20.Click the drop-down arrow and select Where. This causes the Show row check box for the FourHourRental? field to be deselected.

21.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-100.

A screenshot shows a summary query.The query table has four columns and seven rows. The table has rows for various query parameters, such as Field, Table, Total, Sort, Show, Criteria, and Or. The Field row shows the entries F K Boat I D, Boat Type, Full Day Rental Rate, and Four Hour Rental? The Table row shows the entry S D Rentals for the F K Boat I D and Four Hour Rental fields, and the entry Sailboat Fleet for the Boat Type and Full Day Rental Rate fields. The Total row shows the entry Group By for F K Boat I D and Boat Type fields, and the entries Sum and Where for Full Day Rental Rate and Four Hour Rental? fields, respectively. The Sort row is empty. The Show row has check boxes in each column of which the checkboxes for F K Boat I D, Boat Type, and Full Day Rental Rate fields are checked. The Criteria row shows the entry No for the Four Hour Rental field.

Figure 3-100 Query design grid for the summary query with criteria

22.Click the Run button. The query should open in Datasheet view and display seven records (Figure 3-101). The Boat ID now displays only once because the criteria limits the results only to the full day rentals.

A screenshot shows a query table in the datasheet view depicting the Full Day Rentals By Boat summary.The table has three columns and seven rows. The column headings are Boat I D, Boat Type, and Sum Of Full Day Rental Rate. The Boat I D column shows I Ds such as 1003, 1015, 1146, 1152, 1185, 1225, and 1310. The Boat Type column shows the model or type of sailboat, which includes Catalina 270, Hunter 33, Capri 22 M k 2, Hunter 36, and Beneteau 373. The Sum Of Full Day Rental Rate column shows the sum of the full day rental rates.

Figure 3-101 Results from completed FullDayRentalsByBoatSummary query

23.Click the Save button and save the query as FullDayRentalsByBoatSummary.

24.Verify that that query works correctly.

25.Open the SDRentals table in Datasheet view.

26.Click the drop-down arrow in the Boat ID field name cell and select the Sort A to Z option.

27.Click the drop-down arrow in the Four Hour Rental? field name cell and select the Sort Cleared to Selected option. You can see that Boat ID 1003 has five full day rentals. From Figure 3-99 you know that the full day rate for that boat is $205.00 and 5 × $205 = $1,025.00. This matches the results of your query.

28.Click the Remove Sort button [Home tab, Sort & Filter group].

29.Close the SDRentals table. If prompted, do not save the changes to the table.

30.Save a copy of the query.

31.Click the Save As button [File tab].

32.Select the Save Object As button and then click the Save As button.

33.Replace the suggested name with Parameter-FullDayRentalsByBoatSummary and click OK. This second query will enable San Diego Sailing to enter the date range for the summary query and will count the number of rentals.

34.Edit the query to add additional fields and an aggregate function and parameters.

35.Click the Design View icon in the Status bar.

36.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!)

37.Click the Total row cell for this field, click the drop-down arrow, and select Count.

38.Drag the RentalDate field from the SDRentals table to the sixth column in the query design grid.

39.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.

40.Click the Criteria row cell of the RentalDate field.

41.Right-click and select Zoom.

42.Type Between [Enter the Start Date] And [Enter the End Date] in the Zoom box to add the two parameters.

43.Click OK. The query window should look similar to Figure 3-102.

A screenshot shows a query table with aggregate functions.The query table has six columns and seven rows. The table has rows for various query parameters, such as Field, Table, Total, Sort, Show, Criteria, and Or. The Field row shows the entries F K Boat I D, Boat Type, Full Day Rental Rate, Four Hour Rental, Full Day Rental Rate, and Rental Date. The Table row shows the entry S D Rentals for the F K Boat I D, Four Hour Rental, and Rental Date fields, and the entry Sailboat Fleet for the Boat Type, Full Day Rental Rate, and Full Day Rental Rate fields. The Total row shows the entry Group By for F K Boat I D and Boat Type fields, the entry Sum for the Full Day Rental Rate field, the entry Where for Four Hour Rental and Rental Date fields, and the entry Count for the Full Day Rental Rate field. The Sort row is empty. The Show row has check boxes in each column of which the checkboxes for F K Boat I D, Boat Type, and Full Day Rental Rate fields are checked. The Criteria row shows the entry No for the Four Hour Rental field, and the entry Between [Enter the Start Date] And [Enter the End Date] in the Rental Date field.

Figure 3-102 Query window with aggregate functions and a parameter

44.Click the Save button and save the changes to the query.

Test the query.

45.Click the Run button.

46.Enter 7/1/2026 in the Enter the Start Date box of the Enter Parameter Value dialog box.

47.Click OK.

48.Enter 7/31/2026 in the Enter the End Date box of the Enter Parameter Value dialog box.

49.Click OK. The query should open in Datasheet view and display the records shown in Figure 3-103.

A screenshot shows a query table in the datasheet view depicting the Sum and the Count aggregate functions.The table has four columns and four rows. The column headings are Boat I D, Boat Type, Sum Of Full Day Rental Rate, and Count of Full Day Rentals. The Boat I D column shows I Ds such as 1146, 1152, 1225, and 1310. The Boat Type column shows the model or type of sailboat, which includes Hunter 33, Capri 22 M k 2, Hunter 36, and Beneteau 373. The Sum Of Full Day Rental Rate column shows the sum of the full day rental rates. The Count of Full Day Rentals column shows the entries 1, 1, 3, and 1.

Figure 3-103 Query results with Sum and Count aggregate functions and parameter

50.Click the Save button to save the changes made to the query.

51.Close the query.

Review the Parameter-FullDayRentalsByBoatSummary query.

Reopen the query in Design view. Notice that Access has reordered the position of the fields. The two fields (FullDayRentalRate and RentalDate) 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.

52.Close the query.

53.Create a new query in Design view. San Diego Sailing 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.

54.Click the Query Design button [Create tab, Queries group] to open the Query Design window and the Add Tables task pane.

55.Click the Tables tab in the Add Tables task pane. Double-click the SDRentals table and double-click the SailboatFleet table to add them to the query.

56.Click the X in the right corner of the Add Tables task pane to close it.

57.Right-click inside each table to open the context menu and select Size to Fit to increase the size of each table.

58.Click the drop-down arrow in the first Field row cell in the query design grid and select SDRentals.FKBoatID.

59.Click the Sort row, click the drop-down arrow, and select Ascending.

60.Click the second cell in the Field row, click the drop-down arrow, and select SailboatFleet.BoatType.

61.Click the Run button [Query Design tab, Results group] to execute the query. The query should open in Datasheet view and display 28 records (Figure 3-104). Notice that in a number of instances the same Boat ID displays multiple times, once for each time that boat was rented.

A screenshot shows a query table in the datasheet view depicting the Boat I D query results.The table has two columns and twenty-eight rows. The column headings are Boat I D and Boat Type. The Boat I D column shows I Ds such as 1003, 1015, 1146, 1152, 1185, 1225, 1310, 1401, and 1419. The Boat I D 1003 is repeated 8 times, 1015 is repeated 2 times, 1146 is repeated 3 times, 1152 is repeated 6 times, and 1225 is repeated 5 times are repeated. The Boat Type column shows the model or type of sailboat, which includes Catalina 270, Hunter 33, Capri 22 M k 2, Hunter 36, and Beneteau 373. For example, Boat I D 1003 (Catalina 270) appears multiple times consecutively.

Figure 3-104 BoatID query results

62.Edit the query to display Unique Values.

63.Click the Design View icon in the Status bar to switch back to Design view of the query.

64.Click the Property Sheet button [Query Design tab, Show/Hide group] to open the Property Sheet.

65.Click anywhere in the Query Window so that the Selection type in the Property Sheet updates to display Query Properties.

66.Click the Unique Values property box and select Yes. The query window should look similar to Figure 3-105.

A screenshot shows the unique values property in the property sheet of the query.The query is titled Query 1. Two boxes placed side-by-side are titled S D Rentals and Sailboat Fleet. The S D Rentals box lists fields such as Rental I D, F K Boat I D, Rental Date, Four Hour Rental, and Member I D. A short horizontal line on the right, corresponding to F K Boat I D, has the infinity symbol. The Sailboat Fleet box lists fields such as Boat I D, Boat Type, Length, Seats, Sleeps, Four Hour Rental Rate, Full Day Rental Rate, Gallery With Stove, and Model Year. A short horizontal line on the left, corresponding to Boat I D, has the number 1. A line from the infinity line extends up to the 1 line. A query design grid shows a table below with two columns and six rows. The table has rows for various query parameters, such as Field, Table, Sort, Show, Criteria, and Or. The Field row shows the entries F K Boat I D and the Boat Type. The Table row shows the entries S D Rentals and Sailboat Fleet. The Sort row indicates that the results will be sorted in Ascending order based on the F K Boat I D field. The Show row has check boxes in each column, with each checkbox checked. The Criteria and Or rows are empty. On the right of the query and the query design grid is the Property Sheet. The text Selection type: Query Properties is shown below. The sheet has a tab for General. The General tab shows a list of properties such as: Description: Blank. Default View: Datasheet. Output All Fields: No. Top Values: All. Unique Values: Yes (Highlighted in black). Unique Records: No. Record Locks: No Locks. Recordset Type: Dynaset. Order By: Blank. Max Records: Blank. Orientation: Left-to-Right. Subdatasheet Name: Blank. Link Child Fields: Blank. Link Master Fields: Blank. Subdatasheet Height: 0 inches. Subdatasheet Expanded: No. Filter On Load: No. Order By On Load: Yes.

Figure 3-105 Set the Unique Values property in the Property Sheet of the query

67.Close the Property Sheet.

68.Click the Run button. The query should open in Datasheet view and display 9 records (Figure 3-106). Each Boat ID now displays only once, but the Boat Types are still repeated.

A screenshot shows a query table in the datasheet view depicting the Boat Type values.The table has two columns and nine rows. The column headings are Boat I D and Boat Type. The Boat I D column shows I Ds such as 1003, 1015, 1146, 1152, 1185, 1225, 1310, 1401, and 1419. The Boat Type column shows the model or type of sailboat, which includes Catalina 270, Hunter 33, Capri 22 M k 2, Hunter 36, and Beneteau 373. The Boat Types are repeated.

Figure 3-106 Query results with repeated BoatType values

69.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.

70.Click the View button [Home tab, Views group] and select the Design View option to switch back to Design view.

71.Click the Sort row of the FKBoatID field, click the drop-down arrow, and select (not sorted).

72.Deselect the Show row check box for the FKBoatID field.

73.Click the Run button. The query should display the five boat types that have been rented at least once (Figure 3-107.)

A screenshot shows a query table in the datasheet view depicting the Unique records for Boat Type.The table has one column and five rows. The column heading is Boat Type. The Boat Type column shows the model or type of sailboat, which includes Beneteau 373, Capri 22 M k 2, Catalina 270, Hunter 33, and Hunter 36.

Figure 3-107 Unique records for BoatType

74.Click the Save button and save the query as BoatTypesRented.

75.Close the query.

76.Close the database.

77.Upload and save your project file.

78.Submit project for grading.