AC2016 Guided Project 3 2 Instructions

User Manual:

Open the PDF directly: View PDF PDF.
Page Count: 5

DownloadAC2016-Guided Project-3-2-instructions
Open PDF In BrowserView PDF
USING MICROSOFT ACCESS 2016

Guided Project 3-2

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. This
project has been modified for use in SIMnet®.

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 Office 365. If the instruction does not match your version of Office, try using the
alternate instruction instead.
Step 1:
Download
start file

1. Open the SailingDatabase-03.accdb database start file.
2. The file will be renamed automatically to include your name. Change the project file name if
directed to do so by your instructor.
3. Enable content in the security warning.
4. 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, there is a record in the SDRentals table.
a. Click the Query Design button [Create tab, Queries group] to open the Show Table dialog box.
b. Select the SailboatFleet table and click the Add button.
c. Select the SDRentals table and click the Add button.
d. Click the Close button in the Show Table dialog box.
e. Increase the size of the table objects to display all of the fields.
f. Click the drop-down arrow in the first Field row cell of the query design grid and select
SDRentals.FKBoatID.
g. Click the second cell in the Field row, click
the drop-down arrow, and select
SailboatFleet.BoatType.
h. Click the third cell in the Field row, click the
drop-down arrow, and select SailboatFleet.
FullDayRentalRate.
i. Click the fourth cell in the Field row, click the
drop-down arrow, and select SDRentals.
FourHourRental?.
j. Click the Totals button [Query Tools Design
tab, Show/Hide group].
k. 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-86). This query
only shows boats that have been rented. At

Access 2016 Chapter 3 Creating and Using Queries

Last Updated: 1/29/18 Page 1

USING MICROSOFT ACCESS 2016

Guided Project 3-2

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.
5. 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.
a. Click the View button [Home tab, Views group] and select the Design View option to switch
back to Design view of the query.
b. Click the Total row cell for the FullDayRentalRate field.
c. Click the drop-down arrow and select Sum.
d. Click the Total row cell for the FourHourRental? field.
e. Click the drop-down arrow and select Where. This causes the Show row check box for the
FourHourRental? field to be deselected.
f.

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

g. Click the Run button. The query should
open in Datasheet view and display
eight records (Figure 3-88). The Boat ID
now displays only once since the criteria
limits the results only to the full day
rentals.
6. Click the Save button and save the query as

FullDayRentalsByBoatSummary.
7. Verify that that query works correctly.
a. Open the SDRentals table in Datasheet
view.
b. Click the drop-down arrow in the Boat ID
field name cell.
c. Select the Sort A to Z option.
d. Click the drop-down arrow in the Four Hour Rental? field name cell.
e. Select the Sort Cleared to Selected option. You can see that Boat ID 1010 has five full day
rentals. From Figure 3-86 you know that the full day rate for that boat is $179.00 and 5 x $179 =
$895.00. This matches the results of your query.
f. Click the Remove Sort button [Home tab, Sort & Filter group].
g. Close the SDRentals table. If prompted, do not save the changes to the table.
8. Save a copy of the query.
a. Click the Save As button [File Tab].
b. Select the Save Object As button and then click the Save As button.

Access 2016 Chapter 3 Creating and Using Queries

Last Updated: 1/29/18 Page 2

USING MICROSOFT ACCESS 2016

Guided Project 3-2

c. Replace the suggested name with FullDayRentalsByBoatSummaryWithParameter. This
second query will allow the Sailing Club to enter the date range for the summary query and
also will count the number of rentals.
Save a copy of the query.
a. Close the FullDayRentalsByBoatSummaryWithParameter query if still visible in Datasheet View.
Right-click the query in the Navigation pane, and click Copy
b. Right-click in the Navigation pane and click Paste. The Paste As dialog box opens. Type

FullDayRentalsByBoatSummaryWithParameter in the Paste As dialog box and click OK.
c. Double-click the FullDayRentalsByBoatSummaryWithParameter query in the Navigation
Pane to open the query in Datasheet view.
9. Edit the query to add additional fields and an aggregate function and parameters.
a. Click the View button [Home tab, Views group] and select Design View.
b. 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.)
c. Click the Total row cell for this field, click the drop-down arrow, and select Count.
d. Drag the RentalDate field from the SDRentals table to the sixth column in the query design grid.
e. 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.
f. Click the Criteria row cell of the RentalDate field.
g. Right-click and select Zoom.
h. Type Between [Enter the Start Date] And [Enter the End Date] in the Zoom box to add the
two parameters.
i. Click OK. The query window should look similar to Figure 3-89.

j. Click the Save button and save the changes to the query.
10. Test the query.
a. Click the Run button.
b. Enter 2/1/2017 in the Enter the Start Date box of the Enter Parameter Value dialog box.
c. Click OK.
d. Enter 2/28/2017 in the Enter the End
Date box of the Enter Parameter Value
dialog box.
e. Click OK. The query should open in
Datasheet view and display the records
shown in Figure 3-90.
f. Click the Save button to save the
changes made to the query.
g. Close the query.
Access 2016 Chapter 3 Creating and Using Queries

Last Updated: 1/29/18 Page 3

USING MICROSOFT ACCESS 2016

Guided Project 3-2

11. Review the query.
a. 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.
b. Close the query.
12. 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 on some of
its boat types. If a boat has been rented, a record of that
rental exists in the SDRentals table.
a. Click the Query Design button [Create tab, Queries
group] to open the Show Table dialog box.
b. Select the SDRentals table, press and hold the Shift key,
select the SailboatFleet table and click the Add button.
c. Click the Close button in the Show Table dialog box.
d. Click the drop-down arrow in the first Field row cell in the
query design grid and select SDRentals.FKBoatID.
e. Click the Sort row, click the drop-down arrow, and
select Ascending.
f. Click the second cell in the Field row, click the dropdown arrow, and select SailboatFleet.BoatType.
g. 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-91).
Notice that in a number of instances the same Boat ID
displays multiple times, once for each time that boat
was rented.
13. Edit the query to display Unique Values.
a. Click the View button [Home tab, Views group] and
select the Design View option to switch back to Design
view of the query.
b. Click the Property Sheet button [Query Tools Design tab,
Show/Hide group] to open the Property Sheet.
c. Click anywhere in the Query Window so that the
Selection type in the Property Sheet displays Query
Properties.

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

Access 2016 Chapter 3 Creating and Using Queries

Last Updated: 1/29/18 Page 4

USING MICROSOFT ACCESS 2016

Guided Project 3-2

e. Close the Property Sheet.
f. Click the Run button. The query should open in Datasheet view
and display 12 records (Figure 3-93). Each Boat ID now displays
only once, but the Boat Types are still repeated.
14. 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.
a. Click the View button [Home tab, Views group] and select the
Design View option to switch back to Design view.
b. Click the Sort row of the FKBoatID field, click the drop-down arrow,
and select (not sorted).
c. Deselect the Show row check box for the
FKBoatID field.
d. Click the Run button. The query should
display the five boat types that have been
rented at least once (Figure 3-94.)
e. Click the Save button and save the query as

BoatTypesRented.
Step 2
Upload &
Save
Step 3
Grade my
Project

f. Close the query.
15. Close the database.
16. Upload and save your project file.
17. Submit project for grading.

Access 2016 Chapter 3 Creating and Using Queries

Last Updated: 1/29/18 Page 5
Source Exif Data: 
File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.7
Linearized                      : No
Page Count                      : 5
Language                        : en-US
Tagged PDF                      : Yes
XMP Toolkit                     : Adobe XMP Core 5.6-c015 84.159810, 2016/09/10-02:41:30
Producer                        : Microsoft® Word 2016
Format                          : application/pdf
Creator                         : Maggie Shores
Creator Tool                    : Microsoft® Word 2016
Create Date                     : 2018:01:29 22:12:03-05:00
Modify Date                     : 2018:01:29 22:18:44-05:00
Metadata Date                   : 2018:01:29 22:18:44-05:00
Document ID                     : uuid:8B1D1D88-6480-4E66-98D2-9C4D4D6EE358
Instance ID                     : uuid:1f177183-af08-4ecb-83d5-8a9bce3c64cb
Author                          : Maggie Shores
EXIF Metadata provided by EXIF.tools

Navigation menu