Exp19_Access_Ch02_Capstone - International Foodies 1.0

Project Description:

International 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.

Steps to Perform:

Step	Instructions	Points Possible
1	Start Access. Open the downloaded Access file named Exp19_Access_Ch2_Cap_Foodies. 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.	0
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 Suppliers.xlsx Excel workbook, examine the data, and close the workbook. Open the Products.xlsx 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 External Data tab, click New Data Source, point to From File in the Import & Link group, and then select Excel. Navigate to and select the Suppliers.xlsx workbook to be imported as a new table in the current database. Select First Row Contains Column Headings. Set the SupplierID field Indexed option to Yes (No Duplicates). Select SupplierID as the primary key when prompted and accept the table name Suppliers. Do not save the import steps.	10
3	Import the Products.xlsx workbook, set the ProductID Indexed option to Yes (No Duplicates), and select ProductID as the primary key. Accept the table name Products.	10
4	Change the Field Size of the QuantityPerUnit field to 25 in Design view of the Products table. Set the Field Size of ProductID and CategoryID to Long Integer. Save the changes and open the table in Datasheet view. Open the Suppliers table in Datasheet view to examine the data. Close the tables.	4
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 CategoryID with the Number Data Type and Number assigned to a new category. (type the period) as the Description. Set the Caption property to Category ID. Save the table as Categories.	6
6	Add CategoryName with the Short Text Data Type and Name of food category. (type the period) as the Description. Change the field size to 15. Set the Caption property to Category Name and the Required property to Yes.	6
7	Add CategoryDescription with the Long Text Data Type. Set the Caption property to Category Description. Switch to Datasheet view and save the table when prompted. You will enter Category data into the table in the next step.	4
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 records to the Categories table: Category ID Category Name Category Description 1 BEVERAGES SOFT DRINKS, COFFEES, TEAS 2 CONDIMENTS SAUCES, RELISHES, SEASONINGS 3 CONFECTIONS DESSERTS, CANDIES, SWEET BREADS 4 DAIRY PRODUCTS CHEESES 5 GRAINS/CEREALS BREADS, PASTA, CEREAL 6 MEAT/POULTRY PREPARED MEATS 7 PRODUCE DRIED FRUIT, BEAN CURD 8 SEAFOOD SEAWEED AND FISH Close the table.	6
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 Enforce Referential Integrity and Cascade Update Related Fields check boxes. Save and close the Relationships window.	12
10	You will use the Simple Query Wizard to create a query of all products that you import in the seafood category. Add the ProductName, SupplierID, and CategoryID fields from Products (in that order). Save the query as Seafood Products.	10
11	Add a criterion in Design view, to include only products with 8 as the CategoryID.	2
12	Sort the query results in ascending order by ProductName. Run, save, and close the query.	2
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 Seafood Products query and paste it using Seafood Or Meat/Poultry as the query name.	2
14	Open the Seafood Or Meat/Poultry query in Design view and delete the CategoryIDcolumn.	2
15	Add the Categories table to the top pane of the query design window. Add the CategoryName field to the last column of the design grid and set the criterion as "Seafood" Or "Meat/Poultry". Run, save, and close the query.	4

SUPPLIERS

PRODUCTS

AutoSave On U Suppliers Swed- Search Freda Nkrumah EN File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri - A A AutoSum 10 == 89 Wrap Text General NI 2Y O rill X Out [Copy Peste Format Painter Cipboard BIU w A Insert Delete format Merge Center $ -% Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Analyze Data Analysis Font Alignment Number Cels C5 > Yoshi Necase L M N U P 0 G Region H PostalCode K Fax 1 Country Phone ECI 450 UK (171) 555-2222 2. LA 70117 USA (100) 555-4822 3 MI 48104 USA 4 4 (313) 555-5/35 (313) 555-3349 100 Japan (08) 3555-5011 5 Asturias Asturias 33007 Suain 198) 598 76 54 6 545 Japan (06) 431 7877 7 A D F SupplierID Company Name Contact Name Contact Title Address City Purchasing Exotic Liquids Charlotte Cooper 49 Gilbert St London Manager , New Orleans Order Shelley Burke P.O. BOK 79934 New Orleans Cajun Delights Administrator Grandma Kelly's Sales 3 Homestead Regina Murphy 707 Uxford Rd. Ann Arbor Representative Marketing 9-9 Sekimai 4 Tokyo Traders Yoshi Nagase Tokyo Manager Musashino-shi Cooperativa de Antonio del Valle Export 5 Quesos las Calle del Rosal 4 Oviedo Saavedra Administrator Cabras Marketing 92 Setsuko 6 Mayumi's Mayumi Ohno Osaka Fleure:entative Chuck Marketing 7 Pavlova, Lid. 74 Rose St. lar Devling Melbourne Manager Moonia Ponds Specialty Sales 8 Peter Wilson 29 King's Way Manchester Biscuits, Ltd. Representative 9 PB Knckebrd Lars Peterson Sales Agent Kaluadagatan 13 Gteborg AB Av. das Refrescos Marketing 10 Carlos Diaz Arriericanas So Paulo Americanas LTDA Manager 12.890 Hell Subwaren Petra Winkler 11 Tiergartenstrae Sales Manager Berlin GmbH & Co. KG 5 5 Plutzer International 12 Lebensmittelgro Martin Bein Bogenallee 51 Frankfurt Bmarkte AG Marketing Mgr. Nord-Ost-lisch Coordinator Frahmredder 12 Handelsgesellsch Sven Petersen Cuxhaven Foreign Markels 1124 112 Suppliers + Victoria 305B Australia (03) 444-2343 103) 444-6588 M1 690 UK (161) 555 4448 9 S-345 67 Sweden 031-987 65 43 021-987 65 91 10 5442 Brazil (11) 555 4640 11 10785 Germany (010) 9984510 12 60439 Germany (069) 992755 13 27478 Germany 104721) 8713 104721) 8714 1009 Type here to search O BEL 4) ch 8:52 PM 1/18/2021 AutoSave On U Products - Last Modified: lust now Search Freda Nkrumah EN File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments AutoSum Calibri - A A 10 89 Wrap Text General 27 NI Brill Paste X Out [Copy Format Painter Clipboard LA Conditional Format as Cell Formatting Table Styles BIU 9 $ %, 8 Insert Delete Tormat Merge Center Clear Sort & Tind & Filter Select Eding Analyze Data Font Alignment Number Cels Analysis A1 ProductID L M N 0 A ProductID B ProductName F UnitPrice G Product Cost Suppliers H 1 UnitsInStock UnitsOnOrder K ReorderLevel Discontinued 1 1 Chal 1 $18.00 $12.95 39 0 10 FALSE 2 2 D E CategoryID QuantityPerUnit 1 10 boxes x 20 bags 24 - 12.02 1 bottles 12-550 ml 2 bottles 2 Chang 1 $19.00 $13.50 17 40 25 FALSE 3 3 Aniseed Syrup 1 $10.00 $8.40 13 10 25 FALSE 4 2 $22.00 $15.50 53 0 0 FALSE 5 6 6 2 48-6 az jars 2 36 boxes 2 $21.35 $18.00 0 0 0 0 TRUE 2 3 2 12 8 oz jars $25.00 $19.00 120 0 25 FALSE 7 Chef Anton's Cajun Seasoning 5 Chef Anton's Gumbo Mix Grandma's Boysenberry 6 Spread Uncle Bob's Organic Dried Pears Northwoods Cranberry 8 Sauce 9 Mishi Kobe Niku 10 kura 11 Queso Cabrales 3 7 12 - 1 lb pkgs $30.00 $22.50 15 0 10 FALSE 8 3 2 12-12 oz jars $40.00 $28.75 6 0 0 FALSE 4 0 9 10 11 12 4 5 6 19-500 g pkgs. & 12 -200 ml jars 4 1 kepke. 4 10 500 g pkgs $97.00 $31.00 $21.00 0 $65.00 $30.00 $16.00 $22.75 29 31 22 0 0 30 TRUE FALSE FALSE 30 12 Queso Manchego La Pastora 5 5 $38.00 86 0 0 FALSE 13 14 15 5 13 Kanbu 14 Tofu 6 6 $6.00 $23.25 $2.12 $ $15.85 24 29 35 0 0 FALS FALSE FALSC 0 15 Genen Shouyu $9.99 0 S FALSE 16 17 18 19 16 Pavlova 17 Alice Muttori 18 Carnarvon Tigers 7 7 7 a 2 kg box 7 10 - 100 g plus 24 - 250 ml 2 bottles 3 32-500 g hoxes 6 20- 1 kg tins 8 16 ks pkc. 10 boxes x 12 3 pieces $15.50 $17.45 $39.00 $62.50 $16.99 $20.70 $18.75 39 29 0 o 12 0 0 0 0 10 0 0 FALSE TRUE FALSE 8 $9.20 $4.50 25 20 0 5 FALSE 19 Teatime Chocolate Biscuits Products HI 1009 Type here to search O L 4) 8:52 PM 1/18/2021 AutoSave On U Suppliers Swed- Search Freda Nkrumah EN File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri - A A AutoSum 10 == 89 Wrap Text General NI 2Y O rill X Out [Copy Peste Format Painter Cipboard BIU w A Insert Delete format Merge Center $ -% Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Analyze Data Analysis Font Alignment Number Cels C5 > Yoshi Necase L M N U P 0 G Region H PostalCode K Fax 1 Country Phone ECI 450 UK (171) 555-2222 2. LA 70117 USA (100) 555-4822 3 MI 48104 USA 4 4 (313) 555-5/35 (313) 555-3349 100 Japan (08) 3555-5011 5 Asturias Asturias 33007 Suain 198) 598 76 54 6 545 Japan (06) 431 7877 7 A D F SupplierID Company Name Contact Name Contact Title Address City Purchasing Exotic Liquids Charlotte Cooper 49 Gilbert St London Manager , New Orleans Order Shelley Burke P.O. BOK 79934 New Orleans Cajun Delights Administrator Grandma Kelly's Sales 3 Homestead Regina Murphy 707 Uxford Rd. Ann Arbor Representative Marketing 9-9 Sekimai 4 Tokyo Traders Yoshi Nagase Tokyo Manager Musashino-shi Cooperativa de Antonio del Valle Export 5 Quesos las Calle del Rosal 4 Oviedo Saavedra Administrator Cabras Marketing 92 Setsuko 6 Mayumi's Mayumi Ohno Osaka Fleure:entative Chuck Marketing 7 Pavlova, Lid. 74 Rose St. lar Devling Melbourne Manager Moonia Ponds Specialty Sales 8 Peter Wilson 29 King's Way Manchester Biscuits, Ltd. Representative 9 PB Knckebrd Lars Peterson Sales Agent Kaluadagatan 13 Gteborg AB Av. das Refrescos Marketing 10 Carlos Diaz Arriericanas So Paulo Americanas LTDA Manager 12.890 Hell Subwaren Petra Winkler 11 Tiergartenstrae Sales Manager Berlin GmbH & Co. KG 5 5 Plutzer International 12 Lebensmittelgro Martin Bein Bogenallee 51 Frankfurt Bmarkte AG Marketing Mgr. Nord-Ost-lisch Coordinator Frahmredder 12 Handelsgesellsch Sven Petersen Cuxhaven Foreign Markels 1124 112 Suppliers + Victoria 305B Australia (03) 444-2343 103) 444-6588 M1 690 UK (161) 555 4448 9 S-345 67 Sweden 031-987 65 43 021-987 65 91 10 5442 Brazil (11) 555 4640 11 10785 Germany (010) 9984510 12 60439 Germany (069) 992755 13 27478 Germany 104721) 8713 104721) 8714 1009 Type here to search O BEL 4) ch 8:52 PM 1/18/2021 AutoSave On U Products - Last Modified: lust now Search Freda Nkrumah EN File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments AutoSum Calibri - A A 10 89 Wrap Text General 27 NI Brill Paste X Out [Copy Format Painter Clipboard LA Conditional Format as Cell Formatting Table Styles BIU 9 $ %, 8 Insert Delete Tormat Merge Center Clear Sort & Tind & Filter Select Eding Analyze Data Font Alignment Number Cels Analysis A1 ProductID L M N 0 A ProductID B ProductName F UnitPrice G Product Cost Suppliers H 1 UnitsInStock UnitsOnOrder K ReorderLevel Discontinued 1 1 Chal 1 $18.00 $12.95 39 0 10 FALSE 2 2 D E CategoryID QuantityPerUnit 1 10 boxes x 20 bags 24 - 12.02 1 bottles 12-550 ml 2 bottles 2 Chang 1 $19.00 $13.50 17 40 25 FALSE 3 3 Aniseed Syrup 1 $10.00 $8.40 13 10 25 FALSE 4 2 $22.00 $15.50 53 0 0 FALSE 5 6 6 2 48-6 az jars 2 36 boxes 2 $21.35 $18.00 0 0 0 0 TRUE 2 3 2 12 8 oz jars $25.00 $19.00 120 0 25 FALSE 7 Chef Anton's Cajun Seasoning 5 Chef Anton's Gumbo Mix Grandma's Boysenberry 6 Spread Uncle Bob's Organic Dried Pears Northwoods Cranberry 8 Sauce 9 Mishi Kobe Niku 10 kura 11 Queso Cabrales 3 7 12 - 1 lb pkgs $30.00 $22.50 15 0 10 FALSE 8 3 2 12-12 oz jars $40.00 $28.75 6 0 0 FALSE 4 0 9 10 11 12 4 5 6 19-500 g pkgs. & 12 -200 ml jars 4 1 kepke. 4 10 500 g pkgs $97.00 $31.00 $21.00 0 $65.00 $30.00 $16.00 $22.75 29 31 22 0 0 30 TRUE FALSE FALSE 30 12 Queso Manchego La Pastora 5 5 $38.00 86 0 0 FALSE 13 14 15 5 13 Kanbu 14 Tofu 6 6 $6.00 $23.25 $2.12 $ $15.85 24 29 35 0 0 FALS FALSE FALSC 0 15 Genen Shouyu $9.99 0 S FALSE 16 17 18 19 16 Pavlova 17 Alice Muttori 18 Carnarvon Tigers 7 7 7 a 2 kg box 7 10 - 100 g plus 24 - 250 ml 2 bottles 3 32-500 g hoxes 6 20- 1 kg tins 8 16 ks pkc. 10 boxes x 12 3 pieces $15.50 $17.45 $39.00 $62.50 $16.99 $20.70 $18.75 39 29 0 o 12 0 0 0 0 10 0 0 FALSE TRUE FALSE 8 $9.20 $4.50 25 20 0 5 FALSE 19 Teatime Chocolate Biscuits Products HI 1009 Type here to search O L 4) 8:52 PM 1/18/2021