1 / 20

BUS 206 Access Assignment

BUS 206 Access Assignment. Hints on the DB Assignment Case 3 The Sweet Snacks Database (Lab textbook P.64 – P. 69) 2013 Term 1. Introduction. A database keeps track of pieces of cookies and sweets that are sold and delivered to students at the local university

blake-noble
Download Presentation

BUS 206 Access Assignment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BUS 206 Access Assignment Hints on the DB Assignment Case 3 The Sweet Snacks Database (Lab textbook P.64 – P. 69) 2013 Term 1

  2. Introduction • A database keeps track of pieces of cookies and sweets that are sold and delivered to students at the local university • Please read p.64 and p.65 of the Lab textbook for background information of the assignment.

  3. Deliverables (1) • Refer to Lab textbook P.64 – P. 69 • You need to build 4 databasetables and populate them with data. • You need to construct 1 Form, 7 query, and 1 report. • Refer to p.69, items 2, 3, 4, 5, 6, 7, 8, 9, 10, and 11 are needed for the assignment. • Make sure you understand the material covered in tutorial A and tutorial B (P.3-P.50) of the lab textbook in order to work on the assignment.

  4. Details of the deliverables (2) Refer to page 69 of the Lab textbook 2. Tables created in Access 3. Form and subform: Orders 4. Query 1: Chocolate Products 5. Query 2: Orders At or After 11pm 6. Query 3: Products by Price 7. Query 4: Updated Prices 8. Query 5: Popular Products 9. Query 6: Delivery Time 10. Query 7: For Report 11. Report: Today’s Sales

  5. Database Design Tables involve: • Customers table: data input manually • Order Line Item table: data input manually • Orders tables: data input manually • Products table : data input manually

  6. Customers Table (1)

  7. CustomersData (1) primary key

  8. Order Line Item Table (2)

  9. Order Line Item Data (2) Compound key Unique combination of a compound key

  10. Orders Table (3)

  11. Orders Data (3) primary key

  12. Products Table (4)

  13. Products Data (4) Primary key

  14. Table Relationships • From page 66 Assignment 2A: • Enter 9 customer records. • Enter 10 products records. • Enter 10 orders records. • Enter 17 order line item records.

  15. Relationship (2)

  16. Queries (1) Query 1: It displays the Product Name and Price per Dozen of all products that contain chocolate Query 2: It should filter orders for a specific date and list the Last Name of each customer, their Addresses and Telephone numbers, and the Product Name and Quantity of each product in the order Query 3: It prompts for an upper monetary limit so you can tell budget-minded customers which products cost less than the given amount. The query output should include columns for Product Name and Price per Dozen Query 4: It updates the prices of all products. It decrease each price by 2 percent

  17. Queries (2) Query 5: It lists the number of products sold on a specific date. The output should include columns for Product Name and Number Ordered. Sort the output so the most popular product is listed first and the least popular product is shown last Query 6: It finds the average delivery time in minutes for all orders on a specific date. Display columns for Date Ordered and Length of Delivery Time in Minutes Query 7: It should display columns for Last Name, First Name, Address, Product Name, Quantity, and Total Price, which is a calculated field

  18. Form and subform: Orders

  19. Report: Today’s Sales

  20. The End and Have Fun!

More Related