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.
3. Add the three records listed belowto the Orders table.
4. 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.
5. 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.
Save as #5 Cities YourName. Query results will show 8 records.
6. 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.
Save as #6Coffee Types YourName. The query results willshow 13 records.
7. 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.
Save the query as #7 Decaf YourName. Your results should show 35 records.
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. Name the fields: Lowest Price, Average Price and Highest Price, respectively.
Save the query as #8 Prices YourName. Your results will show one row of results: $7.99 $30.36 $234.00
9. 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.
Save the query as #9 Price by Weight YourName. You will have 7 rows of output in this query.
10. 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.
Save the query as #10 StatesYourName. Run the query using a parameter value of OH. You will see only 4 Oh records.
11. 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. Save the query as #11 Date Your Name.
12. 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.
Save the query as #12Late Fee YourName. One of the Late Fees will be $55.61. There will be one other late fee.
13. 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.
Save the query as #13 Blend YourName. Your results will display 6 records.
14. 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.