Homework Assignment 5

About this homework

When you enter the MIS workforce you probably will not be given an assignment on a new project. Rather, you will be working on a project for a system that needs upgrading or debugging, what we call a Legacy Project. A modified Northwind database was used to provide a legacy project for an exercise in the Query By Form interface. You will need to download and unzip the mdb that holds the data tables for this assignment. Do not change the data in any way.

Overview

You will create a single-form project that accepts query information from: a list box, radio buttons, and a pair of combos. The query information must be used in an SQL statement to control the rowsource of a listbox which holds aggregate sales figures by employee. The functional objective of the project is analysis of employee sales.

Query by Form

The sales analysis is a simple summary listing of employees and their total orders (subtotal + freight). This listing may be broken down using the QBF you are to provide into one or all employees, the size of total orders, including freight charges (all size orders, < $100, $100-499, $500-999, +$1000), and the date range of orders. A prototype form is shown below.

Form

Data source

Sales order dollars are contained in tblOrder and tblOrderDetail. The tblEmployee contains the employee information for the query by form sales analysis. A query has been provided (qryOrderSubtotal) to assist you in calculating the subtotal for each of the 830 orders. Visio created an ERD diagram that may help you understand the table entities in this assignment. You may not know that Visio will reverse engineer an ER Diagram from an Access mdb that has relationships established among tables.

Tips

You will need SQL skills to provide row sources for the left-side list of employees and right-side combos. Display your sales analysis in the bottom listbox; support the listbox row source with an SQL statement that includes aggregate functions and joins and a union. Use the QBE to develop and test the aggregate functions and joins in your SQL statement; after your QBE-assisted SQL is working add the union syntax to the SQL for providing the grand total sales figure.

The course page has links to a few SQL resources. Look at the SQL Examples for Single Tables for a refresher in fundamental syntax. The textbook used for MIS 325 is a good SQL reference: Running Access 2000 by John Viescas (MS Press, 1999). A very highly rated web site for basic SQL information (including very many examples and tutorials) is James Hoffman's Introduction to Structured Query Language.

Error checking your work

There is a total of $1,330,735.57 in orders; $64,942.69 represents freight in tblOrder and $1,265,792.88 represents the subtotal in qryOrderSubtotal. Your analysis of employee sales must include subtotals plus freight -- the entire $1,330,735.57. Laura Callahan has 104 orders for a total of $134,350.15 in sales orders.

How the QBF will be graded

Graders will use your form and change the settings for employees, order size and date range. Dates are not considered to become a query factor unless two dates are selected from the combos; in other words, leaving one of the dates blank signals that dates will not be included in the query of sales orders.

Graders will check your query results against known answers in the grading script. Therefore do not change the data in any way. They will also look for whether your form behaves responsively to changes in the employee control, order size control, etc.


Return to MIS 333k Syllabus