excel and tableu

profilefahadhero
mis180_student_version4.xlsm
Home>Computer Science homework help>excel and tableu

Orders

Order ID Employee Customer Ship Via Ship Address Ship City Ship State/Province
30 Anne Hellung-Larsen Company AA Shipping Company B 789 27th Street Las Vegas NV
31 Jan Kotas Company D Shipping Company A 123 4th Street New York NY
32 Mariya Sergienko Company L Shipping Company B 123 12th Street Las Vegas NV
33 Michael Neipper Company H Shipping Company C 123 8th Street Portland OR
34 Anne Hellung-Larsen Company D Shipping Company C 123 4th Street New York NY
35 Jan Kotas Company CC Shipping Company B 789 29th Street Denver CO
36 Mariya Sergienko Company C Shipping Company B 123 3rd Street Los Angelas CA
37 Laura Giussani Company F Shipping Company B 123 6th Street Milwaukee WI
38 Anne Hellung-Larsen Company BB Shipping Company C 789 28th Street Memphis TN
39 Jan Kotas Company H Shipping Company C 123 8th Street Portland OR
40 Mariya Sergienko Company J Shipping Company B 123 10th Street Chicago IL
41 Nancy Freehafer Company G 123 7th Street Boise ID
42 Nancy Freehafer Company J Shipping Company A 123 10th Street Chicago IL
43 Nancy Freehafer Company K Shipping Company C 123 11th Street Miami FL
44 Nancy Freehafer Company A 123 1st Street Seattle WA
45 Nancy Freehafer Company BB Shipping Company C 789 28th Street Memphis TN
46 Robert Zare Company I Shipping Company A 123 9th Street Salt Lake City UT
47 Michael Neipper Company F Shipping Company B 123 6th Street Milwaukee WI
48 Mariya Sergienko Company H Shipping Company B 123 8th Street Portland OR
50 Anne Hellung-Larsen Company Y Shipping Company A 789 25th Street Chicago IL
51 Anne Hellung-Larsen Company Z Shipping Company C 789 26th Street Miami FL
55 Nancy Freehafer Company CC Shipping Company B 789 29th Street Denver CO
56 Andrew Cencini Company F Shipping Company C 123 6th Street Milwaukee WI
57 Anne Hellung-Larsen Company AA Shipping Company B 789 27th Street Las Vegas NV
58 Jan Kotas Company D Shipping Company A 123 4th Street New York NY
59 Mariya Sergienko Company L Shipping Company B 123 12th Street Las Vegas NV
60 Michael Neipper Company H Shipping Company C 123 8th Street Portland OR
61 Anne Hellung-Larsen Company D Shipping Company C 123 4th Street New York NY
62 Jan Kotas Company CC Shipping Company B 789 29th Street Denver CO
63 Mariya Sergienko Company C Shipping Company B 123 3rd Street Los Angelas CA
64 Laura Giussani Company F Shipping Company B 123 6th Street Milwaukee WI
65 Anne Hellung-Larsen Company BB Shipping Company C 789 28th Street Memphis TN
66 Jan Kotas Company H Shipping Company C 123 8th Street Portland OR
67 Mariya Sergienko Company J Shipping Company B 123 10th Street Chicago IL
68 Nancy Freehafer Company G 123 7th Street Boise ID
69 Nancy Freehafer Company J Shipping Company A 123 10th Street Chicago IL
70 Nancy Freehafer Company K Shipping Company C 123 11th Street Miami FL
71 Nancy Freehafer Company A Shipping Company C 123 1st Street Seattle WA
72 Nancy Freehafer Company BB Shipping Company C 789 28th Street Memphis TN
73 Robert Zare Company I Shipping Company A 123 9th Street Salt Lake City UT
74 Michael Neipper Company F Shipping Company B 123 6th Street Milwaukee WI
75 Mariya Sergienko Company H Shipping Company B 123 8th Street Portland OR
76 Anne Hellung-Larsen Company Y Shipping Company A 789 25th Street Chicago IL
77 Anne Hellung-Larsen Company Z Shipping Company C 789 26th Street Miami FL
78 Nancy Freehafer Company CC Shipping Company B 789 29th Street Denver CO
79 Andrew Cencini Company F Shipping Company C 123 6th Street Milwaukee WI
80 Andrew Cencini Company D 123 4th Street New York NY
81 Andrew Cencini Company C 123 3rd Street Los Angelas CA

Assignment: Use data from the 3 tables provided to create a new worksheet (name it 's tab, "flatfile") that combines all the data . Use this flatfile to create 3 Pivot Tables: one each to answer the following: Sales $ per State Sales $ per Category Sales per employee by State Import the three tables into Access and answer the same questions by creating a separate query for each question. Be sure to label the Pivot Table tabs and Access Queries to clearly indicate which question they answer. Keep track of the total time it took to complete the Excel portion of the assignment and also the total time taken to do the same in Access.

Additional notes: 1. the pivot table must be on their own worksheet and must start in cell "A1". 2. "Sales per employee by State" means to show the sales within a given state by employees selling in that state.

Order_Detail

Product ID Order ID Quantity
Northwind Traders Almonds 67 403
Northwind Traders Beer 30 113
Northwind Traders Beer 47 487
Northwind Traders Beer 55 354
Northwind Traders Boysenberry Spread 42 150
Northwind Traders Boysenberry Spread 77 188
Northwind Traders Cajun Seasoning 42 10
Northwind Traders Cajun Seasoning 76 30
Northwind Traders Chai 32 20
Northwind Traders Chai 44 212
Northwind Traders Chocolate 35 15
Northwind Traders Chocolate 39 348
Northwind Traders Chocolate 56 10
Northwind Traders Chocolate 74 111
Northwind Traders Chocolate 75 208
Northwind Traders Chocolate Biscuits Mix 33 70
Northwind Traders Chocolate Biscuits Mix 34 22
Northwind Traders Chocolate Biscuits Mix 42 10
Northwind Traders Chocolate Biscuits Mix 48 140
Northwind Traders Clam Chowder 36 200
Northwind Traders Clam Chowder 45 239
Northwind Traders Clam Chowder 51 101
Northwind Traders Clam Chowder 73 383
Northwind Traders Coffee 32 20
Northwind Traders Coffee 38 491
Northwind Traders Coffee 41 300
Northwind Traders Coffee 44 249
Northwind Traders Coffee 72 37
Northwind Traders Crab Meat 45 105
Northwind Traders Crab Meat 51 30
Northwind Traders Crab Meat 71 40
Northwind Traders Curry Sauce 37 17
Northwind Traders Curry Sauce 48 296
Northwind Traders Curry Sauce 63 85
Northwind Traders Curry Sauce 70 365
Northwind Traders Dried Apples 31 261
Northwind Traders Dried Apples 79 118
Northwind Traders Dried Pears 31 10
Northwind Traders Dried Pears 79 322
Northwind Traders Dried Plums 30 60
Northwind Traders Dried Plums 31 10
Northwind Traders Dried Plums 43 20
Northwind Traders Dried Plums 69 15
Northwind Traders Fruit Cocktail 78 307
Northwind Traders Gnocchi 80 314
Northwind Traders Gnocchi 81 0
Northwind Traders Green Tea 40 23
Northwind Traders Green Tea 43 112
Northwind Traders Green Tea 44 407
Northwind Traders Green Tea 81 0
Northwind Traders Long Grain Rice 58 56
Northwind Traders Marmalade 58 348
Northwind Traders Mozzarella 46 296
Northwind Traders Mozzarella 60 170
Northwind Traders Olive Oil 51 337
Northwind Traders Ravioli 46 100
Northwind Traders Scones 50 20
Northwind Traders Syrup 63 50

Products

ID Product Code Product Name List Price Category
74 NWTDFN-74 Northwind Traders Almonds $0.88 Dried Fruit & Nuts
34 NWTYUN-75 Northwind Traders Beer $14.00 Beverages
6 NWTJP-6 Northwind Traders Boysenberry Spread $28.94 Jams, Preserves
85 NWTBGM-85 Northwind Traders Brownie Mix $12.49 Baked Goods & Mixes
4 NWTCO-4 Northwind Traders Cajun Seasoning $24.79 Condiments
86 NWTBGM-86 Northwind Traders Cake Mix $12.96 Baked Goods & Mixes
1 NWTB-1 Northwind Traders Chai $18.00 Beverages
91 NWTCFV-91 Northwind Traders Cherry Pie Filling $1.21 Canned Fruit & Vegetables
99 NWTSO-99 Northwind Traders Chicken Soup $1.11 Soups
48 NWTCA-48 Northwind Traders Chocolate $12.75 Candy
19 NWTBGM-19 Northwind Traders Chocolate Biscuits Mix $11.50 Baked Goods & Mixes
41 NWTSO-41 Northwind Traders Clam Chowder $8.19 Soups
43 NWTB-43 Northwind Traders Coffee $47.61 Beverages
93 NWTCFV-93 Northwind Traders Corn $0.08 Canned Fruit & Vegetables
40 NWTCM-40 Northwind Traders Crab Meat $18.40 Canned Meat
8 NWTS-8 Northwind Traders Curry Sauce $9.52 Sauces
51 NWTDFN-51 Northwind Traders Dried Apples $1.77 Dried Fruit & Nuts
7 NWTDFN-7 Northwind Traders Dried Pears $26.37 Dried Fruit & Nuts
80 NWTDFN-80 Northwind Traders Dried Plums $1.11 Dried Fruit & Nuts
17 NWTCFV-17 Northwind Traders Fruit Cocktail $37.33 Canned Fruit & Vegetables
56 NWTP-56 Northwind Traders Gnocchi $38.00 Pasta
82 NWTC-82 Northwind Traders Granola $3.28 Cereal
92 NWTCFV-92 Northwind Traders Green Beans $0.77 Canned Fruit & Vegetables
81 NWTB-81 Northwind Traders Green Tea $1.55 Beverages
97 NWTC-82 Northwind Traders Hot Cereal $3.99 Cereal
65 NWTS-65 Northwind Traders Hot Pepper Sauce $16.93 Sauces
52 NWTG-52 Northwind Traders Long Grain Rice $6.83 Grains
20 NWTJP-6 Northwind Traders Marmalade $51.54 Jams, Preserves
72 NWTD-72 Northwind Traders Mozzarella $34.80 Dairy Products
77 NWTCO-77 Northwind Traders Mustard $13.00 Condiments
5 NWTO-5 Northwind Traders Olive Oil $18.23 Oil
89 NWTCFV-89 Northwind Traders Peaches $1.50 Canned Fruit & Vegetables
88 NWTCFV-88 Northwind Traders Pears $0.75 Canned Fruit & Vegetables
94 NWTCFV-94 Northwind Traders Peas $1.50 Canned Fruit & Vegetables
90 NWTCFV-90 Northwind Traders Pineapple $1.20 Canned Fruit & Vegetables
83 NWTCS-83 Northwind Traders Potato Chips $1.15 Chips, Snacks
57 NWTP-57 Northwind Traders Ravioli $22.17 Pasta
21 NWTBGM-21 Northwind Traders Scones $10.00 Baked Goods & Mixes
96 NWTCM-96 Northwind Traders Smoked Salmon $4.42 Canned Meat
3 NWTCO-3 Northwind Traders Syrup $10.00 Condiments
87 NWTB-87 Northwind Traders Tea $4.00 Beverages
66 NWTS-66 Northwind Traders Tomato Sauce $20.19 Sauces
95 NWTCM-95 Northwind Traders Tuna Fish $1.91 Canned Meat
98 NWTSO-98 Northwind Traders Vegetable Soup $1.08 Soups
14 NWTDFN-14 Northwind Traders Walnuts $23.25 Dried Fruit & Nuts

Hidden

File Name Last Name First Name RedID Creation date Pivot1 (rows/Total) Pivot2 (rows/Total) Pivot3 (rows/Total)
Counter
1