A6 1 4 Weck 6 homework _ _ Preparing database tables The following are the tables that are needed to create your database . Table Name Primary Key | Foreign Keys Other Attributes Customer Customer Number Customer Name* Shipping Address Billing Address Credit Limit Balance Sales Invoice number Customer Date of sale* number Sales - Inventory Item number Quantity sold Invoice number Price ( actual sales price ) Inventory Item Number Cost ( standard or list ) Description* Quantity on Hand Price ( standard or list ) . Open up Access . ( File , New , Click Blank Database , then name your file , then click create table in design view )\\ IN Enter each of the fields for Inventory , starting with the primary key item number . . For Item Number , type Item Number directly underneath Field Name , leave its data type set to text , and right click your mouse ( your cursor should be to the left of Item Number ) to set up this attribute as the primary key . For field properties :" Field size : 3 Caption : Item Number Validation Rule : Like "# # # " Validation Text : Invalid entry . Item Number must be three digits long. For validation rule , be sure to write the word Like . For all other properties , use the Access defaults 4 . Item Cost is the next field . data tree is text . caption is Standard Cost5 . Item Description is next , data type is text , field size is 25, caption is Description . 6 . Quantityon Hand is the next field , data type is numeric , field size is 3 , caption is QOH 7 . Standard Price is next , data type is numeric , caption is Standard Price* 8. Now enter data directly into your table . Directly underneath the File there is a table* icon ; click this and then click datasheet view .9. You can now enter data . Enter one line of data ( you must be in datasheet view . you can get to datasheet view by clicking the button underneath File and doubleclicking " datasheet" ) . Item Number : 101 Item Cost ." $10.00 Item Description : 1/8 " sheet metal Quantity on Hand : 250 Standard Price : $11.00 Save your table as Inventory 10. Next , create a new table for sales . Make sure you are in design view , click tables , double click create table in design view and enter each of the fields for Sales ., starting with the primary key Invoice Number 1 1 . For Invoice Number , type Invoice Number directly underneath Field Name , leave its data type set to text , and right click your mouse ( your cursor should be to the left of Invoice Number ) to set this attribute up as the primary key . Forfield properties enter the following :" Field size : 3 Caption : Invoice Number Validation Rule : Like " # # # " Validation Text . Invalid entry . Invoice Number must be three digits long . For all other properties , use the Access defaults12. Customer Number is the next field - - type this underneath Invoice Number ; The data type is text . For field properties enter the following :" Field size : 3 Caption : Customer Number Validation rule : Like "# # # " Validation text : Invalid Entry . Customer Number must be three digits long .13 . DateofSale is the next field , type this underneath Customer Number , the data type is date . For field properties enter the following :" Format : Short date Caption : Date of Sale* For all other properties use the Access defaults . Go to datasheet view and enter the following record ? Invoice Number : 101 Customer Number : 101 Date of sale : January 5 , 2007 14 . Now build a table for Sales- Inventory . Make sure you are in design view , click tables and double click create table in design view . Then enter each of the fields for Sales- Inventory . This table has a composite primary key ( both Item Number and Invoice Number are primary key's . 15 . Enter a field name of Item Number , leave its data type set to text . For field properties :" Field size : 3 Caption : Item Number Validation Rule : Like "# # # " Validation Text : Invalid entry . Item Number must be three digits long . Indexed Property : Yes ( Duplicates OK ) For all other properties , use the Access default16. For Invoice Number , type Invoice Number directly underneath Field Name , leave* it's data type set to text . For field properties enter the following : Field size : 31 Caption : Invoice Number Validation Rule : Like " # # #" Validation Text . Invalid entry . Invoice Number must be three digits long . Indexed Property : Yes ( Duplicates OK ) For all other properties , use the Access defaults 17. Now we can set up the composite primary key . While pressing the Curl key , click the row selectors for Item Number and Invoice Number . With both fields selected , right click the Primary key toolbar button . The primary key should appear in the row selectors of both fields .18 . For the field Quantity Sold , type Quantity Sold directly underneath Field name , set it's data type to text . Enter the following field properties : Field Size : 3 Caption : Quantity Sold Validation Rule : Like " # # #" Validation Text : Quantity sold cannot exceed four digits . For all other properties , use the Access defaults 19 . Actual Price is next , data type is currency , caption is Actual Price . For all other properties , use the Access defaults Save your table as Salesinventory . 20 . Next , enter a row of data :" Item Number : 101 Invoice Number : 101 Quantity Sold : 10 Actual Price : $12.00 21 . Now , create a new table for your customers . Make sure you are in design view , click tables , double click create table in design view and enter each of the fields for Customer , starting with the primary key Customer Number .25 . Enter CustomerCity . The data type is text , the field size is 40 and the caption is City . 26 . Enter CustomerZip . The data type is text , the field size is 10 , and the caption is Zip Code. 27 . Type Credit Limit in the next row . And set the data type to currency . Change the decimal places property to O . 28 . Type Account Balance in the next row . And set the data type to currency . Change the decimal places property to O. 29. Save your table as customer . 30 . Click to datasheet view and enter one line of data ( use your own name , address etc . ) 31 .. Link your tables . This will enforce referential integrity . To do this , close all your open tables . Choose Tools , Relationships . When the tables dialog box appears , select all three tables . Connect the primary keys ( by double clicking them ) to their respective foreign Keys . 32. Send your file to your gradebook