Access Assignment
vk4queen
IntroData.xlsxVLOOKUP
| VLOOKUP Practice | |||||||
| Data | |||||||
| Employee | No. Customers | Net Sales | Profit/Loss | Questions | Answer | Formula | |
| Joseph | 8 | 1,592 | 563 | 1. What were the net sales for John? | =VLOOKUP("John",$A$4:$D$17,3,FALSE) | ||
| John | 8 | 1,088 | 397 | 2. How many customers did Jamie have? | =VLOOKUP("Jamie",$A$4:$D$17,2,FALSE) | ||
| Josh | 8 | 1,680 | 753 | 3. What is the profit for Jessy? | =VLOOKUP("Jessy",$A$4:$D$17,4,FALSE) | ||
| Jamie | 9 | 2,133 | 923 | 4. How many customers did Joshua have? | =VLOOKUP("Joshua",$A$4:$D$17,2,FALSE) | ||
| Jackie | 10 | 1,610 | 579 | 5. How many customers did Joshua have? No Error | =IFERROR(VLOOKUP("Joshua",$A$4:$D$17,2,FALSE), "Not Found :-(") | ||
| Johnson | 10 | 1,540 | 570 | ||||
| Jonathan | 7 | 1,316 | 428 | ||||
| Jagjit | 7 | 1,799 | 709 | ||||
| Jairam | 8 | 1,624 | 621 | ||||
| Jessy | 6 | 726 | 236 | ||||
| Javed | 9 | 2,277 | 966 | ||||
| Jimmy | 6 | 714 | 221 | ||||
| Juno | 9 | 2,682 | 1,023 |
OriginalData
| V# | Vendor | Vendor Street | Vendor City | PO# | PO Date | Shipper | POLine# | Qty | Item# | Item Description | Cost | E# | Signed by |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 328 | 12/16/13 | Interstate Motor Freight | 1 | 115 | BB-019 | Basketball pole pad | 94 | 1 | Ray Kramer |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 328 | 12/16/13 | Interstate Motor Freight | 2 | 60 | BB-538 | Basketball bag | 26 | 1 | Ray Kramer |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 328 | 12/16/13 | Interstate Motor Freight | 3 | 70 | BB-688 | Portable inflation pump | 69 | 1 | Ray Kramer |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 328 | 12/16/13 | Interstate Motor Freight | 4 | 85 | BB-926 | Trainers first aid kit | 30 | 1 | Ray Kramer |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 329 | 1/1/14 | Allied Trucking | 5 | 25 | BB-538 | Basketball bag | 26 | 3 | Nancy Ford |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 329 | 1/1/14 | Allied Trucking | 6 | 20 | BB-019 | Basketball pole pad | 94 | 3 | Nancy Ford |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 329 | 1/1/14 | Allied Trucking | 7 | 60 | FB-225 | Football helmet | 90 | 3 | Nancy Ford |
| 253 | Chicago Office Supply | 1411 E. Michigan | Chicago, IL 60606 | 330 | 1/1/14 | Our truck | 8 | 2 | LT-100 | Laptop computer | 1000 | 1 | Ray Kramer |
| 253 | Chicago Office Supply | 1411 E. Michigan | Chicago, IL 60606 | 331 | 1/10/14 | Interstate Motor Freight | 9 | 2 | DK-200 | Desk | 200 | 3 | Nancy Ford |
| 253 | Chicago Office Supply | 1411 E. Michigan | Chicago, IL 60606 | 331 | 1/10/14 | Interstate Motor Freight | 10 | 1 | LT-100 | Laptop computer | 1000 | 3 | Nancy Ford |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 332 | 1/15/14 | Interstate Motor Freight | 11 | 22 | BB-688 | Portable inflation pump | 69 | 3 | Nancy Ford |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 332 | 1/15/14 | Interstate Motor Freight | 12 | 13 | BB-019 | Basketball pole pad | 94 | 3 | Nancy Ford |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 332 | 1/15/14 | Interstate Motor Freight | 13 | 48 | FB-225 | Football helmet | 90 | 3 | Nancy Ford |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 332 | 1/15/14 | Interstate Motor Freight | 14 | 34 | BB-926 | Trainers first aid kit | 30 | 3 | Nancy Ford |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 | 332 | 1/15/14 | Interstate Motor Freight | 15 | 14 | BB-538 | Basketball bag | 26 | 3 | Nancy Ford |
| 253 | Chicago Office Supply | 1411 E. Michigan | Chicago, IL 60606 | 333 | 1/1/14 | Our truck | 16 | 1 | DK-200 | Desk | 200 | 3 | Nancy Ford |
POLineItems
| POLine# (PK) | PO# (FK) | Item# (FK) | Qty | Cost |
| 1 | 328 | BB-019 | 115 | 94 |
| 2 | 328 | BB-538 | 60 | 26 |
| 3 | 328 | BB-688 | 70 | 69 |
| 4 | 328 | BB-926 | 85 | 30 |
| 5 | 329 | BB-538 | 25 | 26 |
| 6 | 329 | BB-019 | 20 | 94 |
| 7 | 329 | FB-225 | 60 | 90 |
| 8 | 330 | LT-100 | 2 | 1000 |
| 9 | 331 | DK-200 | 2 | 200 |
| 10 | 331 | LT-100 | 1 | 1000 |
| 11 | 332 | BB-688 | 22 | 69 |
| 12 | 332 | BB-019 | 13 | 94 |
| 13 | 332 | FB-225 | 48 | 90 |
| 14 | 332 | BB-926 | 34 | 30 |
| 15 | 332 | BB-538 | 14 | 26 |
| 16 | 333 | DK-200 | 1 | 200 |
PO
| PO# (PK) | PO Date | Shipper | E# (FK) | V# (FK) |
| 328 | 12/16/13 | Interstate Motor Freight | 1 | 252 |
| 329 | 1/1/14 | Allied Trucking | 3 | 252 |
| 330 | 1/1/14 | Our truck | 1 | 253 |
| 331 | 1/10/14 | Interstate Motor Freight | 3 | 253 |
| 332 | 1/15/14 | Interstate Motor Freight | 3 | 252 |
| 333 | 1/1/14 | Our truck | 3 | 253 |
Inventory
| Item# (PK) | Item Description |
| BB-019 | Basketball pole pad |
| BB-538 | Basketball bag |
| BB-688 | Portable inflation pump |
| BB-926 | Trainers first aid kit |
| FB-225 | Football helmet |
| LT-100 | Laptop computer |
| DK-200 | Desk |
Vendor
| V# (PK) | Vendor | Vendor Street | Vendor City |
| 252 | Velocity Sporting Goods | 1258 Colgrove Ave | Pierre, SD 57501 |
| 253 | Chicago Office Supply | 1411 E. Michigan | Chicago, IL 60606 |
Employee
| E# (PK) | Signed by |
| 1 | Ray Kramer |
| 3 | Nancy Ford |
OrderDetails
| V# (PK) | Vendor | Vendor Street | Vendor City | PO# (PK) | PO Date | Shipper | E# (FK) | V# (FK) | POLine# (PK) | PO# (FK) | Item# (FK) | Qty | Cost | Item# (PK) | Item Description | E# (PK) | Signed by |
