WEEK 7 MICROSOFT ACCESS DATABASE HOMEWORK

squashyja
Home>Homework Answsers>Computer Science homework help

Assignment for Week 7

  1. Review chapter 8, 9, & 10 of the textbook.
  2. Do Final Database Project Assignment #4. Due by midnight Eastern Standard Time on Sunday March 6, 2016 
    This is the fourth of the 8 individual assignments that you'll be doing to create your own database.

    In this assignment, you're going to create a series of Select queries. These are not all going to be as simple as they might appear at first glance. I have specified a Field and Sort order for the queries, so please make sure you follow these precisely. They are intended to address specific Access query issues.

    Here's the assignment:

Query # 1
  1. List all active customers. Include the following fields, in the order in which they appear below:

    Customer ID
    Customer Name
    Contact First Name
    Contact Last Name
    Contact Phone

  2. Sort by Customer Name, then by Contact Last Name, then by Contact First Name.
Note: All 3 of these Sorts need to be done at the same time. In other words, the Customer Name field is the primary Sort key. When there are duplicate Customer names, the list will be sorted by the Contact Last Name field, and when there are duplicate last names the list will be sorted by Contact First Name.
  1. Name the query qry#1ActiveCustomers.
Query # 2:
  1. List all customers in territories 1 and 5 who have bought Standard lumber, and all customers in territory 3 who have bought 3+ Ash.
  2. Include the following fields in the query, in the order in which they appear below:

    Customer ID
    Customer Name
    Territory Name
    Sales Rep ID
    Sales Rep Last Name
    Product Code
    Product Description

  3. Sort by Product Description, then by Customer Name, in Ascending order.

  4. Name the query qry#2TerritoriesAndProducts.
Query # 3:
  1. List all customers with the products they have ordered since and including 1/1/03.
  2. Include the following fields in the query, in the order in which they appear below:

    Customer Name
    Product Description
    Product Code
    Quantity Ordered
    Date of Order

  3. Format the Quantity Ordered field to display with commas and zero decimal places.

  4. Sort by Product Description, then by Customer Name, in Ascending order.

  5. Name the query qry#3CustomersAndOrders.
Query #4:
  1. Create a query that calculates the total product cost and the discounted price for all products.
  2. Include the following fields in the query, in the order in which they appear below:

    Product Description
    Product Code
    Customer Name
    Quantity Ordered
    List Price
    Discount
    Base Cost (=Quantity multiplied by the List Price)
    Discounted Cost (=Base Cost minus the Base Cost multiplied by the Discount)

  3. Format the Base Cost and Discounted Cost fields to Currency with 2 decimal places and commas.

  4. Format the Quantity Ordered field to display commas with 0 decimal places.

  5. Format the Discount value to Percent with 1 decimal place.

  6. Sort in Ascending order by Product Description, then by Customer Name.

  7. Name the query qry#4TotalProductCost.

  • 10 years ago
  • 20
Report issue
Answer(0)
Bids(1)