Question

In: Computer Science

Exp19_Access_Ch02_Capstone - International Foodies 1.0 Project Description: International Foodies is an importer of exotic foods from...

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

Solutions

Expert Solution

Suppliers.xlsx and Products.xlsx in this two workbook did not provide in the question in this cause it is not to posible to implement the Access workbook file.

So this cause Asumping this two excel table  file and implimented in the Access file

Asumption Suppliers.xlsx file =

Suplier Id Suplier Name Suply Items Suply Form

1023

D_ Das Grossory Local Market
1025 M_Moriaty Medicins

Local Pathology

1026 S_Shvastav Dry Food

Loacal Bazar

Asumption Products.xlsx file =

Product Id Quantity Per Item Catagory Id Supliers Id
205 10 Packets 1 1023
208 100 Packets 3 1025
209 50 Packets 5 1026

1)  

2) Importing Two Excel files

i)Goto External Data -> Then Click Import to Excel Spred Sheet option -> Brows the file -> Enable the option Import the source data into the new table and corrent Database -> click OK -> Click Next -> Enable the option First row is colum Heading.

iii) Set Field Suplier Id No duplicates -> Click Next

iv) Choose the Suplier Id As a Primery Key -> click Next

v) Set the Name of Table and Finish

In This same way to import the Products Table.

4) Goto Design view tab And change the Catagory Id Data type ia long integer

Datsheet View of Suplier

Datasheet View of Products

5) Creating New Table

Goto Insert Tab -> Click Table Design -> Fill the Design View Tab ->

Set Primary Key Catagory ID and Click Save with the table name Categories.

8) Fill the Data then table look like

9) Goto Database Tools tab and Click Relationship option

Add three table

10) Go to Query Wizerd in create tab Click Simple querry wizard and click Ok

Add table filds and click Ok then the Query is created

Use Sort and filter option to Sort the value of query results in ascending order by ProductName.

Next query is the same method using to Run


Related Solutions

1.    The financial staff at Exotic Foods Inc., a food importer, wholesaler, and distributor, has estimated the...
1.    The financial staff at Exotic Foods Inc., a food importer, wholesaler, and distributor, has estimated the following sales figures for the first half of 2018: Month Sales Other Expenses January $100,000 $3,000 February $120,000 $3,200 March $150,000 $3,500 April $180,000 $3,800 May $150,000 $3,500 June $120,000 $3,200 July $150,000 $3,500 August $180,000 $3,800 Actual November and December 2017 sales were $200,000 and $90,000, respectively. Cash sales are 45% of the total and the rest are on credit. About 70% of...
Mini Case With the growth in demand for exotic foods, Possum Products’s CEO Michael Munger is...
Mini Case With the growth in demand for exotic foods, Possum Products’s CEO Michael Munger is considering expanding the geographic footprint of its line of dried and smoked low-fat opossum, ostrich, and venison jerky snack packs. Historically, jerky products have performed well in the southern United States, but there are indications of a growing demand for these unusual delicacies in Europe. Munger recognizes that the expansion carries some risk. Europeans may not be as accepting of opossum jerky as initial...
Portfolio Project: Exotic Food Inc., Capital Budgeting Case CASE SUMMARY Exotic Food Inc., a food processing...
Portfolio Project: Exotic Food Inc., Capital Budgeting Case CASE SUMMARY Exotic Food Inc., a food processing company located in Herndon, VA, is considering adding a new division to produce fresh ginger juice. Following the ongoing TV buzz about significant health benefits derived from ginger consumption, the managers believe this drink will be a hit. However, the CEO questions the profitability of the venture given the high costs involved. To address his concerns, you have been asked to evaluate the project...
innovation at international foods case study in APA Format
innovation at international foods case study in APA Format
H&H is a tannery that supplies high quality exotic leather to major international fashion houses and...
H&H is a tannery that supplies high quality exotic leather to major international fashion houses and customers globally. Tanning is the process of treating skins and hides of animals to produce leather. The three major steps in the production of leather are curing, beamhouse operations and tanning. Due to the increased demand and limited production space, H&H sourced pre-tanned leather (the supplier has already completed the curing and beamhouse operations processes), and raw and untreated skin (another separate supplier). The...
Joe Exotic Inc. is considering expanding into the cruise line business. To finance the expansion project,...
Joe Exotic Inc. is considering expanding into the cruise line business. To finance the expansion project, Joe Exotic will issue a 3-year, zero coupon bond with face value of $500 million for $450.97 million. Joe’s consultants have analyzed the cruise line market and produced the following scenarios for the expansion project for each of the next three years: Probability EBIT Depreciation CapEx High .35 750 50 50 Medium .35 500 50 50 Low 0 0 50 50 Joe’s consultants have...
Pinnacle Foods imports a variety of items for resale to U.S. retailers. Following is a description...
Pinnacle Foods imports a variety of items for resale to U.S. retailers. Following is a description of purchases and foreign-currency-denominated payments made in the last accounting period, plus the direct exchange rates for each date: Country Amount Currency Spot rate at purchase Spot rate at payment Argentina 250,000 Peso $0.06 $0.05 Canada 400,000 Dollar 0.732 0.713 India 300,000 Rupee 0.016 0.018 South Africa 100,000 Rand 0.074 0.077 Required Prepare the journal entries made by Pinnacle, a U.S. company, to record...
Pinnacle Foods imports a variety of items for resale to U.S. retailers. Following is a description...
Pinnacle Foods imports a variety of items for resale to U.S. retailers. Following is a description of purchases and foreign-currency-denominated payments made in the last accounting period, plus the direct exchange rates for each date: Country Amount Currency Spot rate at purchase Spot rate at payment Argentina 250,000 Peso $0.06 $0.05 Canada 400,000 Dollar 0.732 0.713 India 300,000 Rupee 0.016 0.018 South Africa 100,000 Rand 0.074 0.077 Required Prepare the journal entries made by Pinnacle, a U.S. company, to record...
Pinnacle Foods imports a variety of items for resale to U.S. retailers. Following is a description...
Pinnacle Foods imports a variety of items for resale to U.S. retailers. Following is a description of purchases and foreign-currency-denominated payments made in the last accounting period, plus the direct exchange rates for each date: Country Amount Currency Spot rate at purchase Spot rate at payment Australia 200,000 Australian dollar $0.76 $0.73 Thailand 800,000 Baht 0.0345 0.0365 Hong Kong 5,000,000 Hong Kong dollar 0.1319 0.1362 Jordan 500,000 Dinar 1.41 1.39 Required Prepare the journal entries made by Pinnacle, a U.S....
GIVE A PROJECT DESCRIPTION ON TRAINING AND DEVELOPMENT OF STAFF. * OBJECTIVES OF THE PROJECT ON...
GIVE A PROJECT DESCRIPTION ON TRAINING AND DEVELOPMENT OF STAFF. * OBJECTIVES OF THE PROJECT ON TRAINING AND DEVELOPMENT OF STAFF. *DELIVERABLES ON TRAINING AND DEVELOPMENT OF STAFF. *MILESTONES SCHEDULE ON TRAINING AND DEVELOPMENT OF STAFF. *SUCCES CRITERIA ON TRAINING AND DEVELOPMENT OF STAFF. *KEY ASSUMPTIONS ON TRAINING AND DEVELOPMENT OF STAFF. *LIMITS OF THE SCOPE ON TRAINING AND DEVELOPMENT OF STAFF. *PROJECT PRIORITIES ON TRAINING AND DEVELOPMENT OF STAFF. kindly answer ASAP... VERY URGENT...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT