ch05ex02_solution.accdb
| Employee_ID | Last_Name | First_Name | UG_Degree | Adv_Degree | Years_Exp | Skill_1 | Skill_2 | Skill_3 |
| A321 | Alverez | Hernando | BS Computer Science | | 4.0 | 1.0 | 2.0 | 4 |
| B130 | Boston | Matt | BS Computer Science | MS Computer Science | 3.0 | 4.0 | 5.0 |
|
| B195 | Barton | Alice | BS Management | MBA | 1.0 | 1.0 | 5.0 |
|
| C013 | Cox | Amanda | BS Accounting | | 3.0 | 2.0 | 5.0 |
|
| C427 | Cary | Jane | BS Computer Science | MS Computer Science | 5.0 | 1.0 | 5.0 |
|
| F430 | Francis | Tom | BS Logistics | | 2.0 | 4.0 | 5.0 |
|
| J045 | Johns | Carl | BS Mathematics | | 2.0 | 2.0 | 5.0 |
|
| J342 | Johnson | Sally | BS Education | | 7.0 | 3.0 | 4.0 |
|
| M432 | Morgan | Thomas | BS Mathematics | | 7.0 | 4.0 | |
|
| N541 | Nottingham | Henry | BS Mathematics | | 3.0 | 1.0 | 2.0 |
|
| P549 | Palmer | James | BS Electical Engineering | | 5.0 | 2.0 | 4.0 |
|
| R430 | Randal | Joyce | BS Economics | MBA | 2.0 | 5.0 | 6.0 |
|
| S0343 | Soloman | Robert | BS Computer Science | | 6.0 | 2.0 | |
|
| T078 | Thornton | Robert | BS History | | 4.0 | 3.0 | |
|
| T871 | Trenton | Mary | BS Mathematics | MS Mathematics | 3.0 | 2.0 | 4.0 |
|
| U039 | Underwood | Frank | BS Accounting | | 2.0 | 4.0 | 5.0 |
|
| V321 | Victor | Albert | BS Computer Science | MS Computer Science | 3.0 | 1.0 | 3.0 |
|
| W821 | Watson | Helen | BS Computer Science | | 8.0 | 2.0 | |
|
| Y326 | Yee | Han | BS Accounting | MS Accounting | 6.0 | 5.0 | |
|
| Project_ID | Client | Project_Type1 | Project_Type2 | Years_Exp | Skill_1 | Skill_2 | Skill_3 | Preferred_Degree1 | Preferred_Degree2 | Preferred_Degree3 |
| P036 | JM Logistics Inc. | 1.0 | | 4.0 | 1.0 | 4.0 | | BS Computer Science | BS Mathematics |
|
| P037 | Jefferson Automotive | 4.0 | | 5.0 | 4.0 | 5.0 | 1.0 | MBA | |
|
| P038 | World-wide Sourcing | 2.0 | 4.0 | 5.0 | 2.0 | 4.0 | | BS Accounting | MBA |
|
| P039 | Solar Systems | 2.0 | | 2.0 | 2.0 | | | BS Mathematics | BS Computer Science | BS Accounting |
| P040 | Robert's Heating Supply | 3.0 | | 2.0 | 3.0 | | | none | |
|
| P041 | Casual Dining, Inc. | 2.0 | | 3.0 | 2.0 | 1.0 | | BS Computer Science | |
|
| P042 | US Brokerage | 1.0 | 2.0 | 5.0 | 1.0 | 2.0 | 5.0 | BS Computer Science | BS Mathematics | MBA |
| P043 | Computer Chips, Inc. | 4.0 | | 3.0 | 4.0 | 5.0 | 1.0 | none | |
|
| P044 | Southeast Region Youth Ministries | 1.0 | 2.0 | 4.0 | 2.0 | 1.0 | | BS Computer Science | BS Mathematics |
|
| P045 | Huston Power Co. | 1.0 | | 5.0 | 1.0 | | | none | |
|
| P046 | Helen's Clothiers | 4.0 | | 3.0 | 4.0 | 1.0 | | BS Computer Science | BS Accounting | BS Mathematics |
| P047 | McMasters Printing | 3.0 | | 2.0 | 3.0 | | | none | |
|
| P048 | United Grocers | 4.0 | | 5.0 | 1.0 | 4.0 | 5.0 | MBA | |
|
| P049 | Shelby County Women's Shelter | 1.0 | | 2.0 | 1.0 | 5.0 | | none | |
|
| P050 | National Distributors | 1.0 | 3.0 | 5.0 | 1.0 | 3.0 | 2.0 | BS Accounting | |
|
| Skill_ID | Description |
| 1.0 | Database management |
| 2.0 | Business intelligence |
| 3.0 | Web design |
| 4.0 | ERP systems |
| 5.0 | Project management |
SELECT Employees.Employee_ID, Projects.Project_ID, IIf([Employees]![Years_Exp]>=[Projects]![Years_Exp],"Yes","No") AS Sufficient_Experience
FROM Employees, Projects
WHERE (((Employees.Employee_ID)="T871"));
SELECT Projects.Client, IIf([Employees]![Years_Exp]>=[Projects]![Years_Exp],"Yes","No") AS Sufficient_Experience, Employees.Years_Exp, Employees.First_Name, Employees.Last_Name
FROM Projects, Employees
WHERE (((Projects.Client)="US Brokerage"))
ORDER BY IIf([Employees]![Years_Exp]>=[Projects]![Years_Exp],"Yes","No") DESC;
qkiii123
