Microsoft Access Project
ACCESS 2010 EXAM INSTRUCTION SHEET
ISM 3011-007 FALL 2013
You have to follow this instruction to properly save and upload your file. You are entirely responsible for any corrupt or missing content file if you fail to comply with this requirement.
Download and save Access 2010 Exam Fall 2013.xlsx to your computer. You will do all your work on this file and then upload the file when finished, using the name Access Exam Fall 2013 (Your first and last name).xlsx. You will have one hour and thirty minutes to do this exam.
The exam is open book-open note. The maximum grade is 100. Good Luck!
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. (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
5. (20 points) Create a query named Average Tire Price
a.Use the fields: Tire Type, Unit Price, and Part ID from the Tires table.
b.By using an aggregate function group by Tire Type.
c.Sort ascending order by Tire Type.
d.Calculate the Sum of the Unit Price and label the column “Price Sum”.
e.Calculate the Count of the Part ID and label the column “Product Count”.
f.RUN and save the query.
g.Adjust the fields in the datasheet view to display the complete field names and records.
.
6.( 24 points) Create a second query named Customer Revenue
a.Add these fields from Customers table: Last Name and City. Add these names from the Tires table: Unit Price and Tire Type.
b.Sort Tire Type in ascending order.
c.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.
d.INCLUDE customers that reside in the cities of Upland, Ontario and Claremont.
e.Don’t show the Tire Type field in the query.
f.RUN and save the query.
.7.(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..
8.(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.
