Your task in this assignment is to modify the Orders database provided to add the following functionality: 1.  

Your task in this assignment is to modify the Orders database provided to add the following functionality:

1.       Add a table to the database called Order Details. This table will hold details regarding each order. Each record in Orders can potentially link to many OrderDetails records. You should create the following fields for the OrderDetails table, 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 (some orders should have 2 or 3 order details records). 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 first and last name of every customer with an address in California. Save this query as “Query One”.

5.       Write a query that returns the name and city for every customer that placed an order that shipped after Jan 1 2016 and who paid via credit card.

6.       Write a report that displays all the customers in the system. Sort the customers into alphabetical order by last name, then first name. Whenever the last name changes its first letter, there should be a page break, so customers starting with “A” are on one page, “B” on the next page etc. The end of the report should show a total count of the customers in the report. This report should be in portrait mode. 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. This report should be in landscape mode. Name this report “Customer Orders”.

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 letter sized paper.

Notes:

·         All queries and reports should continue to 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). ^*If you use queries to build your reports, please label the queries “report 1”, “report 2” etc.

·         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).