MANAGEMENT INFORMATION SYSTEMS

MICROSOFT ACCESS ASSIGNMENT

Your task in this assignment is to modify the Orders database available on Sakai to add the following functionality:

1. Add a table to the database called Order Details. This table will hold details regarding each order, such as the products ordered and the quantity and price for each. Each record in Orders can potentially link to many OrderDetails records. You should create the following fields, making sure you choose data types that suit each field:

a. OrderDetailsID is the primary key of the table (use autonumber)

b. An OrderID field that will be the foreign key that links to the Orders table (make sure you match the data types, i.e number to autonumber)

c. ProductName

d. Quantity

e. UnitPrice

f. Status (a text field that will have one of the following values: Invoiced, Shipped, or Closed)

g. PurchaseOrderNo

2. Fill the OrderDetails table with random data, making sure you have at least one OrderDetails record for each record in Orders (at least some should have 2 or 3 or more). The values in the OrderDetails.OrderID field must match those in Orders.OrderID.

3. Link the OrderDetails table to the Orders table:

a. The relationship is one-to-many. Each OrderDetails record links to exactly one Orders record, but one Order can be associated with many OrderDetails records.

b. Link the tables together in the relationships window.

4. Write a query that returns the name and company of every customer whose last name ends with "sen". Save this query as "Query One".

5. Write a query that returns the customer name for every order placed between March 1^st and May 1^st, 2006. Call the query "Query 2" and save it.

6. Write a report that displays all the customers in the system. Sort the customers into alphabetical order by last name, then first name. The end of the report should show a total count of the customers in the report. Name this report "Customer List" and save it.

7. Write a report that lists the orders for each customer. List the customer first/last name on one line, and then underneath that there should be one details line per order. Each details line should list the order date and full shipping address (street/city/state/zip). These details lines should appear in descending order by order date.

Requirements for both reports:

• Each report must have a descriptive header including an appropriate title.
• Each report should have page numbers and the report title in their footer lines.
• Use fonts, layout and colors to customize the look of the report. Assume the reports would be printed on landscape, letter sized paper.

Notes:

• All queries and reports should work correctly regardless of whether records are added or deleted from the associated tables.
• Make sure you delete all draft attempts: your database should include only 2 queries and 2 reports when submitted (and the one extra table).
• Make sure that your queries/reports are saved inside the database, do not export / save them as separate files. You should submit a single Microsoft Access file as your assignment (an Access 2007 or later file has the extension .accdb, an Access 2003 or earlier file has the extension .mdb).
• You must submit your completed MS Access database as an electronic file to Sakai by the date due. Please DO NOT email me the assignment - our firewall will not allow the file through (plus my email quota isn't up to it).

You may work individually, or in a group of no more than two people.