nternational Foodies is an importer of exotic foods from all over the world. You landed a summer internship with the company and discovered that their product lists and the suppliers they buy from are stored in Excel workbooks. You offer to help by using your newly gained knowledge of Access to create a relational database for them. You will begin by importing the workbooks from Excel into a new Access database. Your manager mentions that she would also like a table that specifies food categories so that you can relate the products you sell to specific categories in the database. You will create a table from scratch to track categories, create relationships between the tables, and create some baseline queries. 1 Start Access. Open the downloaded Access file named . Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. 2 You will examine the data in the downloaded Excel worksheets to determine which fields will become the primary keys in each table and which fields will become the foreign keys so that you can join them in the database. Open the Excel workbook, examine the data, and close the workbook. Open the Excel workbook, examine the data, and close the workbook. You will import two Excel workbooks that contain supplier and product information into the database. Click the , click , point to in the Import & Link group, and then select . Navigate to and select the workbook to be imported as a new table in the current database. Select . Set the SupplierID field Indexed option to . Select as the primary key when prompted and accept the table name . Do not save the import steps. 3 Import the workbook, set the ProductID Indexed option to , and select as the primary key. Accept the table name . 4 Change the Field Size of the QuantityPerUnit field to in Design view of the Products table. Set the Field Size of ProductID and CategoryID to . Save the changes and open the table in Datasheet view. Open the Suppliers table in Datasheet view to examine the data. Close the tables. 5 You will create a new table that will enable International Foodies to associate each product with a food category in the database. Create a new table in Design view. Add the following fields in Design view and set the properties as specified: Add the primary key field as with the and (type the period) as the Description. Set the Caption property to . Save the table as . 6 Add with the and (type the period) as the Description. Change the field size to . Set the Caption property to and the Required property to . 7 Add with the . Set the Caption property to . Switch to Datasheet view and save the table when prompted. You will enter Category data into the table in the next step. 8 You will add 8 records to the Categories table so that you have some sample data to test in the database. Add the following rec
ords to the Categories table: Category ID Category Name Category Description Close the table. 9 You will create the relationships between the tables using the Relationships window. Add all three tables to the Relationships window. Identify the primary key fields in the Categories table and the Suppliers table and join them with their foreign key counterparts in the related Products table. Select the and . Save and close the Relationships window. 10 You will use the Simple Query Wizard to create a query of all products that you import in the seafood category. Add the , , and fields from Products (in that order). Save the query as . 11 Add a criterion in Design view, to include only products with as the CategoryID. 12 Sort the query results in ascending order by ProductName. Run, save, and close the query. 13 You want to create a query that displays actual category names rather than the CategoryIDs. You are interested to know which meat and poultry products are imported. You will copy the Seafood Products query and modify it to delete a field, then add an additional table and field. Copy the and paste it using as the query name. 14 Open the in Design view and delete the column. 15 Add the to the top pane of the query design window. Add the field to the last column of the design grid and set the criterion as . Run, save, and close the query. 16 You will create a query that identifies suppliers and their associated products. Because there is a relationship between the two tables, you can now pull data from each of them together as usable information. Create a query in Design view that includes the and tables. The query should list the company name, contact name, phone (in that order), then the product name and the product cost (in that order). 17 Sort the query by company name in ascending order, then by product cost in descending order. Run, close, and save the query as . 18 You determine that the data in the Company by Product List query could be summarized with a Total row. You will group the records by company name, and then count the number of products you buy from each of them. Copy the and paste it using as the query name. 19 Open the in Design view and delete the , , . 20 Click in the Show/de group on the Query Tools Design tab. Click in the of the ProductName field, click the , and then select . The records will be grouped by the company’s name and the products for each company will be summarized. 21 Modify the field name of the ProductName column as to make the field name more identifiable. Click in the Results group (20 records display in the Datasheet). The results display the product count for each company that supplies your organization. Save and close the query. 22 Close all database objects. Close the database and then exit Access. Submit the database as directed.