Managing Inventory at Cem’s Sporting Goods 

Managing Inventory at Cem’s Sporting Goods 

Project Description: 
Cem’s Sporting Goods has been in business for over a decade. Cem, the owner, has realized the importance of using technology to manage his inventory, and he has created a database to help him do so. Unfortunately, he does not know enough about database design to create tables that help control the way data is entered. Thus, he has only entered a small amount of data. You have been asked to modify the tables to ensure ease of use and functionality so he can begin using the database.

Instructions: 
For the purpose of grading the project you are required to perform the following tasks: 
Step    Instructions    Points Possible
1    Start Access. Open the downloaded Access file named a03ws05_grader_h1.accdb. Save the file with the name a03ws05SportingGoods_LastFirst replacing LastFirst with your name. Enable the content if necessary.    0
2    Open tblSupplier in Design view, and then make the following modifications.

• SupplierID: Create a custom format so the supplier ID is displayed as ID-001, ID-002, and so on. PG 263-265• CompanyName: Change the caption to Company Name. PG270-271
    6
3    Make the following modifications to the tblSupplier table. 

• State: Create a custom format so the state name is displayed in uppercase. Change the field size to only allow two characters. PG 263-265
• ZipCode: Create an input mask for ZipCode that appears in the format 15044-1234. Change the field size to only allow 10 characters. Change the caption to Zip Code.
PG 263-265    6
4    Continue to make the following modifications to the tblSupplier table.

• Phone: Create an input mask using the Phone format. The phone number should appear in the format (555) 555-1234. Access should store the symbols in the mask and use an underscore as a placeholder. Change the field size to only allow 14 characters.
• Fax: Create an input mask using the Phone format. The fax number should appear in the format (555) 555-1234. Access should store the symbols in the mask and use an underscore as a placeholder. Change the field size to only allow 14 characters. PG 258-269
• Add a new field named URL. Select Hyperlink as the data type, and then enter This is the website home page as the Description.PG 292-294
    10
5    Save your changes and switch to Datasheet view in TblSupplier table. Enter the following record to ensure your field settings are correct PG 275. Enter Maurice Sporting Goods as the company name. Enter 1910 Techny Road as the address. Enter Northbrook as the city. Enter il as the state. Enter 60065 as the ZIP code. Enter 800-477-3474 as the phone number. Enter 847-715-1418 as the fax. Enter www.maurice.net as the URL. Close tblSupplier. PG 95-97    6
6    Make the following modifications to the tblInventory table.

• ItemName: Change the caption to Item.
• Supplier: Use the Lookup Wizard to create a lookup field that looks up the CompanyName in the tblSupplier table. Sort the Company names in Ascending order. Keep the key column hidden. “Enable Data Integrity and Restrict Delete”. Make the data in this field required. PG 279-282
    10
7    Continue to make the following modifications to the tblInventory table.

• QuantityOnHand: Add a validation rule that requires all data entries to be greater than or equal to zero. Enter the following if a user accidently enters a negative number: Values must be greater than or equal to zero. Please reenter the quantity on hand. Make the data in this field required. Change the caption to Qty on Hand.
• Discontinued: PG 268-271  Change the field to the Yes/No data type. PG 286-289    10
8    Switch to Datasheet view, and then enter the following data in record 1 to ensure your field settings are correct.PG 275 Enter DollarDays PG 95-97International, Inc. as the supplier. Change quantity on hand to –1 to see if the validation rule works PG268-270. Change back to 2 once verified. Enter Yes as discontinued. .     6
9    Filter the data using an advanced filter to display all items that have Qty on Hand greater than or equal to 3. Save the filter as a query named qry3orMore.PG294-297    10
10    Make the following modifications to the tblOrder table.

• TransID: Change the data type to AutoNumber. Create a custom format so the TransID is displayed as TID-A01, TID-A02, and so on.PG289-291
• Item: Create a lookup field that looks up the ItemName and Description in the tblInventory table. Sort the item names in Ascending order. Keep the key column hidden. Enable Data Integrity and restrict related records from being deleted. Make the data in this field required
PG277-283    10
11    Continue to make the following modifications to the tblOrder table.

• TransactionType: Create a lookup field that looks up values that you enter. Enter the following options: Row 1: Check; Row 2: Credit; Row 3: Pay On Delivery. Limit selections to the list, and then do not permit multiple values to be selected.PG277-283

 Change the caption to Transaction Type.PG270-271
• Quantity: Add 1 as the default value.PG276-277    10
12    Continue to make the following modifications to the tblOrder table.

• OrderDate: Change the caption to Order Date.PG270-271 Format the field as Medium Date PG263-265.
• Create a multiple-field index on the Item, Quantity, and OrderDate fields. Enter OrderDetails as the name. Sort Item, Quantity, and OrderDate in Ascending order. Select No for the Primary, Unique, and Ignore Nulls properties. PG273-274
    10
13    Save your changes and switch to Datasheet view. Enter the following record to ensure your field settings are correct. Select Pro Racer Goggles as the item.  Select Check as the transaction type. Keep the default quantity. Choose 6/8/2015 as the order date. PG 275    6
14    Close all database objects. Close the database and then exit Access. Submit the database as directed.    0
    Total Points    100