project -Database in Access

Page 1 of 4

BUS 145 – Database Project – Part 3

Query Development

The Information Systems (MIS) reporting process consists of adding functionality

to your database by creating queries to perform actions on your database, and to

act as the foundation for your database forms and reports. Queries create views

of the data that can be used to provide information about your revenues,

customers, staff, and services. In this part of the

project, you will create the following queries:

 Eight detail queries

 Two summary queries

 Two crosstab queries

 Three action queries

Creating Documentation

1. Using MS Word, list the questions that you

would like your database to answer and the actions you would like to be

able to perform on your data, along with a description of the purpose of

each query. Number the questions and queries in sequence as shown

below. The samples show one example of each query type but please

note that a total of fifteen queries are required.

a. Query1–Which pets are overdue for vaccinations?

qry1OverdueVaccinations – This query will list all of the pets that

are overdue for vaccinations. The user will be able to specify the

particular type of animal as well as the specific vaccination when

the query is run. The query will produce a contact list so that

owners can be telephoned or sent a reminder. (Detail)

Page 2 of 4

b. Query2–How much revenue has each service earned?

qry2RevenueByService – This query provides a summary of the

revenue earned by each service. It displays a count of how many

times the service has been provided and a total of the revenue

generated. The user can input the range of dates to be included in

the query. The resulting information can be used to determine

whether service prices should be increased or decreased in order to

maximize profits. (Summary)

c. Query3–Service revenue cross-tabulated by Pet Type

qry3RevenueByServiceAndPetType – This query can be used to

analyze the revenue generated for each service cross-tabulated by

various pet types. The user will be able to specify the services, the

pet types, and the range of dates to be analyzed. This information

can be used to determine which services for each pet type provide

the most revenue, and to determine whether there are seasonal

variations in demand. (Crosstab)

d. Query4–Update Prices for Services

qry4PriceUpdate – This query can be used to update the price of

specified services. The user will be able to specify the services the

price change will be applied to. The query will only be used after the

current transactions have been archived. (Action)

Page 3 of 4

Creating the Queries

1. Create the queries that are necessary to retrieve the required information

and to update your database as specified in your documentation. Number

each query so that it corresponds with your documentation (see

documentation examples provided). You do not have to demonstrate the

criteria or properties listed below in every query, but at least two

appropriate examples of each of the items should be demonstrated.

 Combining data from two or more tables

 Appropriate use of field and sort order

 Concatenated and calculated fields (expressions) based on existing fields combined with mathematical operators.

 Functions such as DatePart, DateDiff, IIF, PMT, etc. (Please note: this does not include using the SUM, AVERAGE or other aggregate functions selected from a drop-down list in a Totals query)

 The use of a variety of record selection criteria selected from the following operators: <, <=, >, >=, Like, Between, In, Not, Is Null

 The use of multiple record selection criteria using AND and OR

 Parameters that are passed to a query when it is run

 Modification of query field properties

 Top values

Submission Instructions

1. Print the finished Documentation and staple the mark sheet on top. Hand

in the paperwork to your instructor at the beginning of class on the

assignment due date. The project will not be graded unless a printed copy

of the report is submitted to your instructor by the due date.

2. Upload your Word document file and your database file to your

instructor’s inbox on \\labdatashares OR to the drop box in D2L as

specified by your instructor.

Page 4 of 4

Mark Sheet for Queries

Name: _______________________________________________________

Documentation:

Quality of finished paperwork .............................................................................. /8

Queries Retrieve Data Specified in the Documentation (1 point per query):

Detail Queries ....................................................................................................... /8

Summary Queries .................................................................................................... /2

Crosstab Queries ..................................................................................................... /2

Action Queries ......................................................................................................... /3

Query Layout (2 points each): ............................................................................... /6

  Multi-Table Queries

  Appropriate Field Order

  Appropriate Sort Order

Query Fields (2 points each): ................................................................................ /6

  Concatenated Field(s)

  Calculated Field(s)

  Query Function(s)

Query Criteria (2 points each): .............................................................................. /8

  Variety of Record Selection Criteria

  Multiple Selection Criteria (two or more criteria in a single query)

  Parameter Boxes

  Top Values

Query Properties (2 points each): ......................................................................... /2

  Modified Field Properties (for credit, indicate which queries have modified field properties below)

TOTAL ................................................................................................ /45