> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# HOW TO: Make Backward-Compatible Changes to SQL SELECT Queries
- URL: https://nolongerset.com/aliasing-in-queries/
- Published: 2024-01-03T04:59:00.000Z
- Updated: 2026-05-08T12:37:51.000Z
- Description: The `AS` keyword in SQL allows you to replace table fields with calculated columns while retaining the original name.
- Author: Mike Wolfe
- Tags: SQL, #Import 2026-05-20 02:59

In a complex application, it's not uncommon for a SELECT query to become a dependency for multiple objects:

- A form's RecordSource
- A report's RecordSource
- A combo box's RowSource
- A list box's RowSource
- Subquery to another query
- Subordinate component of a UNION query
- Etc.

To make matters worse, it's not easy to identify all the possible dependencies.

Given this reality, it's good to get in the habit of maintaining the backward compatibility of a query as you make changes to it. In this article, we will look at **how to change the definition of a column in your SELECT query without changing the column's name**.

#### *A Brief Note About Normalization*

*The example table I will be using in this article violates [third normal form](https://en.wikipedia.org/wiki/Third%5Fnormal%5Fform) because the FullName field depends on the values of the FirstName and LastName fields. A normalized design would omit the FullName field and create it only as needed. However, this denormalized design makes the examples in this article easier to follow. Generally speaking, though, you should never have fields in your tables that depend on the values of other fields in the same table.*

## Sample Situation

Consider a sample table named **Customer** with the following fields:

- CustomerID
- FirstName
- LastName
- FullName
- ShippingAddress
- InactivatedAt

### Original Query

Consider the following query:

```sql
SELECT FullName, ShippingAddress
FROM Customer
WHERE InactivatedAt Is Null
```

This query will generate the following named columns:

- FullName
- ShippingAddress

Now, let's say one day you realize that–despite your best efforts–there are records where the value of the customer's **FullName** column does not match the customer's **FirstName** and **LastName** fields. In other words, the data is out of sync. 

*Incidentally, this is why [database normalization](https://nolongerset.com/under-100-normalization/) is important in the first place.*

### Switching to a Calculated Field

You decide to change the query so that the customer's full name is calculated at runtime. Here's your first attempt:

```sql
SELECT FirstName + ' ' & LastName, ShippingAddress
FROM Customer
WHERE InactivatedAt Is Null
```

When you run this query, though, it returns the following fields:

- Expr1000
- ShippingAddress

What the heck is "Expr1000"? That's the autogenerated name that Access assigns to an unnamed calculated column in the first position of a SELECT query. Unnamed columns in positions 2, 3, etc. would be named "Expr1001", "Expr1002", etc. 

I'm not sure, but I believe that older versions of Access used a different naming convention–"Expr1", "Expr2", etc. Unfortunately, I don't have an older version of Access handy to verify. In any case, the one thing you *don't* want to do is rely on this auto-generated name in your forms, reports, VBA code, or other queries. 

Instead, you want to explicitly give the column a meaningful name.

### Using `AS` to Provide a Column Alias

In SQL, we can give columns meaningful names by providing a **column alias** via the `AS` keyword.

For example:

```sql
SELECT FirstName + ' ' & LastName AS CustomerName, ShippingAddress
FROM Customer
WHERE InactivatedAt Is Null
```

This updated SQL will produce the following columns:

- CustomerName
- ShippingAddress

Now, "CustomerName" is definitely an improvement over "Expr1000", but it is a change from our original query, which produced the columns "FullName" and "ShippingAddress."

### Aliasing a Calculated Column with the Name of a Different Column

If we want to match the original column names, we can do that by simply using the alias "FullName":

```sql
SELECT FirstName + ' ' & LastName AS FullName, ShippingAddress
FROM Customer
WHERE InactivatedAt Is Null
```

This produces a query with the columns:

- FullName
- ShippingAddress

Note that the Customer table already contains a column named "FullName." That's no problem here because 1) we're not returning the FullName column and 2) we're not using the FullName column as part of the calculation for our aliased "FullName" field.

But what if we wanted to?

### Returning an Aliased Column and an Existing Column with the Same Name

```sql
SELECT FirstName + ' ' & LastName AS FullName, ShippingAddress, FullName
FROM Customer
WHERE InactivatedAt Is Null
```

This produces a query with the columns:

- FullName
- ShippingAddress
- Expr1002

Note that if you were to add the FullName field to this query via the Query By Example (QBE) window, Access would automatically *fully qualify* the column name by prepending the table name to the field name:

```sql
SELECT FirstName+' ' & LastName AS FullName, Customer.ShippingAddress, Customer.FullName
FROM Customer
WHERE Customer.InactivatedAt Is Null;
```

Further note that Access qualified *all* of the non-calculated column names with the name of the table. The query above produces the following columns:

- FullName
- ShippingAddress
- Customer.FullName

Even though "ShippingAddress" was fully qualified in the SQL statement, the associated query column name does not include the table name prefix. That only happens for the "FullName" column because it is the second instance of that column name in the same query.

### Including a Field in the Calculation for a Column of the Same Name

This concept is awkward to explain in writing, but a simple example should clarify things.

Let's go all the way back to our original query:

```sql
SELECT FullName, ShippingAddress
FROM Customer
WHERE InactivatedAt Is Null
```

Now, let's say that we want to return the FullName in all caps. We can accomplish that easily with the [**UCase()** function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ucase-function):

```sql
SELECT UCase(FullName), ShippingAddress
FROM Customer
WHERE InactivatedAt Is Null
```

The above query will run, but we get the following columns:

- Expr1000
- ShippingAddress

Obviously, if part of our goal is to maintain backwards compatibility, then we want to alias that first column so that it is named "FullName."

Unfortunately, this does not work:

```vba
SELECT UCase(FullName) AS FullName, ShippingAddress
FROM Customer
WHERE InactivatedAt Is Null
```

If you try to execute it you get the following error message:

![Microsoft Access error message that reads: "Circular reference caused by alias 'FullName' in query definition's SELECT list."](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-10.png)

The easiest way to get around this error is by using the table name as a prefix to fully qualify the `FullName` table field:

```sql
SELECT UCase(Customer.FullName) AS FullName, ShippingAddress
FROM Customer
WHERE InactivatedAt Is Null
```

This produces the following columns:

- FullName
- ShippingAddress

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-11.png)

### You Can Alias Table Names, Too

While the above SQL is nice, having to write out a long table name like "Customer" a bunch of times is no fun.

To do that, we use the same `AS` keyword as before, but apply it to the table(s) in the FROM clause of the query:

```sql
SELECT UCase(C.FullName) AS FullName, C.ShippingAddress
FROM Customer AS C
WHERE InactivatedAt Is Null
```

This returns the same columns as before...

- FullName
- ShippingAddress

...but now it's not such a chore to fully qualify the field names throughout the rest of the query.

*Cover image created with [Microsoft Designer](https://designer.microsoft.com/)*