Assignment Due - PS3 due - Graphical User Interfaces

Information Technology I Problem Set 3 GUIs: Extending a Microsoft Access application Due: Thursday, April 3, 2003 Important Note: You are encouraged to work in pairs on this assignment. If you do, the same grade will be given to both members of the pair. You may choose to work alone, but you will be expected to do the same amount of work as the pairs. Do not leave this assignment for the last minute! NORTHWIND TRADERS EXECUTIVE SUMMARY In this assignment, you will extend the functionality of the Northwind Traders application we have been using so far to illustrate the capabilities of Microsoft Access. More specifically, you will create some new queries and forms, which provide an executive summary of the company’s performance in a given year. As usual, you will each make a private copy of the file nwind.mdb, which can be downloaded from the class server. You will then work on your private copy of the database, making additions and modifications as needed for this assignment. What to turn in: Compress the final version of your nwind.mdb database into a .zip file. Name that file after your last names. For example, if your last names are Smith and Jones then your file should be called “SmithJones.zip”. Email the .zip file to the TA before the start of class on April 3. THE ASSIGNMENT The Northwind Traders application provides an extensive collection of Forms that are used during the day-to-day operations of the firm. You can click the icon “Form” in the database window and double click on some of the forms provided by the application to become more familiar with their capabilities. Here are some examples of interesting forms you will find: Categories Customers Employees Orders product categories and associated products list of customers list of employees with short bio and picture detailed list of all orders on file Although forms such as these are essential in order to support the day-to-day operations of the firm, they are not very useful for providing top management with summary information that helps it understand how the company is doing, and where the focus of its activities lies. In this problem, you will design an “Executive Summary” form that provides a concise summary of the firm’s activities and results during a given calendar year. The Executive Summary form will help management get concise answers to questions such as: • • • • What were our total sales per quarter? Which are our highest revenue generating products? Who are our largest customers? Which countries are our most important markets? Here’s the information that your form should contain: • • • • • A summary of total sales per quarter in graphical format A list of products sorted by the total order revenue they generated during the year A list of customers ordered by the total value of their orders during the year A list of countries sorted by the total value of orders shipped to each during the year Buttons that point to existing forms that contain more information about products and customers. Clicking these buttons should open the respective forms. It should be possible to specify the year for which the summary will be calculated as a parameter. In order to enable easy comparisons between years, once the form is created for one year, it should be easy to recalculate it for another year in situ. Here is a sample (but by no means the only) layout of a form that satisfies the previous requirements. This screenshot is from the Microsoft Access software program. DETAILED INSTRUCTIONS Here’s a step-by-step guide for creating an “Executive Summary” form. Phase A: Create the Queries (30%) Your first task is to create all the necessary queries that summarize the firm’s activity in a given year. To make things simpler, do not worry about parameterizing the queries yet. Make all queries calculate their results for the year 1995. Step A.1: Create an “Extended Order Details” Query The Northwind Traders database contains two tables which store details about customer orders: • • Table “Orders” contains “fixed” information about each order, such as customer name and address, relevant dates (ordered, required, shipped), freight costs, etc. For each record in the “Orders” table, table “Order Details” contains one or more records describing each line item in the order. Each order detail record contains information such as the product id, unit price, quantity ordered, and discount extended. Order detail records are linked to their corresponding order record through the Order ID field, which appears in both tables. You will summarize the firm’s performance by looking at all its order details in a given year (initially 1995). To do that, you first need to create an “Extended Order Details” table that will contain all order details for all orders placed in 1995. For each order detail, you will need to include at least the following information: Product Id Ship Country Customer Id Order Date Extended Price to calculate our most revenue-generating products to calculate our most important markets to calculate our largest customers to select orders placed in a given year (and also to calculate the totals per quarter) to calculate the total value of orders per quarter and per year. The extended price for an order item can be calculated from the formula: [Extended Price] = [Unit Price]*[Quantity]*(1-[Discount]) Create a query that builds such an extended order details table. Your query extends the information found in each row of table “Order Details”, by additional information found in the corresponding row of table “Orders”. It also selects only the order detail records that correspond to queries placed in 1995. Save your query as “PS3 Extended Order Details” Step A.2: Create a “Revenues Per Product” Query This query will summarize information found in the “PS3 Extended Order Details” table in order to generate a sorted list of the highest revenue-generating products in a given year. The list should be showing the highest revenue-generating products at the top and the lowest revenue-generating products at the bottom. Each row in the list will contain the following two fields: Product Name Order Total the English name of the product the total dollar amount of all orders for this product in the given period Save your query as “PS3 Product Revenues” Step A.3: Create a “Revenues per Customer” Query In a manner very similar to the previous step, you can create a query that generates a sorted list of the firm’s best customers. Each row in the list will contain the following fields: Customer Name Order Total the company name for the customer the total dollar amount of all orders from this customer in the given period Save your query as “PS3 Customer Revenues” Step A.4: Create a “Revenues per Country” Query This query will generate a sorted list of the firm’s most important markets. Each row in the list will contain the following fields: Country Order Total the name of the country the total dollar amount of all orders shipped to this country in the given period Save your query as “PS3 Country Revenues”. Step A.5: Create a “Totals per Quarter” Query This query will summarize the total value of all orders placed in each quarter of 1995. It will generate a table with four rows that should look like this (the actual amounts will differ): Quarter Q1 Q2 Q3 Q4 Total Orders 34567.89 32578.70 55444.56 87023.78 <-- total orders placed between 1/1/95 and 3/31/95 <-- total orders placed between 4/1/95 and 6/30/95 <-- total orders placed between 7/1/95 and 9/30/95 <-- total orders placed between 10/1/95 and 12/31/95 This query can be built as follows: 1. Create four SQL queries. Each of your queries should generate one of the four rows of the above table. For example, your first query should calculate the total orders placed between 1/1/95 and 3/31/95 and generate a one-line output such as: Quarter Q1 Total Orders 34567.89 These are fairly easy queries that select the relevant rows of “PS3 Extended Order Details” and then sum up the values of field “Extended Price”. The only difference among the four queries lies in the range of order dates used to select records. 2. Combine all four queries together by using the UNION statement of SQL. The syntax for doing this is the following: for second query*> for third query*> for fourth query*> Of all queries created for this problem, this is the only query that must be written in SQL. All other queries can be created directly using Microsoft Access’s query design capability. Save your query as “PS3 Quarterly Totals”. Phase 2: Design the Form (30%) After having created queries that generate executive summary data, in this step you will create a form that displays it in a concise and (hopefully) attractive manner. Step B.1: Design the Subforms Your form will display information from several different queries. In order to display information from several tables or queries in a single Microsoft Access form, you first have to define a subform for each of the tables and then embed all subforms inside your main form. The queries for which you need to design subforms are the following: PS3 Product Revenues PS3 Customer Revenues * Omit the final semicolon (;) that usually goes at the end of an SQL query. Problem Set 3 PS3 Country Revenues In order to create a subform for each of the previous three queries, do the following: 1. Click icon “Form” to get to the list of forms and then click “New” 2. Click on "Autoform: Datasheet" 3. Select the query from the drop-down labeled "Choose the table or query where the object's data comes from" and click OK 4. Save the form created in the previous step as “PS3 Product Subform”, “PS3 Customer Subform” and “PS3 Country Subform” respectively Step B.2: Design the Executive Summary Form You are now ready to design the main executive summary form. Do the following: 1. Click icon “Form” to get to the list of forms and then click “New” 2. Click on “Design View” and then click OK. Leave the field “Choose the table or query where the object’s data comes from” blank. The main executive form does not display data from any single table or query, but rather acts as a “container” for summarizing data from several tables and queries. 3. Design the initial layout of the form. Using the Toolbox place the form title and other text labels which you want to appear in your form. 4. Create a graph that displays the total sales per quarter. From the Insert menu, select "Chart..." and draw the chart object in your form. A Chart Wizard dialog box will appear. The Wizard will walk you through the creation of this graph. The first question you will be asked by the Chart Wizard is “Which table or query would you like to use to create your chart?”. Answer “PS3 Quarterly Totals” (a query). The other questions should be easy for you to answer yourself. After you have finished answering all questions, the Chart Wizard will automatically create the graph and place it in your form. You can then move and resize it, like any other form control. The chart may not display properly in design view, so switch to form view to verify your formatting. 5. Insert the subforms you created in Step B.1 into your Executive Summary form. Go to the list of forms in the database window and drag each of the subforms into the form you are designing. Then move and resize them to fit your form layout. Each subform will have its own automatically created text label, initially set to the name of the query on which each subform is based. Feel free to change the text in those labels to something more descriptive. 6. Create buttons that allow users to take a detailed look at the list of customers and products (by opening the respective forms). To create each button, do the following: Using the Toolbox, place a command button in your form. A Command Button Wizard dialog box will appear. The Wizard first asks for the kind of action that will happen when the button is pressed. Select “Form Operations” and “Open Form”. Then continue answering the Wizard’s questions. After you have finished answering all questions, the Wizard will create the button and will automatically make it perform the desired action when pressed. 7. Save the form as "PS3 Executive Summary" Phase 3: Parameterize the Form (30%) The form that you created in Phase 2 produces a summary of the firm’s operation and performance for the year 1995. Our next task will be to parameterize the form, so that it can display performance summary information for any year specified by the user. That way, managers can quickly compare the performance of the company with that of previous years. Here’s what we propose that you do: Step C.1: Create a parameter control On your executive summary form, create a control that allows the user to specify the year for which the summary will be displayed. This control can be either a text box, or, better still, a list box or a combo box. Go to the properties window for your control (it appears when you double-click the control) and set the Name of your control to “Year” and its Default Value to 1995. If your control is a list box or a combo box, set the property “Row Source” to 1994;1995;1996. For list boxes and combo boxes, property “Row Source” lists the allowable values. In our case, the allowable values are determined by the set of years for which data exists in our sample database. These properties may also be set through a Wizard. Step C.2: Parameterize your queries In Phase A, you designed all queries so that they generate their data for the year 1995. In this step, you will modify them so that they generate their date for the year that is currently stored in the “Year” control created in the previous step. Here are the queries that you need to modify: 1. PS3 Extended Order Details. This query (among other things) selects all rows of table “Order Details” for which the corresponding “Order” record has “Order Date” between 1/1/1995 and 12/31/1995. In this step you will replace references to the year 1995 in the query, with references to the “Year” control that you created in step C.1. In order to reference the “Year” control from somewhere else in a Microsoft Access application, you should use the syntax Forms![PS3 Executive Summary]![Year] (i.e., the prefix Forms! followed by the name of the form, followed by the name of the control). Here is a sample query that selects records with “Order Date” in 1995: SELECT * FROM [PS3 Extended Order Details] WHERE [PS3 Extended Order Details].[Order Date] BETWEEN #1/1/95# AND #12/31/95#; Here’s how this query should be changed to select records with “Order Date” in the year currently stored in the “Year” control: SELECT * FROM [PS3 Extended Order Details] Problem Set 3 WHERE Year([PS3 Extended Order Details].[Order Date]) = Forms![PS3 Executive Summary]![Year]; Go to the SQL view of query “PS3 Extended Order Details” and perform a similar transformation. To test your revised query, you should have the Executive Summary form open in form view. Otherwise there is no value assigned to Forms![PS3 Executive Summary]![Year]. If you run the query with the form closed, you will be prompted to enter a year manually. 2. PS3 Quarterly Totals. Each of the four subqueries of this UNION query selects rows from query “PS3 Extended Order Details” where “Order Date” falls in their corresponding quarter of 1995. Now that “PS3 Extended Order Details” is parameterized, you need to modify "PS3 Quarterly Totals" so that it no longer refers to hard-coded dates. Go to the SQL view of the query and transform it so that it no longer refers to a specific year. (HINT: You can define each quarter just by specifying the months in that quarter. You may need to use the Month function, which looks like Month([PS3 Extended Order Details].[Order Date])). Step C.3: Synchronize form updates This step will synchronize the various elements of your form and will make the form automatically redisplay the correct data every time the user changes the year of interest. The data displayed on the executive summary form are the results of running a number of queries. When the form is initially opened, the “Year” control has the default value 1995, and all queries are calculated for this year. In order to make your form truly interactive, you must: 1. give the form the capability to detect when the user changes the value of the “Year” control, and 2. recalculate all queries for the new year when this happens. This step will require you to write a simple macro. In order to refresh your form with new data for the year of interest, queries “PS3 Product Revenues”, “PS3 Customer Revenues”, and “PS3 Country Revenues” must be re-executed. Note that it is the queries, not the subforms, that are re-executed. In addition, the quarterly totals graph must be redrawn. Microsoft Access provides the macro action “Requery” to force the recalculation of queries and controls. Write a macro that uses “Requery” actions in order to recalculate the necessary queries and controls. Name your macro “PS3 Recalculate” Attach your macro to property “After Update” of your “Year” control. The “Update” event is signaled every time the contents of a text, combo, or list box change. Therefore, by attaching your macro to the “After Update” property, you are ensuring that it will be executed every time the user changes the value of the “Year” control. With this simple link, you have automated the regeneration of your executive summary for a year of the user’s choice. Phase D: Make your Form look nice (10%) By now your form should be fully functional. In this phase, try to use Microsoft Access’s capabilities for making your form look nice. Here are some of the things you can try: • • • • Changing the foreground and background colors of your form Changing the font size of the various text labels Inserting decorative lines Inserting the “Northwind Traders” logo at the top of the form (you can copy and paste it from form “Main Switchboard” We will give extra credit for exceptionally well-designed forms. NOTE: The sample database we are using in this exercise only contains data for the period between the third quarter of 1994 and the second quarter of 1996. Therefore, you will only be able to get meaningful executive summaries for years between 1994 and 1996.