EMPLOYEES

CREATE VIEW [dbo].[vEmployees]

AS

SELECT Employees.*, Superior.LastName + ', ' + Superior.FirstName as [Manager]

FROM Employees

LEFT OUTER JOIN Employees Superior

ON Superior.EmployeeID = Employees.ReportsTo

GO

We use vEmployees in browse mode below to display the Manager name

We use the vEmployeeTerritors on the Detail Grid below for EmployeeTerrories->EmployeeID

CREATE VIEW [dbo].[vEmployeeTerritories]

AS

SELECT EmployeeTerritories.*, Employees.LastName + ', ' + Employees.FirstName as Employee,

Territories.TerritoryDescription AS Territory

FROM EmployeeTerritories

INNER JOIN Employees ON Employees.EmployeeID = EmployeeTerritories.EmployeeID

INNER JOIN Territories ON Territories.TerritoryID = EmployeeTerritories.TerritoryID

GO

We use the vOrders on the Detail Grid below for Orders->EmployeeID

CREATE VIEW [dbo].[vOrders]

AS

SELECT Orders.*, Customers.CompanyName as CustomerName, Employees.LastName + ', ' +

Employees.FirstName as Employee, Shippers.CompanyName as Shipping

FROM Orders

LEFT OUTER JOIN Customers ON Customers.CustomerID = Orders.CustomerID

LEFT OUTER JOIN Employees ON Employees.EmployeeID = Orders.EmployeeID

LEFT OUTER JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia

GO

We map the ReportsTo column to a concatenation of LastName + FirstName