V2010, S12

ACCT 2840

Lessons7-9
Unit Review Exercise

Instructions

The Public Safety Department of Tophill College is in the process of developing a database to track parking permits and traffic violations. Follow the instructions below to begin building the Tophill database. This exercise is based on casesfrom the Adamski text with modification by the instructor.

  1. Download, save, and open the database named Tophill 3 found on the Test 3 Review page.

2.Open the Permit table and enter yourself.

a.Your permit number is 99999.

b.Your license plate number is TN1234.

3.Create a query based on the Permit and Citations tables.

a.Include the OwnerFirst, OwnerLast, PermitNum, CitationNum and ViolationCode fields in the query results.

b.Design the query so that only records with last names beginning with the letter “L” are returned in the results.

c.Save the query as LCitationsQry.

4.Create a query based on the Citation table,

a.Include the CitationNum, CitationDate, and ViolationCode fields in the query results.

b.The query should allow the user to specify the violation code when the query is run.

c.Save the query as ViolationParameterQry.

5.School officials would like to see the highest ticket values.

a.Design a top-values query based on the Payment and Citations tables.

b.Include the CitationNum, CitationDate, ViolationCode, and PaymentAmt fields in the query results.

c.The query should return the top (highest) 20% of ticket values.

d.Save the query as HighTicketsQry.

6.Design a simple select query.

a.Base the query on the Citations and Violations tables.

b.Include the CitationDate, ViolationCode, and CitationNum fields in the query results.

c.Save the query as CitationDateCodeNumQry.

7.Create a crosstab query using the Crosstab Query Wizard.

a.Base the crosstab query on CitationDateCodeNumQry.

b.Set the Row Heading as ViolationCode.

c.Set the Column Heading as CitationDate and group by Date.

d.Count the number of citations.

e.Save the query as CitationDateCrosstab.

8.Create a simple select query.

a.Base the query on the Citations, Violations, and Permit tables.

b.Include the OwnerFirst, OwnerLast, ViolationCode, ViolationDes, ViolationAmt, CitationNum, and CitationDate fields in the query results.

c.Save the query as CitationInfoQry.

9.Create a custom report based on CitationInfoQry.

  1. Using a blank report in Design view, set the Record Source to CitationInfoQry.
  2. Make the report page width 6.5 inches.
  3. Add a group for OwnerLast. Sort by CitationDate.
  4. Place an unbound textbox in the OwnerLast Group Header. Enter an expression in the unbound textbox to join the first and last name of the owner.
  5. Remove the label from the unbound textbox and place the unbound textbox flush against the left margin in the group header.
  6. Bold and italicize the owner name.
  7. Place the CitationNum, CitationDate, ViolationCode, ViolationDes, and ViolationAmt fields in the detail section of the report.
  8. Edit the labels for the items in the Detail section so that they read as follows:

Citation Num = Citation Number

ViolationDes = Violation Description

ViolationAmt = Violation Amount

  1. Adjust the Citation Date Citation Number, and Violation Amount labels so that they display on two lines. Move the labels for each field to the Page Header and arrange the fields in a horizontal layout in the order shown in the example below.
  1. Place a background color of Brown 2 on the Page Header section.
  2. Place a two-point line under the field labels in the Page Header. Change the color of the line to Brown 5.
  3. Make sure the labels and textboxes are aligned.
  4. Place another ViolationAmt field in the Detail section to the right of the current ViolationAmt field. Set this field as a Running Sum over the group.
  5. Move the running sum label to the Page Header and edit the label to read “Violations Total”.

  1. Add a footer for the OwnerLast group.Place an unbound textbox in the OwnerLast footer. Enter an expression in the unbound textbox that displays the message, “Your permit has expired. You have 10 days to renew” if the Violation Code is EP. For all other violation codes, the message should read, “Pay your ticket at the Business Office.”
  2. Bold and center the message.
  3. Place a dashed, two-point border around the message.
  4. Remove the rectangles from all fields except for the message field.
  5. Remove the alternate background color from all sections.
  6. Add a Report Header/Footer.
  7. Place a title flush left in the Report Header that reads “Violations Report”. Change the background color for the Report Header to Brown 2. Make the label text 14 points, brown 5.
  8. Add an unbound textbox to the Report Footer. Enter an expression in the unbound textbox to total all Violation Amounts.
  9. Edit the label for the unbound textbox to read “Total Violations”.
  10. Remove the rectangle from the Total Violations textbox, format the total to display in Currency with no decimal places, and bold the total.
  11. Place a two-point line above and below the overall total.
  12. Make sure the spacing for each section is appropriate.

10.Save the report.

a.Save the report as ViolationsRpt.

b.A portion of the completed report is shown below.

Page 1 of 3