MICROSOFT ACCESS

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

Last Completed Projects