The RIGHT JOIN relates the Employees and Orders2 tables ON EmployeeID, while the INNER JOIN relates these joined tables with the Order_Details2 table based ON OrderID. The query returns the Total Sales by employee, sorted on Total Sales from highest to lowest.
|LastName||FirstName||Title||Total Units||Total Sales|
|Buchanan||Steven||Sales Manager||17||$ 3002|
|Callahan||Laura||Inside Sales Coordinator||5||$ 793|
|King||Robert||Sales Representative||1||$ 310|
|Leverling||Janet||Sales Representative||1||$ 98|
The SQL Source of the Recordset is as follows:
SELECT DISTINCT LastName, FirstName, Title, Sum(Quantity) AS [Total Units], Sum(UnitPrice*Quantity) AS [Total Sales] FROM (Employees RIGHT JOIN Orders2 ON Employees.EmployeeID = Orders2.EmployeeID) INNER JOIN Order_Details2 ON Orders2.OrderID = Order_Details2.OrderID GROUP BY LastName, FirstName, Title ORDER BY Sum(UnitPrice*Quantity) DESC
This Recordset includes fields from three tables: Employees, Orders2, and Order_Details2. When selecting fields from multiple tables, the fields must be referenced as tableName.fieldName if the same field appears in more than one table.
Example: Order_Details2.ProductID, Products.ProductID
This is required to explicitly specify from which table the values of conflicting fields are retrieved, and returns an error if not defined.