Detailed instruction is attached as a .zip file along with the Database and a sample file. I've done one example for you, so all you need to do is create 3 different queries that fulfill the requirements indicated in the guidelines. There are 8 requirements, and my example fulfilled the only one: "A query using an inner join." Therefore, 7 requirements need to be distributed over the 3 queries. Also, a brief report (each query should be ~250 words long). Please let me know if you need clarifications or have any questions.
Access Database.accdb BranchIdBranchNameMangerId 3Toronto Reference Library6 4North York Central Library8 5City Hall Branch10 6Don Mills Branch13 7Fairview Branch16 BranchIdSignUpDateClientId 73/15/034 36/12/105 311/11/157 52/14/199 43/19/1712 78/29/2015 67/2/2120 41/8/2121 69/13/0922 56/30/1423 DonationIdDonorNameDonationAmountBranchId 1Zach Quinn¤ 1,000.007 2Toronto Library Foundation¤ 100,000.003 3DHX Media¤ 4,000.007 BirthDateSexSalaryBranchIdManagerIdEmployeeId 2/20/62F¤ 105,250.00366 11/2/60M¤ 103,080.00488 5/30/72F¤ 101,125.0051010 10/2/92F¤ 72,300.0071611 4/1/59M¤ 120,800.0061313 3/3/95M¤ 68,500.0061314 2/26/85F¤ 93,000.0071616 6/2/90M¤ 70,400.0051017 9/20/70F¤ 88,250.003618 12/23/88F¤ 82,300.004819 ClientIdEmployeeId 126 208 128 1219 1510 2318 411 2111 2013 519 717 918 216 913 917 ManagerStartDateManagerId 4/12/026 6/30/028 8/15/0910 4/1/1313 3/20/2016 PersonIdFirstNameLastName 5AabyBrown 6DavidRodriquez 7MartinezSmith 8WilliamHwang 9JohnZager 10CatherineEarwood 11AnnetteLi 12AgnesLong 13TonyJun 14KevinLee 15LancyOrsburn 16AlyssaBeaudoin 17JeremyZucker 18RoseSt. Pierre 19HeatherTinker 20WendyZang 21JasonCooper 22LisaCade 23KyleFablo 4JennyJohnson SELECT Person.FirstName, Person.LastName, Manager.ManagerId, Branch.BranchName FROM (Person INNER JOIN Manager ON Person.PersonId = Manager.ManagerId) INNER JOIN Branch ON Manager.ManagerId = Branch.MangerId ORDER BY Person.LastName; Sample.docx Query: Managers Name and Branch · This query is used to search what branch the managers are working at. Both the managers’ first and last names are displayed and their unique ID to distinguish those with the same names. The branch’s names are displayed to indicate where each of the managers is working. · -- A list of all Managers and their Branch (sorted by the Last Name) SELECT Person.FirstName, Person.LastName, Manager.ManagerId, Branch.BranchName FROM (Person INNER JOIN Manager ON Person.PersonId = Manager.ManagerId) INNER JOIN Branch ON Manager.ManagerId = Branch.MangerId ORDER BY Person.LastName; · There are two inner joins in this statement—one for Person-Manager, and the other for Manager-Branch. The joined tables create a new table that will present the matching records from both tables. For example, ‘PersonId’ from the ‘Person’ table matches the ‘ManagerId’ from the ‘Manager’ table. Thus, the matched records will be shown in a newly created table. · Fields ‘FirstName’ and ‘LastName’ will be displayed from the ‘Person’ table, ‘ManagerId’ from the ‘Manager’ table, and ‘BranchName’ from the ‘Branch’ table. · The resulting query is sorted by the field ‘LastName’ in ascending (alphabetical) order. · WHERE clause is not present. Guidelines.docx Guidelines Your database must include at least 4 queries that fulfill all the following criteria between them: · A sort on one field and a sub-sort on a second field. · An “AND” criteria (“AND” must appear in the SQL). · An “OR” criteria (“OR” must appear in the SQL). · An “AND” and “OR” criteria used within the same query. · At least 5 fields from at least 2 tables with at least one field not displayed that is used for sorting or querying. · An example of a field’s information being queried using a parameter in the criteria. · A query using a right or left outer join. · A query using an inner join. · A query names that are appropriately descriptive for each query’s purpose. Your written report for this assignment should include · Detailed descriptions of each query and why or how it would be used. · The complete SQL statement for each query and a detailed breakdown of each part of the statement. The goal of this description is to convince me that you understand the SQL. · If the query includes JOINS a specification of what type and why that type was chosen. · What fields are displayed and what tables do they come from? · How is the resulting query sorted? · Description of the WHERE clause if present. The report is not an essay and should use point form and lists. The goal of this description is to convince me that you understand the SQL. The complexity of your queries will count in the marking. To complete this assignment, you will have to enter enough data in your database to make your queries work. Your Access database and Word file must be stored in a Zip file.