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.
