According to the requirements of the first two files, operate in the latter two Access files respectively.Access 2016 Chapter 3 Creating and Using Queries Last Updated: 5/3/18 Page 1

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.

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 …Access 2016 Chapter 3 Creating and Using Queries Last Updated: 10/9/18 Page 1

USING MICROSOFT ACCESS 2016 Independent Project 3-5

Independent Project 3-5
The State of New York Department of Motor Vehicles wants to create three queries. The first query provides summary
data on the number of tickets by city and violation. The second query summarizes the total tickets by violation. The third
query provides summary data for the total fines assessed against each driver who has had a ticket. To ensure consistency,
the starting file is provided for you. Use Design view to create the summary queries. Edit the queries to add fields,
aggregate functions, and sorting. Finally, save and run the queries. This project has been modified for use in SIMnet®.

Skills Covered in This Project
• Create a summary query in Design view.
• Edit a query in Design view.
• Add fields to a query.
• Execute a query.

• Save a query.
• Sort query results.
• Add aggregate functions.

1. Open the NewYorkDMV-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 query counts the number of tickets issued by

city and violation.

a. Add the Tickets table into the

Query Design window.

b. Increase the size of the table

object to display all of the

fields.

c. Add the following fields into the

query: City, PrimaryFactor, and

TicketNumber.

d. Add the Total row to the query.

e. Group By the City and

PrimaryFactor fields and Count

the TicketNumber field.

5. Save the query as

TicketCountByCityAndFactor.

6. Run the query. The datasheet

should display 22 records.

7. Widen the field column widths

using AutoFit or Best Fit. The

datasheet should be similar to

Figure 3-103.

8. Save the …BoatID BoatType Length Seats Sleeps FourHourRentalRate FullDayRentalRate GalleyWithStove ModelYear

1010 Catalina 270 28’4″ 8 6 ¤ 139.00 ¤ 179.00 true 1997

1015 Catalina 270 28’4″ 8 6 ¤ 139.00 ¤ 179.00 true 1998

1146 Hunter 33 33’6″ 10 6 ¤ 299.00 ¤ 349.00 true 2003

1150 Capri 22 Mk II 24’8″ 6 4 ¤ 65.00 ¤ 89.00 false 2004

1152 Capri 22 Mk II 24’8″ 6 4 ¤ 65.00 ¤ 89.00 false 2004

1164 Capri 22 Mk II 24’8″ 6 4 ¤ 65.00 ¤ 89.00 false 2006

1168 Hunter 33 33’6″ 10 6 ¤ 299.00 ¤ 349.00 true 2007

1175 Beneteau 40 39’10” 12 6 ¤ 489.00 ¤ 529.00 true 2008

1180 Beneteau 373 36’11” 10 6 ¤ 369.00 ¤ 409.00 true 2009

1185 Hunter 36 35’6″ 10 6 ¤ 349.00 ¤ 389.00 true 2009

1190 Beneteau 373 36’11” 10 6 ¤ 369.00 ¤ 409.00 true 2009

1200 Beneteau 373 36’11” 10 6 ¤ 369.00 ¤ 409.00 true 2010

1205 Beneteau 40 39’10” 12 6 ¤ 489.00 ¤ 529.00 true 2011

1225 Hunter 36 35’6″ 10 6 ¤ 349.00 ¤ 389.00 true 2011

1254 Hunter 36 35’6″ 10 6 ¤ 349.00 ¤ 389.00 true 2012

1310 Beneteau 373 36’11” 10 6 ¤ 369.00 ¤ 409.00 true 2012

1401 Capri 22 Mk II 24’8″ 6 4 ¤ 65.00 ¤ 89.00 false 2014

1410 Beneteau 40 39’10” 12 6 ¤ 489.00 ¤ 529.00 true 2014

1419 Hunter 33 33’6″ 10 6 ¤ 299.00 ¤ 349.00 true 2014

RentalID FKBoatID RentalDate FourHourRental? MemberID

08032 1150 1/15/17 true 1122

08033 1168 1/15/17 false 1386

08035 1010 1/18/17 false 1212

08036 1225 1/23/17 false 1197

08037 1146 1/27/17 false 1075

08040 1010 2/2/17 true 1122

08045 1010 2/9/17 false 1386

08046 1164 2/9/17 true 1427

08048 1010 2/12/17 false 1592

08049 1185 2/14/17 false 1283

08050 1152 2/15/17 true 1122

…LicenseNumber BirthDate LastName FirstName Address City State ZIP Gender

10000501 3/12/47 Smith Timothy 274 W 145th Street New York NY 10039 M

10001786 11/11/47 Johnson Sanda 321 Deforest Road Syracuse NY 13214 F

10003086 1/12/62 Williams Mark 345 E 73rd Street New York NY 10021 M

10004372 1/21/64 Brown Kathy 448 E. 88th Street #5B New York NY 10128 F

10004387 2/7/68 Jones Miranda 433 E. 80th Street New York NY 10075 F

10004672 9/4/69 Miller Steve 609 Church Street Syracuse NY 13212 M

12344537 10/23/72 Davis Austin 903 Champlin Avenue Utica NY 13502 M

12345678 7/11/73 Garcia Emily 288 Kimball Avenue Yonkers NY 10704 F

12345823 11/10/73 Rodriguez Franco 131 Ridgewood Circle Buffalo NY 14218 M

12345838 4/14/73 Wilson Eric 24 Alwin Place Buffalo NY 14211 M

12346123 11/25/73 Schooler Claudia 748 South Street #2 Utica NY 13501 F

12346251 12/13/73 Pelayo Randy 74 Morris Street Albany NY 12208 M

12346377 2/1/74 Johns Greg 72 Wylie Street Schenectady NY 12307 M

12346978 2/7/74 Juarez Jon 118 Boyden Street Syracuse NY 13202 M

12348264 5/12/74 Carter Kevin 38 Cliff Street Yonkers NY 10701 M

12348279 5/15/74 Altier Sharon 180 Wyoming Avenue Buffalo NY 14215 F

12348564 7/21/74 Gates Kristin 1122 Lexington Avenue Schenectady NY 12309 F

12348692 8/20/74 Finch Michael 28 Besch Avenue Albany NY 12209 M

12388495 7/15/76 Krentler Amanda 1022 WeStreet Street Utica NY 13501 F

12388510 7/19/76 Rios Carlos 1815 Bleecker Street Utica NY 13501 M

12388795 3/7/79 Doiron Caroline 418 Spratt Place Utica NY 13502 F

12388923 3/22/79 Singh Mehdi 118 Manning Boulevard Albany NY 12203 M

12389049 4/17/79 Hirshman Bonnie 299 Clinton Avenue Albany NY 12210 F

12390334 4/15/79 Kerr Steve 603 Ravine Drive Utica NY 13502 M

12394037 9/30/79 Plice Haley 428 Whitehall Road Albany NY 12208 F

12394163 10/30/79 Harvey Jake 705 Renaissance Drive Buffalo NY 14221 M

12395448 11/1/79 Tyler Jackie 98 Manhart Street Buffalo NY 14215 F

12396748 11/6/79 …