Managing Inventory at Cem’s Sporting Goods
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.
For the purpose of grading the project you are required to perform the following tasks:
Start Access. Open the downloaded Access file nameda03ws05_grader_h1.accdb. Save the file with the namea03ws05SportingGoods_LastFirst replacing LastFirst with your name. Enable the content if necessary.
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. • CompanyName: Change the caption to Company Name.
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.• 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.
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.• Add a new field named URL. Select Hyperlink as the data type, and then enter This is the website home page as the Description.
Save your changes and switch to Datasheet view. Enter the following record to ensure your field settings are correct. Enter Maurice Sporting Goods as the company name. Enter1910 Techny Road as the address. Enter Northbrook as the city. Enter il as the state. Enter 60065 as the ZIP code. Enter800-477-3474 as the phone number. Enter 847-715-1418 as the fax. Enter www.maurice.net as the URL. Close tblSupplier.
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.