below attached AIS data query assignment. All the instructions are listed below.

INVENTORY ITEM ID ID 1101 1102 1103 1104 1105 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1119 1120 1122 1123 1124 1125 1126 1128 1129 1130 1132 1133 1135 1136 1138 1139 1140 1141 1143 1146 1148 1153 1155 116 422 440 455 449 134 275 353 314 443 254 107 470 407 200 371 386 377 278 269 401 368 311 362 464 464 398 401 125 338 410 185 431 227 395 440 389 293 DESCRIPTIO N PRICE ON HAND $8.10 $5.30 $7.70 $5.30 $7.60 $10.30 $8.00 $13.70 $6.40 $6.60 $8.80 $9.50 $15.50 $5.30 $8.30 $9.10 $7.60 $9.90 $8.00 $8.40 $4.50 $9.50 $8.90 $8.40 $6.80 $12.30 $7.90 $5.30 $7.00 $8.20 $5.30 $5.30 $7.90 $6.30 $5.30 $5.90 $5.60 $8.60 512 3190 -130 3380 3300 443 354 354 4390 1770 456 5090 354 3040 141 541 1330 380 354 354 4740 71 5600 148 472 354 3790 1000 291 2250 5240 -70 2510 354 3180 5020 -30 545 CRP 400 200 100 350 1500 400 500 350 400 250 200 200 200 250 120 500 1000 350 250 250 350 0 350 120 350 200 200 350 200 100 5000 50 150 350 300 200 50 250 BEVERAGE TYPE T C C C C T T T C C T C T C T T C T T T C T C T T T C C T C C C C T C C C T 1158 1159 1160 1162 1163 1164 1165 1167 1168 1170 1171 1173 1174 1175 1176 1177 1179 1181 1183 1184 1185 1186 1188 1189 1190 1191 1192 1194 1195 1196 1197 1200 1202 1203 1204 1205 1206 1207 1208 1209 446 419 146 206 176 209 344 242 101 302 104 425 428 191 434 467 425 137 365 236 203 257 344 452 383 422 119 266 188 443 260 290 263 359 317 143 272 374 452 122 $5.60 $6.40 $9.10 $12.20 $11.50 $8.30 $7.00 $7.70 $5.50 $44.50 $7.40 $7.90 $7.00 $11.70 $5.30 $13.90 $5.90 $19.80 $7.50 $6.90 $22.70 $10.90 $3.80 $5.30 $7.20 $4.50 $13.30 $18.00 $3.90 $4.50 $7.10 $8.00 $6.70 $8.10 $7.20 $9.90 $8.00 $8.40 $4.50 $6.20 1780 -30 3670 273 354 501 880 343 1570 106 4000 1520 740 474 5420 354 4360 196 202 443 444 238 5470 1410 4140 2220 429 2740 2770 1640 2230 131 1000 -125 1030 515 354 260 -110 468 350 0 250 350 350 350 700 200 1000 0 100 350 350 200 300 350 350 200 200 150 500 350 5000 1000 350 200 200 2500 350 50 2000 0 350 120 200 200 300 200 100 300 C C C T T T C T C T C C C T C T C T T T T T C T C C T T C C C T C T C T C T C T 1210 1211 1212 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1229 1230 1231 1232 1234 1235 1236 1239 1240 1241 1243 1244 1246 1247 1250 1253 1254 416 458 197 326 212 251 101 416 458 386 359 248 305 230 356 404 170 413 308 392 287 233 323 404 194 437 461 182 179 296 173 284 281 $5.60 $14.70 $19.00 $4.70 $7.20 $3.80 $3.90 $6.40 $8.00 $5.60 $12.90 $7.20 $15.20 $7.00 $12.90 $5.80 $12.90 $7.60 $10.20 $5.70 $9.90 $7.10 $10.80 $7.80 $6.50 $5.30 $16.10 $7.40 $12.80 $8.80 $11.60 $9.60 $7.70 1850 354 463 3640 324 -67 1830 5160 297 3260 354 54 511 354 190 -230 354 4080 444 5530 354 226 6000 4120 6750 1400 354 2000 354 354 354 354 354 250 250 400 3000 250 0 250 200 350 200 250 0 300 300 120 100 350 200 350 350 300 200 500 500 150 400 250 200 150 350 150 400 400 C T T C T T C C T C T C T T T C T C T C T T C C T C T C T T T T T COUNTRY CAFFEINATED FLAVORED COUNTRY ID NAME YES NO YES NO NO YES YES NO NO NO NO YES YES NO YES YES YES NO YES YES YES NO YES YES YES NO NO NO YES YES NO NO NO YES NO YES YES YES NO YES YES YES YES NO YES NO NO YES NO NO YES YES NO NO YES NO YES YES YES YES NO NO YES YES YES YES NO NO YES NO YES NO YES YES YES YES 82 0 0 0 0 41 0 82 83 0 56 45 0 0 41 41 0 144 0 93 0 90 61 90 0 0 0 0 163 0 0 42 0 56 0 0 0 0 YES NO YES YES YES YES NO YES NO YES YES NO NO YES NO NO YES YES YES NO YES NO YES NO NO YES YES YES NO YES YES YES YES YES NO YES YES YES YES YES NO YES NO NO NO NO NO NO NO NO NO YES NO NO YES YES YES NO NO NO NO NO NO YES NO YES NO NO NO YES NO YES NO YES NO NO YES NO YES NO 177 0 151 90 127 121 28 0 0 41 45 0 0 41 0 0 0 82 41 68 90 116 28 0 0 0 41 183 88 0 0 0 116 41 93 93 0 41 0 163 YES NO YES YES YES YES YES NO YES NO NO NO YES NO YES YES YES NO YES YES YES YES YES NO NO NO YES NO NO YES NO NO YES YES YES NO NO NO NO NO YES YES YES YES NO NO NO NO YES NO YES NO YES YES NO NO YES NO YES NO YES NO NO NO YES YES 0 93 90 83 0 41 0 0 93 0 41 56 90 0 41 0 68 0 41 0 93 0 83 0 82 0 82 42 0 0 68 0 0 ITEM ID 101 104 107 110 116 119 122 125 134 137 143 146 152 155 158 161 164 167 170 173 176 179 182 185 188 191 194 197 200 203 206 209 212 215 221 224 227 230 BIN D120 C102 B115 A104 A109 C109 D103 D117 B119 C128 C112 D109 D107 C119 A110 C124 C115 B106 D102 B123 B108 A126 C104 A106 A124 D116 A122 D112 B122 A130 C110 C118 A114 C114 B111 A117 A119 C108 DESCRIPTION Continental Blend Costa Rica Tarrazu Costa Rica La Manita Sumatra Mandheling Darjeeling Badamtam Assam Tara FTGFOP-1 Ceylon Pekoe Labookelle Ceylon Supreme China Yunnan Darjeeling Namring Kenya Kaproret Mocha Zimbabwe Ethiopia Mokas Guatemala Antigua Yemen Mocha Jamaican Blue Mountain Ethiopia Sidamo Berry Patch Freesia's Garden New Zealand Sunnyslopes Passionfruit Fruit Blend Columbia Bucaramanga Especial Columbia Supremo Vienna An Hui Silver Sprout Green Jakarta Clove Green Sencha Fukujya Gunpowder Hunan Gu Zhang Mao Jian Japanese Cherry Sakura Morrocan Green Mint Pan Fired Green Guatemala Huehuetenago Kilimanjaro New Guinea Euco Mint Garden Treat 233 236 242 248 251 254 257 260 263 266 269 272 275 278 281 284 287 290 293 296 299 302 305 308 311 314 317 320 323 326 332 335 338 341 344 347 350 353 356 359 D115 C116 A132 A113 A105 B102 C101 B110 C129 D111 C107 B131 B124 B101 C121 A108 A107 C120 C106 B114 B127 B103 D119 B116 C127 D114 B107 B129 C123 B109 D101 A129 A101 A128 B105 D106 B113 B126 A103 C105 Lemon Ainger Rose Potpourri Orange Peel Comfrey Leaves Lemon Grass Chamomile Blossom Hibiscus Espresso Roast Mexican Coatepec Kona Extra Fancy Passionfruit with Flowers Peach Raspberry Rumba Strawberry Vanilla with Vanilla Bean Mango Black Cherry Black Currant Blue Lady Nicaraguan Matagalpa Formosa Silvertip Oolong Fujian Oolong Ti Kuan Yin Orange Blossom Oolong Ethiopia Yergacheffe Mocha Java Kenya AA Yukon Blend Sulawesi Peaberry Java Celebes Kalossi Brazil Sul De Minas Cerra Italian Roast Haiti Strictly High Grown Washed Brazil Bourbon Santos Tanzania Moshi Chanchamayo English Breakfast Chai Earl Grey 362 365 368 371 374 377 380 383 386 389 392 395 398 401 404 407 410 413 416 419 422 425 428 431 434 437 440 443 446 449 452 455 458 461 464 467 470 B120 C113 C125 A123 C126 A121 B130 B132 C131 A120 A125 C132 C111 D110 C103 A111 B128 A131 B104 B125 C122 D113 C117 A115 C130 D104 B118 D118 B117 B112 D105 A118 B121 A102 D108 A116 A127 A112 Guangxi Guihua Irish Blend Jasmine Lapsang Souchong Queen Victoria Russian Blend Ethiopia Harrar French Roast Almond Amaretto Apricot Cordial Chocolate Brandy Chocolate Cinnamon Chocolate Hazelnut Blue Mountain Chocolate Mint Chocolate Raspberry Cinnamon Coconut Cream Dark Chocolate Almond Dutch Chocolate French Vanilla Hazelnut House Blend Irish Cream Kahlua Macadamia Seville Orange Southern Pecan Turkish Vanilla Almond Vanilla Nut Creme Vanilla Nut Fudge Apricot Darjeeling Orange Spice Passionfruit Vanilla Country ID 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Country Name FiFi Republic Afghanistan Albania Algeria American Samoa Andorra Angola Anguilla Antarctica Antigua and Barbuda Argentina Armenia Aruba Australia Austria Azerbaijan Bahama Islands Bahrain Bangladesh Barbados Belarus Belgium Belize Benin Bhutan Bolivia Bosnia and Herzegowina Botswana Brazil Brunei Bulgaria Burkina Faso Burma Burundi Cambodia Cameroon Canada Cape Verde 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 Central African Republic Chad Chile China Colombia Comoros Congo Costa Rica Cote D'Ivoire Croatia Cuba Cyprus Czech Republic Denmark Djibouti Dominica Dominican Republic Ecuador Egypt El Salvador Equatorial Guinea Eritrea Estonia Ethiopia Fiji Finland France Gabon Gambia Georgia Germany Ghana Great Britain Greece Greenland Grenada Guatemala Guinea Guinea-Bissau Guyana 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 Haiti Honduras Hungary Iceland India Indonesia, Republic of Iran Iraq Ireland Israel Italy Jamaica Japan Jordan Kazakhstan Kenya Kiribati Korea, North Korea, South Kuwait Kyrgyzstan Laos Latvia Lebanon Lesotho Liberia Liechtenstein Lithuania Luxembourg Macedonia Madagascar Malawi Malaysia Maldives Mali Malta Marshall Islands Mauritania Mexico Micronesia, Federated States of 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 Moldova Monaco Mongolia, Republic of Morocco Mozambique Namibia Nauru Nepal Netherlands New Zealand Nicaragua Niger Nigeria Norway Oman Pakistan Palau, Republic of Panama Papua New Guinea Paraguay Peru Philippines Poland Portugal Qatar Romania Russia Rwanda Saint Kitts and Nevis Saint Lucia Saint Vincent/the Grenadines San Marino Sao Tome and Principe Saudi Arabia Senegal Serbia and Montenegro Seychelles Sierra Leone Singapore Slovakia 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 Slovenia Solomon Islands Somalia South Africa Spain Sri Lanka Sudan Suriname Swaziland Sweden Switzerland Syria Taiwan Tanzania, United Republic of Thailand Togo Tonga Trinidad and Tobago Tunisia Turkey Turkmenistan Tuvalu Uganda Ukraine United Arab Emirates United States Uruguay Uzbekistan Vanuatu Vatican City Venezuela Vietnam Western Samoa Yemen Zaire Zambia Zimbabwe Part I - Data Query Assignment - Excel Maxi\tHsu\towns\tand\toperates\tHSU's\tGourmet\tBeverage\tCo.,\twhich\twas\tstarted\tby\this\tfather,\tKo\tHsu,\tin 1900. Upon\this\tfather's\tretirement\tin\t2010,\tMaxi\ttook\tover\tthe\tbusiness. Lacking\tadequate\tcomputer training,\tMaxi\thas\tasked\tyou\t[a\tComputer\tSpecialist\tof\tHSU's]\tto\tinstall\tand\tdesign\ta\tcomputer\tsystem. With\tyour\tassistance,\tMaxi\tmaintains\this\tinventory\ton\tan\tExcel\tfile,\t2017\tINVENTORY. The\tspreadsheet contains\t3\tworksheets,\tDATA,\tDESCRIPTION,\tand\tCOUNTRY. Requirements: Maxi\tHsu\twould\tlike\tyour\tassistance\tin\tgenerating\tspecial\treports\tfrom\this\tinventory\tdata. Specially,\the would\tlike\tthe\tfollowing\t[Please\tfollow\tthe\tEXACT\tORDER\tof\tfields\tas\tinstructed]\t-\tSetup\tall\tfour\tcopy-to [output]\tranges\tin\tthe\tSAME\tworksheet: 1. Use\tVLOOKUP\tfunction\tto\tcopy\tCOUNTRY\tNAME\tfrom\tCOUNTRY\tto\tINPUT\tworksheet\t&\tcopy\tNAME from\tDESCRIPTION\tto\tINPUT\tworksheet. 2. Use\tAdvanced\tQuery\tto\tgenerate\ta\tLIST\tof\tinventory\tfor\tTEA\t[coded\tas\tT]\tCAFFEINATED\t[coded\tas YES]\tAND\tFLAVORED\t[coded\tas\tYES]. Display\t(only\tinclude\tfollowing\ttitles\tand\torder): DESCRIPTION,\tBEVERAGE\tTYPE,\tPRICE,\tCAFFEINATED,\tFLAVORED\t&\tCOUNTRY\tNAME. Name\tthe\tCopy-To\trange\tas\tReport\t1. 3. Use\tAdvanced\tQuery\tto\tgenerate\ta\treport\tof\tinventory\tfor\tON\tHAND\tless\tthan\tzero. Display: DESCRIPTION,\tPRICE,\tON\tHAND,\tCAFFEINATED,\t&\tCOUNTRY\tNAME. Name\tthe\tCopy-To\trange\tas\tReport\t2. 4. Use\tAdvanced\tQuery\tto\tgenerate\ta\treport\tof\tinventory\twith\tPRICE\t>\t$12\tOR\tON\tHAND\t>\t3,000. Display: INVENTORY\tID,\tDESCRIPTION,\tPRICE,\t&\tON\tHAND. Name\tthe\tCopy-To\trange\tas\tReport\t3. 5. Use\tAdvanced\tQuery\tto\tgenerate\ta\treport\tof\tinventory\tfor\tCRP\t[Critical\tReorder\tPoint]\tGreater\t[>] than\tON\tHAND. Display: ITEM\tID,\tDESCRIPTION,\tCRP,\t&\tON\tHAND. Name\tthe\tCopy-To\trange\tas\tReport\t4. 6. Save\tthe\tExcel\tfile. Fall 2017 Data Query - 1 Part II - Data Query Assignment - Access Maxi\tHsu\twas\tso\timpressed\tby\tyour\twonderful\tjob\tand\tdecided\tgave\tyou\ta\t200%\tbonus. However,\ta couple\tdays\tlater,\the\tlearned\tfrom\ta\tfriend\tthat\tMS\tAccess\tis\tan\teasier\tprogram\tthen\tExcel\tto\tuse\tfor\tthis type\tof\ttasks. Maxi\twas\tupset\tbecause\tyou\tdid\tnot\tinform\thim\tabout\tthe\tpower\tof\tMS\tAccess. He\tthen decided\tto\ttake\tback\tthe\t200%\tbonus\tunless\tyou\tuse\tthe\tMS\tACCESS\tto\tgenerate\tthe\tabove\treports. Using\tthe\tsame\tinformation\t&\tapplying\tnormalization\tand\tcardinality\trules,\tyou\thave\tcreated\tthree tables: Country;\tData;\tand\tDescription\tin\tthe\tAccess\tdatabase,\tINVENTORY\t2017\t[Note: Only\tlater version\tof\tAccess\tincludes\tSAVE\tAS\tfeather\tto\tsave\tdatabase\tunder\tdifferent\tname]. You\thave\tdecided\tto use\tREPORT\tWIZARD\tto\tgenerate\treports\tfor\tthe\tabove\tFOUR\tqueries. Name\tfour\tqueries\tas\tQuery\t1,\t2, 3,\tand\t4\trespectively. Name\tqueries\tas\tQuery\t1,\t2,\t3,\tand\t4\trespectively. ** Compact & Repair\tthe\tdatabase\tprior\texisting\tthe\tAccess\tprogram: File\t/\tClick\tCompact\t&\tRepair\tDatabase. Part III - Pivot Tables Use\tthe\tPIVOT\t2017\tto\tanswer\tthe\tfollowing\tquestions\t(Setup\tfollowing\tfour\tpivot\tPIVOT\tworksheet): Requirements: 1. How\tmany\taccounts\t[Customer]\twere\topened\tat\teach\tbranch,\tbroken\tdown\tby\taccount\ttype? 2. What\tis\tthe\tdollar\tdistribution\t[Amount]\tin\tdifferent\taccount\ttypes? 3. Which\tbranch\topens\tthe\tmost\taccounts\tfor\tnew\tcustomers? 4. How\tmuch\t[Amount]\twere\topened\tduring\tthe\tfirst\tand\tsecond\t[15\tdays]\thalf\tof\tMay\tby\taccount type? 5. Save\tthe\tExcel\tfile\t(DO\tNOT\tCHANGE\tTHE\tFILE\tNAME). PLEASE\tUSE\tTHE\tCORRECT\tFILE\tNAMES\tAS\tINSTRUCTED!!! Fall 2017 Data Query - 2 ####Standard ACE DB#####n#b` Ugr@? #~1y0 c FNS7U(##`\\6B>MFBFH>@O8H#####f?#####f:#####f;#####f