Module 2: assignment: create and link tables instructions

Use Access to construct a database for a college bookstore store based on the step-by-step instructions listed below.

Create a database and one table:

• The name of the database will be Inventory_YourLastname.
• Create a table named PRODUCTS based on data in Table 1.
• Determine the appropriate data type for each field.
• Assign an AutoNumber field to serve as the Primary Key. This should be the first field of the table and named PRODUCTID.
• Populate the table with data from Table 1.

PRODUCT_NAMEDEPARTMENTQUANTITYPRICESUPPLIERAvocado DipF60.99Jay’sBall Point PenP1500.89BicChipsF770.65Lay’sClass RingC5098.95BalfourDictionaryB2212.95ColumbiaDoritosF1452.49Lay’sFelt-tip PenP760.98BicLogo T-ShirtC2509.99ChampionPencilP2000.15BicPepsiF4440.65PepsiSchool JacketC10034.95ChampionSchool TieC1517.50Field’sThesaurusB 1218.00ColumbiaThree-Hole PaperA2001.98MeadThree-Ring BinderA1223.50Mead

Add a new table to the Inventory database:

You now want to include the supplier information in the INVENTORY database. Use the information below:

• Create a table named SUPPLIERS based on Table 2.
• Determine the appropriate data type for each field.
• Assign the SUPPLIER field as SUPPLIERS table Primary Key.
• Use the Telephone number input mask to format the telephone number.
• Populate the table with data from Table 2.

SUPPLIERCONTACTSTREET ADDRESSCITYSTATEZIP CODEPHONE NUMBERBalfourCoffinWall Street PlazaNew YorkNY100052122690800BicZeienPrudential TowerBostonMA22199ChampionOwen3141 MonroeRochesterNY146037163853200ColumbiaQuigleyColumbia College

New York

NY100270Field’s11 N StateChicagoIL606030Jay’s1504 W 44thChicagoIL606530Lay’sBeeby7701 Legacy DrPlanoTX750242146244700MeadRobertsCourthouse PlazaDaytonOH454635134956323PepsiCallowayAnderson Hill RoadPurchaseNY105779142532000

Relating the two tables:

You will need to establish a link between the PRODUCTS and SUPPLIERS tables.

• Determine the common attribute between the PRODUCTS and SUPPLIERS tables.
• Use the common field and create a relationship.

Perform Sort, Filter and:

• Open the SUPPLIERS table and sort the records based on CITY. Save as CitySort.
• Open the PRODUCTS table and create a filter of your own choice.
• Use the ‘Aggregate’ function to calculate the average price in the PRODUCTS table.

Close and Submit:

• Close your database.
• Submit your database to the assignment submission folder that follows this page.