SELECT PROJECT.ProjectID, PROJECT.Name AS [Project Name], PROJECT.Department, DEPARTMENT.Phone AS DepartmentPhone, EMPLOYEE.EmployeeNumber, EMPLOYEE.LastName, EMPLOYEE.FirstName, EMPLOYEE.Phone AS EmployeePhone FROM ((DEPARTMENT JOIN PROJECT ON DEPARTMENT.DepartmentName = PROJECT.Department) INNER JOIN EMPLOYEE ON DEPARTMENT.DepartmentName = EMPLOYEE.Department) INNER JOIN ASSIGNMENT ON (PROJECT.ProjectID = ASSIGNMENT.ProjectID) AND (EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber) ORDER BY PROJECT.ProjectID; What can we do? There are two work arounds. First, create the query Department Phone. This is the only column needed from the without DEPARTMENT table, which can thus be eliminated from the query. The QBE query is (note the use of the aliases ProjectName, ProjectDepartment and EmployeePhone): The results will be correct, but without the DepartmentPhone column. The results are: Alternatively, as devised by Professor John Schauf of Edgewood College, Madison, WI, you can illustrate building a set of queries, where each one uses the previous query and adds one additional table. This is possible because Microsoft Access allows saved queries to be used as the equivalent of a table in a query. By adding in one table at a time, you can control the JOIN...ON statement sequence, and obtain the correct answer. This is a much better solution, because the end result is exactly what we want, rather than a truncated version of it. You should use this solution in class to illustrate how to use Microsoft Access query objects as pseudo tables in queries, and point out that they can also be used in forms and reports. The steps below show how to create the needed sequence of QBE queries: (1) Create a query that joins PROJECT and ASSIGNMENT, and name it Note that you must include ASSIGNMENT.EmployeeNumber in this query. Also note the use of the two aliases ProjectName and ProjectDepartment: (2) Create a query that joins QBE-Query-02-G-PA and DEPARTMENT, and name it Note that you will have to manually link the DEPARTMENT primary key to the foreign key in QBE-Query-02-G-PA. Also note the use of the alias DepartmentPhone:
SELECT PROJECT.ProjectID, PROJECT.Name AS [Project Name], PROJECT.Department, DEPARTMENT.Phone AS DepartmentPhone, EMPLOYEE.EmployeeNumber, EMPLOYEE.LastName, EMPLOYEE.FirstName, EMPLOYEE.Phone AS EmployeePhone FROM ((DEPARTMENT JOIN PROJECT ON DEPARTMENT.DepartmentName = PROJECT.Department) INNER JOIN EMPLOYEE ON DEPARTMENT.DepartmentName = EMPLOYEE.Department) INNER JOIN ASSIGNMENT ON (PROJECT.ProjectID = ASSIGNMENT.ProjectID) AND (EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber) ORDER BY PROJECT.ProjectID; What can we do? There are two work arounds. First, create the query without Department Phone. This is the only column needed from the DEPARTMENT table, which can thus be eliminated from the query. The QBE query is (note the use of the aliases ProjectName, ProjectDepartment and EmployeePhone): The results will be correct, but without the DepartmentPhone column. The results are: Alternatively, as devised by Professor John Schauf of Edgewood College, Madison, WI, you can illustrate building a set of queries, where each one uses the previous query and adds one additional table. This is possible because Microsoft Access allows saved queries to be used as the equivalent of a table in a query. By adding in one table at a time, you can control the JOIN...ON statement sequence, and obtain the correct answer. This is a much better solution, because the end result is exactly what we want, rather than a truncated version of it. You should use this solution in class to illustrate how to use Microsoft Access query objects as pseudo tables in queries, and point out that they can also be used in forms and reports. The steps below show how to create the needed sequence of QBE queries: (1) Create a query that joins PROJECT and ASSIGNMENT, and name it Note that you must include ASSIGNMENT.EmployeeNumber in this query. Also note the use of the two aliases ProjectName and ProjectDepartment: (2) Create a query that joins QBE-Query-02-G-PA and DEPARTMENT, and name it Note that you will have to manually link the DEPARTMENT primary key to the foreign key in QBE-Query-02-G-PA. Also note the use of the alias DepartmentPhone:
Solution-Manual-for-Database-Processing-Fundamentals-Design-and-Implementation-13E-13th-Edition.docx