Alias Property

Applies To

Query.

Description

You can use the Alias property to specify a custom name for an input 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 used as the source of data in a query.

Setting

You can set the Alias property using a string expression to any name not currently being used in the database. You set this property in query design view by right clicking on the field list in the query design grid and choosing 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 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 as the object name plus an underscore and a one (“_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= "SELECT DISTINCTROW Employees.[FirstName]," _
    & "Employees.[LastName], Employees.Title," _
    & "Managers.[FirstName], Managers.[LastName] FROM Employees" _
    & "INNER JOIN Employees AS Managers ON Employees.[ReportsTo] = " _
    & "Managers.[EmployeeID];"!Form1.RecordSource = strGetSQL

This 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 can enter this SQL statement in SQL view of the Query window.

SELECT DISTINCTROW Employees.[FirstName], Employees.[LastName], .Title, Managers.[FirstName], Managers.[LastName] Employees INNER JOIN Employees AS Managers ON Employees.[ReportsTo] = Managers.[EmployeeID];