MGT 20600 Spring 2011 / Assignment 6
Due Date / Check Concourse for the assignment due date.
Total Points / 15

Objectives

/ In this homework you will demonstrate proficiency in the following:
  • Creating relationships between tables
  • Creating queries of the following types: single-table, multiple-table, queries with calculated fields, parameter queries, make-table query, update query, and delete query
  • Creating and editing simple and multi-page forms
  • Creating and editing a report

To Complete The Assignment:

1)Copy the MGT20600_A6_S11.accdb file from your section’s Assignment folder on the Courseware I: drive to your computer’s desktop.

2)NEVER open or work on any files directly through WebFile!!!

3)NEVERopen Access files from the I Drive or any other drive where you do not have write permissions!!!

4)Always enable macros when prompted.

5)When you are finished, save the file and copyit to the location where you keep your assignment backups on the N: Drive. DO NOT DO A SAVE AS! If you’re working on the desktop of a campus computer, make sure that you make another backup copy of the files you just finished onto a USB Flash drive or in another directory on your N: drive. Any data saved on a computer’s desktop in a lab is lost when you log out.

6)Very Important: NEVER open or work on any files directly through WebFile. Work this and all other assignment files from the desktop in a cluster, a personal USB flash drive, or from a designated drive on your personal computer, and make sure that you BACKUP your completed work on your N: Drive.

A. Complete the Documentation Form

  1. Make sure that you have placed the MGT20600_A6_S11.accdb file on your computer’s desktop.
  2. Before opening your file, rename your MGT20600_A6_S11.accdb file to
    NetID-MGT20600_A6_S11.accdb where the prefix is your NetID.
    (Example: littlefi-MGT20600_A6_S11.accdb)
  3. Open the NetID-MGT20600_A6_S11.accdb database file. The first time you open your file, open and complete the Documentation Form. You must enter accurate information. The Documentation form will automatically provide the current date. Close the Documentation form when you are finished entering your information.

B. Create Relationships Between Tables

  1. Refer to the following figure and establish relationships between the appropriate fields in all tables except the Documentation table. Make sure to maintain referential integrity and cascade update related fields.
Figure 1: Table Relationships

C. Action Queries

  1. Create a select query listing all the fields in the CUSTOMERS table. Limit your query to only those premium customers. All premium customers will have a business name listed in BusName, or have a code listed in the Credit Ratingfield, or both. Run the query to verify your results. You should have 405 records. Change the query type to Make Table and name the resulting table PREMIUM CUSTOMERS. Run the query again and make sure that a table called PREMIUM CUSTOMERS actually appears in your table list. Save and name your query MakeTable(no space). (You should have 405 records in the new PREMIUM CUSTOMERS table.)
  1. Using the PREMIUM CUSTOMERS table that you just created, create a select query that lists the BusName of all the businesses that have a listing in BusName, but do not have aCredit Rating code. Run the query to verify your results. Create an update query that replaces the blank Credit Rating field with the capital letter “K”. Save your query as UpdateTable(no space). (30 blank Credit Rating codes should be updated.)
  1. Worm’s Turn doesn’t deal with billing and purchase orders anymore. The standard is credit cards. Create another select query that will list all the fields in PREMIUM CUSTOMERS and show only those customers whose preferred method of payment is Purchase Order and who don’t have a credit card listed. These customers should be deleted from PREMIUM CUSTOMERS. After running the select query to verify your results,change the query type to Delete Query and run it. Save the query as DeleteTable(no space). (PREMIUM CUSTOMERS table should now contain 400 records.)

Please note that the PREMIUM CUSTOMERS table will NOT be used in any other queries in this project.

D. Creating Multiple-Table Queries

For the following queries, you must be able to see the relationships in the query design window. If you do not, you MUST go back to the relationships window and correct this. Be aware also that it is possible that a query may actually result in a dynaset with no records.

  1. The cost of water and electricity has gone up drastically, and therefore, the cost of raising perennial plants to sell as mature plants has risen. Wholesale prices and retail prices for perennials and tender perennials need to be changed. Create a query that lists the Item #, Hardiness Code, Product,and Retail Price of all plants that are Perennials or Tender Perennials and that have a Container Grown stock code designation. Exclude all plants that have a Retail Price between $0.00 and $40.00 (includes $40.00). Sort the query in descending order by Retail Price and save the query as Query1. (The resulting query should have 45 rows, 4 fields showing and should be sorted correctly.)
  1. A forgetful but well-meaning employee needs to contact a customer, but can’t remember who, and did not write it down. You need to find a list of customers who match the description he has given you. He only remembers that it was a customer whomade somewhat large purchasesduring the month of June, 2010. He also recalls from their conversation that the customer’s last name begins with an R. Create a query that lists all customers who made purchases in June2010 and whose last name begins with the letter R. Their total purchases for the month (not including shipping costs and taxes) should exceed $250. List FirstName, LastNameand Telephone Num and sort ascending by LastName. Name the query Query2. (The resulting query should have 1 row, 3 fields showing and should be sorted correctly.)

NOTE: Be careful of the field you use to calculate the customer’s purchases. Remember that Retail Price is the actual selling price. Don’t forget about the quantity.

  1. Create a query named Query3 that will count the number of transaction orders and identify when the first transaction order and when the last transaction order was placed for each product type during the year 2010. Select only the orders that were placed for the sales reps, Nancy Davolio and Andrew Fuller. Display the First Date, Last Date, Order Count, andProduct Type. Sort in descending sequence by Order Count. Hint: There is a difference between the use of MIN and MAX vs. FIRST and LAST. Your first record will list the following field values: 3/3/2010, 9/5/2010, 16, and ECHINACEA. (The resulting query should have 106 rows, 4 fields showing and should be sorted correctly.)
  1. The boss is interested in the revenues generated by customers from Indianaand Illinois for the summer season of 2010. Create three queries that show the total amount spent by customers from Indiana, the total amount spent by customers from Illinois, and the total amount from both states together. Calculate only the revenue for the months of June, July, and August. Use the retail price to calculate their purchases and do not include shipping costs and taxes. Give the resulting field names in your queries the same names as the query names respectively, Indiana Total, Illinois Total, and Both Total. (Indiana Total should be $17,031.02, Illinois Total should be $4,765.20 and the Both Total should be $21,796.22.)

E. Parameter Query

  1. Worm’s Turn would like you to create a query that allows users to enter a Building Code and then be able to view a dynaset that contains the Hardiness, Category, Type, Product,Retail Price and Plant Locationfields. Sort the dynaset in ascending order by Type and then by Product. The prompt in the parameter box should read: Enter a Building Code: Save the query as Parameter Query. Test your query by entering building codes (i.e., GH 1, MA,etc.). (Note: this query will not be automatically graded, but you can do a self check. Run the query. If you have created the query correctly, you should see the prompt Enter a Building Code: appear. Type in the building code GH 2, and your answer should have 24 records displayed with the first record’s product listed as BELGIAN ENDIVE/WITLOOF CHICORY. Remember, you should only see the fields listed as specified above (6 fields). (Entering Building Code GH 1 for the prompt will return 31 records.)

Compact and Repair your Database before proceeding.

F. Creating Forms Based on Queries

Worm’s Turn would like a form that allows the sales associates to view customers’ orders, the details of the orders, and grand totals. We will begin this form by creating the queries and subforms we’ll need to create the final form.

  1. Create a query in design view that lists customer’s ID, Transaction ID, Item #, Product, Quantity, and Retail Price. You should have 2027records in this dynaset. Save the query as Transaction Information, and close the query.
  1. Create a query in design view that lists the customer’s ID,Transaction ID, Delivery, Shipping and 2 calculated fields:
  1. A calculated field named Subtotal that calculates the subtotal of each transaction. (Quantity * Retail Price) Remember, each transaction may have several items; therefore, make sure you get only 1 subtotal for each unique transaction. Sort in ascending order by Transaction ID. You should see each Transaction ID listed only once.
  2. A calculated field named Taxes that calculates the sales tax amount based upon the subtotal and a 5% tax rate. (Quantity * Retail Price)*.05 You will need to apply the same aggregate function here as in (a) above. Be sure to format these taxes as Currency.

You should have 1002 records in this dynaset. The subtotal for Transaction ID 612 should be $379.22 and the tax amount should be $18.96. Save the query and name it Transaction Calculations. Close the query.

  1. Create a new Form in Design View and select the query Transaction Calculations as the record source for this form on the Property Sheet. Add all existing fields from the Transaction Calculations query on the form. Add the following adjustments:
  1. Make the form itself about 4” wide.
  2. Move the Delivery label to the left of the checkbox.
  3. Make all label text bold. Align the labels right, and set the label text right-aligned as well.
  4. Set the Visible properties for Transaction ID and Customer ID to No.
  5. Align the text box controls as shown in Figure 2.
  6. Add a calculated field to this form that adds Subtotal, Taxes and Shipping together. Format the control as currency. This calculated field’s label should readGrand Total with Shipping and Taxesand should be bold and right aligned.
  7. Adjust ALL form controls so that all fields with visible properties not set to NO are properly displayed in form view.
  8. Save the form as Transaction Calculations Form and close the form.

Compact and Repair your Database before proceeding.

Figure 2: Transaction Calculations Form

G. Creating Multipage Forms

  1. Create a new form in Design View based upon the Customers Table. Add the first three fields of the table to the form (ID, LastName, and FirstName). Save the form as Transactions Form. Arrange and align the controls as shown in Figure 3 below.
    In addition:
  2. Re-size the form to 5.5 inches long and 9.5 inches wide.
  3. Change the ID field’s label to read Customer ID:.
  4. Make all label and control text bold, the font size 10, and adjust the width of the controls so that all text can be read.

Figure 3: Transactions Form before adding Tab Control and Header

  1. Add a Title control (form header) to the form and hide the form footer.
  2. Insert a label in the header containing the form title Worm’s Turn Sales Information. Make the label font color Green 5. Make the font bold and size the font to 24 pt. Center the control as best as you can, and center the text within the control as well.
  3. Add a tab control to the form. Adjust the size of the tab control to match the form as shown below in Figure 4.
  1. Change the caption property for the first page of the tab control to read Purchase Details.
  1. Change the caption property for the second page of the tab control to read Payment Details and Shipping Information.
  1. On the Purchase Details page of the tab control create a subform based on the Transaction Information query you designed at the beginning of this project. Select all fields and make sure you show Transaction Information for each record using ID as the connecting field (make sure that the radio button Choose from a list is active, and do NOT choose Define my own.) Name the subformusing the name provided by Access.
  2. In form view, one by one, select the customer’s ID and Transaction ID fields and hide them by right clicking and choosing Hide Columns. Adjust the subform so that at least 12 records can be viewed at once. Format the Retail Price as currency if necessary. Make all columns wide enough to read all of the entries and be sure that the Product column width is set to at least 75 to accommodate the longest entry. (Note: You can change this column width by right-clicking on the column while in Datasheet View.) Delete the default label control that names the subform; it is unnecessary. Examine Figure 4 below to help you check your work.

Figure 4: Finished Form, Tab 1 View

  1. Add a subform to the second page of the tab control based on the Transaction CalculationsForm you created earlier. Show Transaction Calculation for each record in <SQL Statement> using ID. Change the caption of the subform label to read Payment Details with 16 point bold font and change its background color to Green 4. Adjust the form to look as much like the illustration below (Figure 5) as possible.
  1. Show the field list for the Customers table (upon which your main form is based) and add the following controls to the right of the Payment Details: Address1, Address2, City, State, Zip Code,and Telephone. See the illustration in Figure 5 below; you will need to surround this area with a border by using the Rectangle Control tool on the Design tab, and include a label that reads Shipping Information, formatted exactly as the subform label in step 10. Adjust all controls to look as close as possible to the illustration.
  1. Save and close your multipage form. Be sure to name the form Transactions Form if you have not already done so.

Compact and Repair your Database before proceeding.

Figure 5: Finished Form, Tab 2 View

H. Create a Summary Report

The Worm’s Turn sales staff would like a summary report that summarizes sales for their 2009 tax year. This report will be based on the following query:

Create a query in design view that lists the Type, Category, and a calculated field called TOTAL that calculates the total revenue for each type of product during the fiscal year starting September 1st, 2009 and ending on August 31st, 2010. You should have 259 records in this dynaset. Save this query as 2009 Fiscal Sales.

  1. Using the Report Wizard, create the new report based on the 2009 Fiscal Sales query you just created. You do not need to print out the report when you have completed it. If you get an error messagesaying that you cannot create a report because you do not have a printer installed, go to , download and install any printer driver. Access will assume that you then have a printer and will allow you to build a report.
  1. Review Figures 6and 7 and design the report using the following specifications:

a)Use all the fields from the query in the sequence that they appear in the figure.

b)Group and sort the report on the appropriate fields.

c)The report should have a stepped layout, have a portrait orientation, and use the Concourse style.

d)Save and name your report 2009 Fiscal Sales.

  1. Adjust the format of the control in the Category Header area of your report design so that all categories appear in upper case. (What symbol do you place in the format property to accomplish this?)
  2. Adjust column widths so that all field content is visible and similar to Figure 6 and 7.
  1. Center the label containing the title of the report.
  1. Move the control containing the date in the page footer and center it under the title of the report in the report header. Change the control’s font color to black.
  1. Add a label containing your NetID to the lower left side of the page footer where the date used to be.
  2. Add a control that calculates the subtotal of revenue for each Category. Format the control as currency with bold font. The corresponding label should read Subtotal:.
  1. Place a horizontal line above the text box control for Subtotal and change its border style to solid.
  1. Add a control that calculates the grand total paid by everyone at the bottom of the report. Format the control as currency with bold font. The corresponding label should read Grand Total:.
  2. Place two aligned horizontal lines below the text box control for Grand Total:.
  1. Make sure that all headers can be viewed completely; adjust as necessary.
  1. Align the data and the controls that contain the data in such a way as to look as nearly like the example as possible. Should your report contain more than 14 pages with blank pages in between, your report is too wide. To correct this, in design view, narrow the width of the report page. (You might have to move some of your controls in order to narrow the page width.)

Compact and Repair your Database before proceeding.