Exp19_Access_Ch2_Cap_Foodies.accdb

ID	mSysRowId
1

Grader – Instructions Access

2

0

1

9

Project

Exp

19

_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
2

5

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
CategoryID column.

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

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
Suppliers and
Products 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).

6

17

Sort the query by company name in ascending order, then by product cost in descending order. Run, close, and save the query as
Company by Product List.

4

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
Company by Product List query and paste it using
Summary of Company by Product as the query name.

2

19

Open the
Summary of Company by Product query in Design view and delete the
ContactName,
Phone,
and ProductCost columns.

3

20

Click
Totals in the Show/Hide group on the Query Tools Design tab. Click in the
Total row of the ProductName field, click the
arrow, and then select
Count. The records will be grouped by the company’s name and the products for each company will be summarized.

3

21

Modify the field name of the ProductName column as
Product Count: ProductName to make the field name more identifiable. Click
Run 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.

2

22

Close all database objects. Close the database and then exit Access. Submit the database as directed.

0

Total Points

100

Created On: 07/11/2019 1 Exp19_Access_Ch02_Capstone – International Foodies 1.0

2

>Products

1
1

boxes x

bags

.00

.

9

0

2

1
1

– 12 oz bottles

.00

.

FALSE

3

1
2

13

25
FALSE

4

2
2

oz jars

.00

.50

0
0
FALSE

5

2
2

boxes

.

$

.00
0
0
0

6

3
2

0
25
FALSE

7

3
7

.00

15
0
10
FALSE

8

3
2

.75

6
0
0
FALSE

9

4
6

pkgs.

.00

0
0
TRUE

10

4
8

.00

$30.00
31
0
0
FALSE

5
4

.00

22
30
30
FALSE

12

5
4

.00

0
0
FALSE

13

6
8

0
5
FALSE

6
7

.25

35
0
0
FALSE

15

6
2

0
5
FALSE

16

7
3

– 500 g boxes

.45

29
0
10
FALSE

17

7
6

0
0
0
TRUE

18

7
8

0
0
FALSE

19

8
3

25
0
5
FALSE

20

8
3

40
0
0
FALSE

21

8
3

$10.00
$4.50
3
40
5
FALSE

22

9
5

$21.00

0
25
FALSE

23

9
5

0
25
FALSE

24

10
1

$4.50

20
0
0
TRUE

25

11
3

0
30
FALSE

11
3

15
0
0
FALSE

11
3

.90

0
30
FALSE

28

12
7

26
0
0
TRUE

29

12
6

.79

0
0
0
TRUE

30

13
8

10
0
15
FALSE

31

14
4

0
70
20
FALSE

32

14
4

9
40
25
FALSE

15
4
500 g

0
20
FALSE

16
1

$14.00

0
15
FALSE

35

16
1
24 – 12 oz bottles

20
0
15
FALSE

36

17
8

$19.00

112
0
20
FALSE

17
8

11
50
25
FALSE

38

18
1

.50

17
0
15
FALSE

39

18
1

$18.00
$17.51

0
5
FALSE

40

19
8

123
0
30
FALSE

19
8

85
0
10
FALSE

42

20
5

$14.00
$13.51
26
0
0
TRUE

43

20
1

17
10
25
FALSE

63

7
2

24
0
5
FALSE

12
5

22
80
30
FALSE

65

2
2

76
0
0
FALSE

8
3

$12.50

6
10
15
FALSE

69

15
4

26
0
15
FALSE

70

7
1

15
10
30
FALSE

4
7

$10.00

4
20
5
FALSE

ProductID	ProductName	SupplierID	CategoryID	QuantityPerUnit	UnitPrice	ProductCost	UnitsInStock	UnitsOnOrder	ReorderLevel	Discontinued
												1	Chai	1																																																										0									20		$1													8	$				12				9														5														3										10																																											FALSE
Chang	2											4		$			19	$			13	50	1										7					40										25
Aniseed Syrup	12 – 550 ml bottles			$10.00	$8.40			70
Chef Anton’s Cajun Seasoning	48 –									6	$			22	$												15	53
Chef Anton’s Gumbo Mix		36	$	21		35			18							TRUE
Grandma’s Boysenberry Spread	12 – 8 oz jars	$25.00	$19.00	120
Uncle Bob’s Organic Dried Pears	12 – 1 lb pkgs.		$								30	$22.50
Northwoods Cranberry Sauce	12 – 12 oz jars	$40.00	$	28
Mishi Kobe Niku	18 –	500 g	$97.00	$	65			29
Ikura	12 – 200 ml jars	$		31
				11	Queso Cabrales	1 kg pkg.		$21.00	$			16
Queso Manchego La Pastora	10 – 500 g pkgs.	$	38	$22.75	86
Konbu	2 kg box	$6.00	$2.12			24
		14	Tofu	40 – 100 g pkgs.	$	23	$15.	85
Genen Shouyu	24 – 250 ml bottles	$15.50	$9.99		39
Pavlova		32	$					17	$16.99
Alice Mutton	20 – 1 kg tins	$39.00	$20.70
Carnarvon Tigers	16 kg pkg.	$62.50	$48.75		42
Teatime Chocolate Biscuits	10 boxes x 12 pieces	$9.20			$4.50
Sir Rodney’s Marmalade	30 gift boxes	$81.00	$58.	80
Sir Rodney’s Scones	24 pkgs. x 4 pieces
Gustaf’s Knäckebröd	24 – 500 g pkgs.	$16.90	104
Tunnbröd	12 – 250 g pkgs.	$9.00	$2.75	61
Guaraná Fantástica	12 – 355 ml cans	$0.89
NuNuCa Nuß-Nougat-Creme	20 – 450 g glasses			$14.00	$8.50		76
			26	Gumbär Gummibärchen	100 – 250 g bags	$31.23	$22.80
27	Schoggi Schokolade	100 – 100 g pieces	$	43	$22.89	49
Rössle Sauerkraut	25 – 825 g cans	$45.60	$32.50
Thüringer Rostbratwurst	50 bags x 30 sausgs.	$	123	$82.50
Nord-Ost Matjeshering	10 – 200 g glasses	$25.89	$12.20
Gorgonzola Telino	12 – 100 g pkgs		$12.50	$6.60
Mascarpone Fabioli	24 – 200 g pkgs.	$32.00	$14.40
33	Geitost	$2.50	$2.01		112
34	Sasquatch Ale		24 – 12 oz bottles		$13.51	111
Steeleye Stout		$18.00		$17.51
Inlagd Sill	24 – 250 g jars	$18.51
37	Gravad lax	12 – 500 g pkgs.	$26.00	$25.51
Côte de Blaye	12 – 75 cl bottles	$2	63	$263.01
Chartreuse verte	750 cc per bottle		69
Boston Crab Meat	24 – 4 oz tins	$18.40	$17.91
41	Jack’s New England Clam Chowder	12 – 12 oz cans	$9.65	$9.16
Singaporean Hokkien Fried Mee	32 – 1 kg pkgs.
Ipoh Coffee	16 – 500 g tins	$46.00	$45.51
Vegie-spread	15 – 625 g jars	$43.90	$43.41
64	Wimmers gute Semmelknödel	20 bags x 4 pieces	$33.25	$32.76
Louisiana Fiery Hot Pepper Sauce	32 – 8 oz bottles	$21.05	$20.56
68	Scottish Longbreads	10 boxes x 8 pieces	$12.01
Gudbrandsdalsost	10 kg pkg.	$36.00	$35.51
Outback Lager	24 – 355 ml bottles	$15.00	$14.51
74	Longlife Tofu	5 kg pkg.	$9.51

2

>Suppliers

Gilbert St.

1)

55-2222

2

Cajun Delights

9

4

New Orleans

7

0

) 555-4822

3

USA

) 555-5735

4

Traders

Tokyo

100

5

54

6

Japan

7

Marketing Manager

8

Sales Representative

GSD

UK

1) 555-4448

9

10

Marketing Manager

.890

11

12

Germany

13

Germany

14

Sales Representative

Marketing Manager

16

USA

Sales Representative

Sweden

Sales Manager

USA

20

Singapore

SupplierID

CompanyName

ContactName

ContactTitle

Address

City

Region

PostalCode

Country

Phone

Fax

1

Exotic Liquids

Charlotte Cooper

Purchasing Manager

4

9

London

EC1 4SD

UK

(1

7

5

New Orleans

Shelley Burke

Order Administrator

P.O. Box 7

8

3

LA

70

11

USA

(

10

Grandma Kelly’s Homestead

Regina Murphy

Sales Representative

707 Oxford Rd.

Ann Arbor

MI

48104

(3

13

(313) 555-3349

Tokyo

Yoshi Nagase

Marketing Manager

9-8 Sekimai Musashino-shi

Japan

(03) 3555-5011

Cooperativa de Quesos ‘Las Cabras’

Antonio del Valle Saavedra

Export Administrator

Calle del Rosal 4

Oviedo

Asturias

33007

Spain

(98) 598 7

6

Mayumi’s

Mayumi Ohno

Marketing Representative

92 Setsuko Chuo-ku

Osaka

545

(06) 431-7877

Pavlova, Ltd.

Ian Devling

74 Rose St. Moonie Ponds

Melbourne

Victoria

3058

Australia

(03) 444-2343

(03) 444-6588

Specialty Biscuits, Ltd.

Peter Wilson

29 King’s Way

Manchester

M

14

(

16

PB Knäckebröd AB

Lars Peterson

Sales Agent

Kaloadagatan 13

Göteborg

S-345 67

Sweden

031-987 65 43

031-987 65 91

Refrescos Americanas LTDA

Carlos Diaz

Av. das Americanas

12

São Paulo

5442

Brazil

(11) 555 4640

Heli Süßwaren GmbH & Co. KG

Petra Winkler

Sales Manager

Tiergartenstraße 5

Berlin

10785

Germany

(010) 9984510

Plutzer Lebensmittelgroßmärkte AG

Martin Bein

International Marketing Mgr.

Bogenallee 51

Frankfurt

60439

(069) 992755

Nord-Ost-Fisch Handelsgesellschaft mbH

Sven Petersen

Coordinator Foreign Markets

Frahmredder 112a

Cuxhaven

27478

(04721) 8713

(04721) 8714

Formaggi Fortini s.r.l.

Elio Rossi

Viale Dante, 75

Ravenna

48100

Italy

(0544) 60323

(0544) 60603

15

Norske Meierier

Beate Vileid

Hatlevegen 5

Sandvika

13

20

Norway

(0)2-953010

Bigfoot Breweries

Cheryl Saylor

Regional Account Rep.

3400 – 8th Avenue Suite 210

Bend

OR

97101

(503) 555-9931

17

Svensk Sjöföda AB

Michael Björn

Brovallavägen 231

Stockholm

S-123 45

08-123 45 67

18

Aux joyeux ecclésiastiques

Guylène Nodier

203, Rue des Francs-Bourgeois

Paris

75004

France

(1) 03.83.00.68

(1) 03.83.00.62

19

New England Seafood Cannery

Robb Merchant

Wholesale Account Agent

Order Processing Dept. 2100 Paul Revere Blvd.

Boston

MA

02134

(617) 555-3267

(617) 555-3389

Leka Trading

Chandra Leka

Owner

471 Serangoon Loop, Suite #402

Singapore

0512

555-8787