Tires and the Customers tables

1. (7.5 points) Create a one-to-many relationship between the Tires and the Customers tables. Enforce Referential Integrity and Cascade Update Related Fields.
2. (7.5) Create a one –to –many relationship between Tires and Cars tables. Enforce Referential Integrity and Cascade Update Related Fields.
3. (8 pts) Open the Customers table. Change the Data Types, Field Sizes, and Formats to the BEST choices for each of the following FOUR fields. LOOK AT THE DATA IN THE DATASHEET VIEW to decide your choice (assume that all the data entered will be the same type and length)

Customer ID Quantity

Invoice Date (change to medium date)

Invoice Paid

4. 4. (6 points) Make the following changes to the Customers table.

a. Insert a new field between Part ID and Year, called PRICE. Put in the appropriate data type and specify 2 decimal places

b. Make first and last name required fields

(

20 5. (20 points) Create a query named Average Tire Price

1. 1. Use the fields: Tire Type, Unit Price, and Part ID from the Tires table
   2. By using an aggregate function group by Tire Type
   3. Sort ascending order by Tire Type
   4. Calculate the Sum of the Unit Price and label the column “Price Sum”
   5. Calculate the Count of the Part ID and label the column “Product Count”
   6. RUN and save the query
   7. Adjust the fields in the datasheet view to display the complete field names and records

1. ( 24 points) Create a second query named Customer Revenue
   1. Add these fields from Customers table: Last Name and City. Add these names from the Tires table: Unit Price and Tire Type
   2. Sort Tire Type in ascending order
   3. Create a new field/column, labeled Tire Cost in this query. Make Tire Cost equal to the Quantity field multiplied by the Unit Price field
   4. INCLUDE customers that reside in the cities of Upland, Ontario and Claremont
   5. Don’t show the Tire Type field in the query
   6. RUN and save the query
2. (12 points) Open the Customer List report and add Conditional Formatting to the report. For all Customer IDs less than 50245 make the font Red and bolded.
3. (15 points) Create a form and subform. Select all the field from the Tires table. From the Cars table, select Car ID, Make and Model. Use all the defaults for the layout and grouping. Once the form and subform are created, do a search on the Part ID field for “P475X”. Save the result