The following example sets the RecordSource property for a form to an SQL statement that uses the AS clause to specify Managers as the alias for the Employees table.
Dim strGetSQL AS String
strGetSQL = "SELECT Employees.FirstName, Employees.LastName, " _
& "Employees.Title, Managers.FirstName, Managers.LastName FROM Employees " _
& "INNER JOIN Employees AS Managers " _
& "ON Employees.ReportsTo = Managers.EmployeeID; "
Forms!Form1.RecordSource = strGetSQL
The next example uses a self-join to show employees and their managers. It uses the AS clause to set the Alias property of the duplicate Employees table to Managers. You enter the following SQL statement in SQL view of the Query window.
SELECT Employees.FirstName, Employees.LastName, Employees.Title, Managers.FirstName, Managers.LastName
FROM Employees INNER JOIN Employees AS Managers
ON Employees.ReportsTo = Managers.EmployeeID;