1. Download the ValleProducts.accdb database. Save the file as ValleeProducts_LastNameFirstName.accdb
2. Create a new table named Orders using the table design below. You may create your table in design view or datasheet view. Save the Orders table as Orders Your Name.
3. Define a one-to-many relationship between the Customer table and Orders table. Right click in the window and select Show table. Select the referential integrity option and both cascade options for the relationships. Save the relationship.
4. Create a query based on Customer table that shows CustomerNum, CustomerName, Street, City, and State for all customers in Grand Rapids, South Bend, and Toledo. This will be a List-of-Values query.
5. Create a query based on the Coffee Table that displaysall four fields. Show only those records that do not have the following Coffee Types: African, Blended, and Flavored.
6. Create a query based on the Product Table that shows all Decaf products that have a WeightCode field value of A, or whose Price field value is less than 30. Display all fields from the Product table. Open the Product table and look in design view if you need information about how Decaf is listed there. Remember to indicate Decaf in both rows. Hint: Look in design view of the Product table to see how Decaf is displayed. You must be sure to use the OR operator for A or < 30 which means those two criteria must be on two different rows.
7. Create a new query based on the Product table. Use aggregate functions to find the lowest, average and highest values in the Price field. Name the fields: Lowest Price, Average Price and Highest Price, respectively.
8. Create a new query based on the Product table. Use aggregate functions to find the lowest, average and highest values in the Price field by WeightCode. Name the fields: Lowest Price, Average Price and Highest Price, respectively.
9. Create a parameter query based on the Customer tablethat prompts for customers in a specific state. Do not just make a select query. A parameter is different. Display the CustomerNum, CustomerName, City, and State fields.
10. Create a query based on the Order table and display all fields. Show the record that has a billing Date equal to 2/15/2012.
11. Create a query based on the Order table and display all fields. Only show records that have not paid. Add a calculated field that will display a late fee of 2.8% based on the Invoice Amount field. Use the Standard format and 2 Decimal places. Rename the new field Late Fee.
12. Create a query based on the Coffee table (include all fields) that shows all records where the Coffee Name ends with Blend.Use a Pattern Match query.
13. Create a form in Design View using the Coffee and Product tables.
a) Place the all fields from the Coffee table under the Detail section. Move them so that they approximate the picture of the form below.
b) Add an appropriate title to the form and add your name to the end of the title.
c) Place a subformunder the Coffee fields on the detail section based on the Product table. Include all fields from the Product table.
d) Click on the subform label (highlighted in yellow below) and type your name after Product. Do not rename the entire subform,just the label.
e) Switch to Layout View and resize the columns in your subform to best fit.
f) Save the form as #14 Form YourName.