Compare these results with those shown for SQL-Query-02-H above, and you will see the difference. For reference, here is the SQL code that Microsoft Access created from the QBE query: SELECT PROJECT.ProjectID, PROJECT.Name AS [Project Name], PROJECT.Department AS ProjectDepartment, DEPARTMENT.Phone AS DepartmentPhone, EMPLOYEE.EmployeeNumber, EMPLOYEE.LastName, EMPLOYEE.FirstName, EMPLOYEE.Phone AS EmployeePhone FROM ((DEPARTMENT INNER JOIN PROJECT ON DEPARTMENT.DepartmentName = PROJECT.Department) INNER JOIN EMPLOYEE ON DEPARTMENT.DepartmentName = EMPLOYEE.Department) JOIN ASSIGNMENT ON (PROJECT.ProjectID = ASSIGNMENT.ProjectID) AND (EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber) WHERE (((PROJECT.Department)="Marketing")) ORDER BY PROJECT.ProjectID; The problem we are encountering here is the same as described in 2.64 G. Again, there are two work arounds. First, create the query Department Phone. This is the only without 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: 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:
Compare these results with those shown for SQL-Query-02-H above, and you will see the difference. For reference, here is the SQL code that Microsoft Access created from the QBE query: SELECT PROJECT.ProjectID, PROJECT.Name AS [Project Name], PROJECT.Department AS ProjectDepartment, DEPARTMENT.Phone AS DepartmentPhone, EMPLOYEE.EmployeeNumber, EMPLOYEE.LastName, EMPLOYEE.FirstName, EMPLOYEE.Phone AS EmployeePhone FROM ((DEPARTMENT INNER JOIN PROJECT ON DEPARTMENT.DepartmentName = PROJECT.Department) INNER JOIN EMPLOYEE ON DEPARTMENT.DepartmentName = EMPLOYEE.Department) JOIN ASSIGNMENT ON (PROJECT.ProjectID = ASSIGNMENT.ProjectID) AND (EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber) WHERE (((PROJECT.Department)="Marketing")) ORDER BY PROJECT.ProjectID; The problem we are encountering here is the same as described in 2.64 G. Again, 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: 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:
Solution-Manual-for-Database-Processing-Fundamentals-Design-and-Implementation-13E-13th-Edition.docx