Need help? We are here

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.