Access 2019 In Practice - Ch 3 Independent Project 3-5 - State of New York Department Motor Vehicles

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.
[Student Learning Outcomes 3.2, 3.4, 3.8]
File Needed: NewYorkDMV-03.accdb (Available from the Start File link.)
Completed Project File Name: [First Name.Last Name]-NewYorkDMV-03.accdb
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.
2.
3.
Open the NewYorkDMV-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.
4. Create a new summary query in Design view. The query counts the number of tickets issued by city and violation.
a.
b.
c.
d.
e.
5.
Add the Tickets table into the Query Design window.
Increase the size of the table object to display all of the fields.
Add the following fields into the query: City, PrimaryFactor, and TicketNumber.
Add the Total row to the query.
Group By the City and PrimaryFactor fields and Count the TicketNumber field.
Save the query as TicketCountByCityAndFactor.
6.
7.
8.
9.
Run the query. The datasheet should display 22 records.
Adjust the field column widths using AutoFit or Best Fit. The datasheet should be similar to Figure 3-104.
Save the changes to the query.
Save a copy of the query as TicketCountByFactor.
10. Edit the TicketCountByFactor query in Design view. The query should show the total tickets issued for each violation
factor, sorted in descending order by count.
a. Delete the City field.
Sort the TicketNumber field in descending order.
1/2
b.
11.
12.
Save and run the query. The datasheet should
match Figure 3-105.
Close the query.
13. Create a new summary query in Design view. The
query provides summary data on the total fines
assessed against each driver.
a.
b.
c.
d.
e.
f.
14.
Add both tables into the Query Design window.
Increase the size of the table objects to display
all of the fields.
Add the following fields into the query:
LicenseNumber, FirstName, LastName, Fine,
and TicketNumber.
Add the Total row to the query.
Group By the LicenseNumber, FirstName,
and LastName fields, Sum the Fine field and
Count the TicketNumber field.
Sort the Fine field in descending order.
Save the query as TicketsByDriver.
15.
16.
17.
18.
19.
Run the query. The datasheet should display 24
drivers who have received tickets, sorted in
descending order by the total dollar amount of their
f
ines (Figure 3-106).
Close the query.
Close the database.
Upload and save your project file.
Submit project for grading.