access assignment

Access - Assignment #4 See Blackboard Due Date

Download the Access database from Blackboard “AccessData Project4”. The database is made up of 8 tables: Categories, Customers, Employees, Orders, Order Details, and Products, Shippers and Suppliers. Relationships are already set between the tables. Spend some time browsing through the tables to better understand the data that it stores. For this assignment you will design 6 reports. For each report, pay attention to details. Each report (except the mailing labels) should have a heading that identifies the Company Name (UT Eats & Treats, Limited) and a report title (as indicated in each report description). For example, the second report should have the following Report Headers: UT Eats & Treats, Limited Products by Category Also, pay attention to details – if you use the wizard, in most cases, you will still need to go into design mode to “clean up” the report. Align totals, move labels adjacent to summary lines of data, add line numbers, etc. Generate the following reports. Print the FIRST and LAST page of each report to submit for this assignment.

1) Products by Category. List the products: Category, Product ID, Product Name, Supplier, Qty per unit and unit price. Sort the data by Category.

2) Customer Listing, grouped by country and region. For each customer list the Customer ID, the Company Name, Contact Name, Contact Title, Address, City, Region and Postal Code, Phone and Fax Number. Sort the data by Customer ID. Print the report in Portrait mode. Use multiple lines in the detail section to allow all the data to fit to the page. Make sure the column headings are descriptive (for example, if you move the Fax Number to print under the Phone number, the column heading should read: “Phone/Fax Numbers”. Also, add a line counter to the detail print line. Reset the counter for each new Region.

3) Shipping Costs Report. Generate a report that summarizes the freight costs for all orders by Shipper. The report should list the Shipper ID, the Shipper Company Name, the Shipper Phone Number and then provide the Sum, Avg, Min and Max of the freight costs by Shipper. (Use data from the Shipper and Orders tables. View the data by Shippers. Print only the summary data – no details. Layout the report so that it is similar to the following:

UT Eats & Treats, Limited Shipping Costs Report

Shipper ID Company Name Phone Sum Avg Min Max 1 Speedy Express xxx-xxx-xxxx 999.99 999.99 999.99 999.99 2 United Package xxx-xxx-xxxx 999.99 999.99 999.99 999.99 3 Federal Shipping xxx-xxx-xxxx 999.99 999.99 999.99 999.99 Grand Totals 999.99 999.99 999.99 999.99

4) Orders by Customer. Generate a report that lists Customer ID, Company Name, Order Id, Order Date, Shipped

Date, Product ID, Product Name and Product Unit Price. Choose to display (View) the data by Customer. No summary options are necessary.

5) Products on Order. Generate a report that lists the Products on order (based upon data from the Order Details).

Print the Product ID, Product Name, the Supplier’s Company Name, the Category Name, QuantityPerUnit, UnitsInStock, UnitsOnOrder, Order ID, Customer.Company Name, and Quantity Ordered. Display the data by Product. Rather than use the ID’s from the product table, use the descriptive data from the related tables (Supplier Company Name, Category Name and Customer Company Name). Make sure the labels accurately and descriptively identify the data (for example use “Supplier Name” – rather than the default “Company Name”.) Total the data by Quantity on order.

6) Name each report in your Access Database so that it is easy for me to determine which report you attempted. Post the completed database back to the assignment link on Blackboard. Print the FIRST and LAST page of each report, organize the prints as numbered above and attach them to this grading rubric.

Access Assignment 4 - Grading Sheet See Blackboard Due Date

Name: ____________________________________ Score: _______ Reports will be graded based upon the quality of the design (does the report look good!) and on how well the design matches the specifications given.

You must include only the FIRST and LAST page for each of your reports. Please order your printouts to match the grading sheet below. Points Earned

Category

Description

______(2) Report Mailing labels for suppliers

______(3) Report Products by Category (Sorted by Category)

______(4)

Report Customer Listing: Group by country and region multiple lines in the detail section add a line counter by region

______(4)

Report Shipping Costs Report Print only the summary data – no details Clear layout… Shipper ID Company Name Phone Sum Avg Min Max 1 Speedy Express xxx-xxx-xxxx 999.99 999.99 999.99 999.99

______(4)

Report Orders by Customer List Customer ID, Company Name, Order Id, Order Date, Shipped Date, Product ID, Product Name and Product Unit Price. Display by customer

______(4)

Report Products On Order Total the data by Quantity on order.

______(4)

Organization

Reports named and easily identified in Access database. Print outs organized and ordered 1 – 6, per assignment listing.

______ - Late Deduction

If late, 2.5 point per class period late deducted

______(25)

Total Points Earned

For full credit -- you must include PDFs of the FIRST and LAST page for each of your reports!