Access 2

~ T!Ckrt,FidorClb'C1ont<lb Pr1mii1ri,,Facto~,---•."'r"'ot=, ;-;1o"r;,Tick:;:--· ~ A-,,l;::b,::cny::-~-c,;;,,-,;ff::,,lo::-~-;:;N-::ew:cY:;:o,:ck-.-;-S,ra= ,~= • -~""uti"'·,::c, -~-;;vonk= m

- =------r::.i 10 Sacking Unsafely orrverDl5traetlon Failure lo Keep Right

Passing/lane Violations Tr~fflc control Disregarded Turning lmproper1y

unsafespe:ed

Rc<orG 14 1 ot3

17

USING MICROSOFT ACCESS 2016 Independent Project 6-5

Independent Project 6-5 The New York Department of Motor Vehicles wants to extend the functionality of its database. Create a crosstab query to count the number of tickets by primary factor in each city. Use the top values property in a query. Edit a main report to add a concatenation formula to a text box. Add a subreport to a main report. Customize the subreport in Design view and add a running total to number the rows in the report. This project has been modified for use in SIMnet®.

Skills Covered in This Project • Create a crosstab query. • Find top values in a query. • Edit multiple controls on a report simultaneously. • Add a control to a report. • Edit formatting properties of a control.

• Use a concatenation formula in a text box. • Add a subreport to a main report. • Use an aggregate function on a subreport. • Edit Report Header and Report Footer sections.

Step 1: Download start file

1. Open the NewYorkDMV-06 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 crosstab query to count the number of tickets by city and primary factor.

a. Launch the Query Wizard and select the Crosstab Query Wizard. Refer back to SLO 6.2: Exploring the Query Wizard if you need additional guidance.

b. Select the Tickets table, on the first page of the wizard, since that contains the data you want to summarize.

c. Move the PrimaryFactor field to the Selected Fields window, on the second page of the wizard, to use as the row headings.

d. Select the City field, on the third page of the wizard, to use as the column headings. e. Select the TicketNumber field and the Count aggregate function, on the fourth page of the

wizard. Leave the Yes, include row sums check box selected. f. Enter TicketsFactorCityCrosstab as the query name on the last page of the wizard. Click Finish

to execute the query. The results should match those shown in Figure 6-117. g. Save and close the query.

5. Create a query to find the two most common reasons (factors) tickets are written. a. Open the

TicketCountByFactor query in Datasheet view. The query executes and shows each of the eight primary factors along with a count of how many tickets have been written.

6-117 Crosstab query results showing tickets by primary factor in each city, including row sums

b. Save a copy of that query as TopTwoTicketCountByFactor. If necessary, review the How To: Save a Copy of an Object in SLO 1.2: Creating and Managing a Database.

c. Edit the TopTwoTicketCountByFactor query in Design view. In the Top Values property of the query, enter 2 to limit the query results to only the two factors that appear most frequently on all the tickets. Refer back to the Find the Top Values in a Query section in SLO 6.3: Creating Advanced Queries and Changing Query Properties, if you need additional guidance.

d. Edit the query to not show the TicketNumber in the results.

Access 2016 Chapter 6 Creating Advanced Queries and Reports Last Updated: 3/6/18 Page 1

,_ : : : I

: !Officer ID !A00021411 Date Hired 5/15/2015

Officer Name Reggie Defrank

TicketsSubre ort

TicketNum DLNumber TicketDate aty

423589449, 12345838 3/25/2016 Syracuse

423589528< 39989780 4/24/2016 Syracuse

4235896381 15.500786 5/14/2016 Syracuse

423589o88' 12344537 5/14/2016 Syracuse

423589690. 15502325 5/15/2016 Syracuse

42358969~ 18955806 5/15/2016 Syracuse

Primaryfactor

!Driver Distraction

!Alcohol

§ ohol

lrurnmg Improperly

!Traffic Control Disregarded I !unsafe Speed

~ TopTwoTicketCountByfact... x

PrimaryFactor

M,Hi4W44,i Alcohol

Reco rd: I 1 of .l  I ~ No 6-118 Top two ticket factors

Fine

$120.00

$400.00

$400.00

$120.00

$130.00

$90.00

USING MICROSOFT ACCESS 2016 Independent Project 6-5

e. Save the changes to the query. Test the query. The query results should match those shown in Figure 6-118.

f. Edit the query to change the TicketNumber field to an Ascending sort. This allows you to further explore how the sorting choice influences the query results when using the Top Values property.

g. Run the query. The query now shows the two reasons that appear on the fewest number of tickets. Failure to Keep Right and Backing Unsafely should display in the results.

h. Close the query without saving the changes since that is not what you want the query to display. 6. Add a concatenation formula to a text box in the OfficerTicketListing report. To provide context

for the steps to customize the report, view the completed report in Figure 6-121. a. Open OfficerTicketListing in Design view. b. Edit the unbound text box on the report. Add a concatenation formula into the Control

Source property that will combine the FirstName field, a space, and the LastName field. c. Select Transparent in the Border Style property. d. Save the changes. e. View the report in Report view to ensure that the formula works correctly.

7. Add a subreport to the OfficerTicketListing main report. Refer back to How To: Use a Table or Query as a Subreport in a Main Report in SLO 6.7: Using Subreports if you need additional guidance. a. Change the OfficerTicketListing report to Design view. b. Use the Subform/Subreport button to add a subreport onto the main report. Position the

subreport below the existing fields near the left side of the main report. NOTE: Align the top of the subreport just above the 1” left ruler mark in order to successfully complete step 10.

c. Make sure that the Use existing Tables and Queries button is selected on the first page of the SubReport Wizard.

d. Advance to the second page of the SubReport Wizard and choose the Tickets table and add all the fields except the OfficerBadgeNumber and the RecordAddedTimeStamp from the Tickets table onto the subreport.

e. Advance to the third page of the SubReport Wizard, accept the Choose from a list radio button and the default linking statement.

f. Advance to the last page of the wizard and edit the suggested name for the subreport so that it reads TicketsSubreport.

g. Use Layout view to verify that the subreport is correctly linked to the main report. The report should look similar to Figure 6-119.

6-119 Layout view after subreport is added to main report

h. Save and close the report.

Access 2016 Chapter 6 Creating Advanced Queries and Reports Last Updated: 3/6/18 Page 2

USING MICROSOFT ACCESS 2016 Independent Project 6-5

8. Edit the TicketsSubreport in Design view. a. Open the TicketsSubreport in Design view. If needed, open the Property Sheet. b. Select the Fine text box and label and enter .7" in the Width property and 6.3" in the Left

property. c. Select the City text box and label and enter 1.1" in the Width property and 3" in the Left

property. d. Select the TicketDate text box and label and enter .8" in the Width property and 2.15" in

the Left property. e. Select the DLNumber text box and label and enter .9" in the Width property and 1.2" in

the Left property. f. Select the TicketNumber text box and label and enter 1" in the Width property and .1" in

the Left property. g. Select the PrimaryFactor combo box and enter 2" in the Width property, 4.2" in the Left

property, and select Transparent in the Border Style property. h. Select the PrimaryFactor label and enter 2" in the Width property and 4.2" in the Left

property. i. In the Caption property of the labels, insert a space between Ticket and Number, DL and

Number, Ticket and Date, and Primary and Factor. j. Adjust all labels to be Left aligned. k. Adjust the Width property of the report to be 7.3". l. Adjust the Height property of the Detail section to be .3". m. Save your changes.

9. Use an aggregate function on the subreport. a. Select the Report Footer section bar and enter .4" in the Height property. b. Add a text box control near the left side of the report footer of the subreport. c. Select the text box and enter =Count([TicketNumber]) in the Control Source property, .5" in

the Width property, 1.35" in the Left property, Transparent in the Border Style property, and Bold in the Font Weight property.

d. Select the label of the text box and enter Total Tickets: in the Caption property, .9" in the Width property, .4" in the Left property, and Bold in the Font Weight property.

e. Save and close the subreport. 10. Edit the OfficerTicketListing report in Design view to add Report Header and Report Footer

sections and ensure the report has a professional appearance. a. Open the OfficerTicketListing in Design view. If needed, open the Property Sheet. b. Select the TicketsSubreport control and change the Width property to 7.5", the Left property

to .2" and the Border Style property to Transparent. c. Delete the TicketsSubreport label. d. Adjust the Height property of the Detail section to 1.9". e. Adjust the Width property of the report to 7.8". f. Add a Report Header and Report Footer section to the report. g. Adjust the Height property of the Report Header section to .4". h. Remove the Page Header and Page Footer sections from the report. i. Add a label with the Caption of Listing of Tickets by Officer to the Report Header section.

Access 2016 Chapter 6 Creating Advanced Queries and Reports Last Updated: 3/6/18 Page 3

• • • I • • • 1

~ Detail

: : :: •• . •• . •• . •• .• . •• . •• 1

~ Report Footer

••I••• 2 •••I••• 3 •• • I••• 4 •• •I••• 5 •••I••• 6 •••I•• •7 •••I

• . •• . •• . •••• . •• . •• .. • . 1 .. • .. • •• .. • .. •• . •• . •• . , . . . . . . . . . . . . . . . . . . . . . . I .

Listing ofTickets by Officer

Officer ID A00021411 Date Hired 5/15/2015

Officer Name Reggie Defra nk

Ticket Number DLNumber Ticket Date City Primary Factor Fine

4235894496 12345838 3/25/ 2016 Syracuse Driver Distraction $120.00

4235895280 39989780 4/ 24/2016 Syracuse Alcohol $400.00

4235896886 15500786 5/ 14/ 2016 Syracuse Alcohol $400.00

4235896888 12344537 5/ 14/2016 Syracuse Turn ing Improperly $120.00

4235896903 15502325 5/15/2016 Syracuse Tra ffi c Control Disregarded $130.00

4235896915 18955806 5/ 15/ 2016 Syracuse Unsafe Speed S9{).00

Total Tickets:

Officer ID A00032567 Date Hired 12/20/2013

Officer Name Alex Rodriguez

Ticket Number DLNumber Ticket Date c,1y Primary Factor Fine

4235879527 12348279 1/4/ 2016 Buffalo Unsafe Speed $360.00

4235889527 15503625 1/26/2016 Buffalo A lcohol $400.00

4235895001 12348279 4/ 10/ 2016 Buffalo Un safe Spe"'d $45 .00

4235895648 10000.501 5/ 11/ 2010 Buffalo u nsafe speed $180.00

4235896918 39992391 5/ 17/2016 Buffalo Unsafe Speed S90.oo

Total Tickets:

USING MICROSOFT ACCESS 2016 Independent Project 6-5

Step 2 Upload & Save

Step 3 Grade my Project

j. Select the label and enter 2.2" in the Width property, .25" in the Height property, .08" in the Top property, 2.9" in the Left property, and 14 in the Font Size property. When entering property values, don’t worry if Access slightly changes a few of the values (for example, .08" becomes .0799".)

k. Adjust the Height property of the Report Footer section to .4".

l. Add a label with the Caption of Report created by Student Name to the Report Footer section.

m. Select the label and enter 2.2" in the Width property, .2" in the Height property, .08" in the Top property, and .15" in the Left property.

n. Save the report. The completed report should look similar to Figure 6-120.

6-120 Design view of the completed OfficerTicketListing report

11. Change to Report view to view the completed report. Figure 6-121 shows the finished report.

12. Close the report.

13. Close the database.

14. Upload and save your project file.

15. Submit project for grading.

6-121 Report view of the completed report

Access 2016 Chapter 6 Creating Advanced Queries and Reports Last Updated: 3/6/18 Page 4