ACCT 2840 - Nashville State Community College



ACCT 2840

Lessons 4-6

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 continue working with the Tophill database. This exercise is based on cases from the Adamski text with modification by the instructor.

1. Create a query named High Citations in Design View.

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

b. Include the owner first and last name, the date of the citation, the citation number and the violation amount.

c. The query should return students with violation amounts of $50 or more.

d. Sort the query by violation amount in ascending order.

e. Run the query to make sure the results are what you intended.

2. Create a query named Total Citations in Design View.

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

b. Include the owner first and last name and ViolationAmt fields.

c. Using the Group By feature, set the ViolationAmt field so that it totals the violations for each permit holder.

d. Sort the query in ascending order by owner last name.

e. Run the query to make sure the results are what you intended. Check figure: Scott Grissom’s violations total $95.

f. In Datasheet View, add the Sum to the ViolationAmt field so that the total of all violations appears at the bottom of the query. Check figure: $760.

3. Create a query named Additional Fees in Design View.

a. Base the query on the Payment table.

b. Include the CitationNum and PaymentAmt fields.

c. Sort the query results in descending order by PaymentAmt.

d. All tickets are now to be assessed an additional $5 fee. In the query design window, create a new blank field called TotalFee. This is to be a calculated field that adds $5 to the PaymentAmt field.

e. Run the query to make sure the results are what you intended. Check figure: The total fee for Citation Number 242801 is $80.

4. Create a query named Citation Statistics in Design View.

a. Base the query on the Payment table.

b. Using only the PaymentAmt field, find the minimum, maximum, and average payment (violation) amounts.

c. Change the name for each statistic to a meaningful name.

d. Run the query to make sure the results are what you intended.

5. Open the Permit table in Datasheet View.

a. Add a record with a new permit number of 04333 and license plate number of TN1234.

b. Enter your name in the owner first and last name fields.

6. Open the Citation table in design view.

a. Add a combo box lookup field to the ViolationCode field. The value list will consist of the following items:

BE

EM

EP

HA

RD

b. Make sure that the user can only select from the list you provide.

c. Save the changes to the table and switch to Datasheet view.

d. Enter a violation for yourself (permit #04333). The Citation Number will be the next available number. Use today’s date and time.

7. Create a query named Expired Permit Citation in Design View.

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

b. Include the owner name, and violation code in the query results.

c. The query should return only the name of students with expired permit (EP) violations.

d. Sort by owner last name in ascending order.

e. Run the query to make sure the results are what you intended.

8. Export the Expired Permit Citation query to an Excel spreadsheet. Save the spreadsheet as Expired Permits Spreadsheet.xls.

9. Administration would like to know whether more violations occur in the morning or in the evening. Create a query named Morning Violations in Design View.

a. Base the query on the Citation table.

b. Include the CitationNum, CitationDate, and CitationTime fields.

c. Select only citations for the time period of 12:00 am to 11:59 am.

d. Run the query to make sure the results are what you intended.

e. In query datasheet view, use the Totals tool to count the number of violations.

10. Create a query named Evening Violations in Design View.

a. Open the Morning Violations query in Design View.

b. Change the CitationTime criteria so that you select citations for the time period of 12:00 pm to 11:59 pm.

c. Save the updated query as Evening Violations.

11. Use query data to provide information.

a. Open both the Morning Violations and Evening Violations queries.

b. Looking at the total field, note which time period, morning or evening, had the most citations.

12. Open a new Word document.

a. Create a memo with the following heading:

MEMORANDUM

TO: Tophill School Administration

FROM: [Your Name]

DATE: [Today’s Date]

SUBJECT: Violation Times

b. Enter the following text:

Per your request, I have run data on the times most violations occur. As you can see from the tables below, most citations are given during the [ fill in morning or evening ].

c. Export the Morning Violations and Evening Violations queries to the Word document. (HINT: I recommend using the drag-and-drop method).

d. End the memo with the following text:

If you have any questions, please don’t hesitate to contact me.

e. Save the Word document as Violation Times.

13. Create a select query called PermitsAndPmnts.

a. Base the query on all four tables.

b. Include the PermitNum, LicensePlateNum, OwnerFirst, OwnerLast, ViolationAmt, and PaymentAmt fields in that order.

14. Create a custom form in Design view.

a. Set the main form’s Record Source to PermitsAndPmnts.

b. Place all fields except ViolationAmt and PaymentAmt in the Detail section starting at the 1” horizontal and 2” vertical postions.

c. Place the ViolationAmt field to the right of the OwnerFirst field.

d. Place the PaymentAmt field to the right of the OwnerLast field.

e. Make sure all fields are properly aligned.

f. Add an unbound text box below the PaymentAmt field.

g. Enter an expression in the unbound text box to deduct the PaymentAmt from the ViolationAmt.

h. The label for the calculated field should read Amount Owed.

i. Save the form as Violations and Payments.

15. Create a second custom form with a subform in Design view.

a. Set the main form’s Record Source to tblPermit.

b. Place all fields from the Permit table except PermitNum in the Detail section starting at the 1” horizontal and 2” vertical postions.

c. Add a combo box at the top of the Detail section. The combo box will be used to find the record of the PermitNum from the Permit table. The permit number should display in the combo box. Label this field Find a Permit Number.

d. Add a subform based on the Citation table. Include all fields from the Citation table.

e. Name the submform Citations Subform.

f. Remove the label from the subform

g. In Form View, autosize the subform columns.

h. In Design View, add a header to the form with a label that reads Student Permits and Citations.

i. Make the label 22 points, bold, purple.

j. Change the background color for the header to light purple.

k. Save the form as Permits and Citations.

Upload the following files to Lessons 4-6 Review in NS Online Assignments.

Tophill 4-6.accdb, Expired Permits Spreadsheet.xls, Violation Times.doc

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download