Alias Property

Applies To

Action query, Crosstab query, Parameter query, Query field, Select query, SQL-Specific query.

Description

You can use the Alias property to specify a custom name for a source table or query when you use the same table or query more than once in the same query.

Note The Alias property applies only to tables or queries when they are used as the source of data in a query.

Setting

You can use a string expression to set the Alias property to any name not currently being used in the database. You set this property in query Design view by right-clicking the field list in the query design grid and clicking Properties.

You can set the Alias property in SQL view of the Query window by using an AS clause in the SQL statement.

Remarks

You must use an alias when creating a self-join in an SQL statement.

When you add a table or query to a query, Microsoft Access automatically sets the Alias property to the name of the table or query. If a second instance of the same table or query is added, Microsoft Access sets the Alias property to the object name plus an underscore and a 1 ("_1").

Creating an alias doesn't change the name of the table or query — it only specifies another name that can also be used to refer to the table or query. For example, in a query containing a self-join that shows employees and their managers, you would use two copies of the Employees table. Microsoft Access automatically calls the duplicate table Employees_1. To give this table a more meaningful name, you could set its Alias property to Managers.

Example

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;