HOW TO: Make Backward-Compatible Changes to SQL SELECT Queries

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 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:

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 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:

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:

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":

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

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:

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:

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:

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:

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

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

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

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

This produces the following columns:

• FullName
• ShippingAddress

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:

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