Excel

Access Exam

INSTRUCTIONS

a. Open Access. Create a database with the name ACCESS Test_YourLastName.

b. Create a table named Vendors that includes the data below. Choose VendorID as the primary key.

c. Import the Toys.xlsx file from Excel into the database. Choose Stock Number as the primary key. If the import doesn’t work, then copy the data to create the table.

Modify the Table(s)

Create a caption for all fields with two names that uses a space between the two words (e.g., the VendorID caption should be Vendor ID ).

Open the Toys table in design view. Verify the information below. Make any changes necessary. Save the structure of the Toys table.

Field Name	Data Type	Field Size
Stock Number	Short Text	10
Description	Short Text	20
Inventory	Number	Integer
Cost	Currency
Selling Price	Currency
Vendor ID	Short Text	4

Create a Relationship

Create a one-to-many relationship between the Toys table and the Vendors table. Enforce referential integrity. Save the relationship.

Create and Modify Data in a Form

Create a form based on the Toys table. Add the following two records:

06598726 05987563

Cat Stuffed Animal Horse Puzzle

10 9

12.00 11.00

16.00 15.00

WT36 TT96

Change the selling price of the Colored Shape Blocks to $90.00 and the Sports Car $25.00.

Creating Queries

Create the following queries:

a. Use the Simple Query Wizard to create a new query. Include the Stock Number, Description, Inventory, Cost, and Selling Price for all records. Save the query as Query_1. Hide the Stock Number field in the query results.

b. Create a query in design view. Display the Stock Number, Description, Selling Price, and Vendor ID for all products whose Vendor ID is GT36. Save the query as Query_2.

c. Create a query in design view. Display the Stock Number, Description, Inventory, Cost and Selling Price for all items where the Description includes the word “Puzzle”. Save the query as Query_3.

d. Display all fields for those items with a Selling Price $40 or less, and the number in inventory is 10 or fewer. Save the query as Query_4.

Creating Reports

Create a report using the Report Wizard. Use the Vendor Name from the Vendors table, and Stock Number, Description, Inventory, Cost, and Selling Price from the Toys table. View the data by Vendor and set no grouping levels. Sort the data in ascending order by Description . Use the stepped format and Landscape layout. Save the Report as Toys by Vendor . If necessary, modify the report title to be “ Toys by Vendor.” Make sure all data and field names are visible and evenly spaced on the page.

Create a relationship report and use the default name. Save and close the database. Submit your completed database.

Page 1 of 2