How do I create a column alias in Access?

[John W. Vinson]

It's considered polite to post a question in the big textbox, not just the
subject line (you're talking to human volunteers here, not to a search
engine).

That said: type

AliasName: Fieldname

in a vacant Field cell in the query grid, or in the SQL window use

SELECT fieldname AS Aliasname FROM...

 

[Laura]

That's what I did in the sql and still didn't get the aliases printed as
column headings in the result pane - is there something in Access that needs
to be turned on?

SELECT t2.business_Rule_Short_Desc as dsc

Business Rule Short Description:

In the example above I wanted "dsc" to be the column heading

 

[John W. Vinson]

That's what I did in the sql and still didn't get the aliases printed as
column headings in the result pane - is there something in Access that needs
to be turned on?

SELECT t2.business_Rule_Short_Desc as dsc

What is it you're printing? The query datasheet? If so, don't; datasheets
aren't really designed for printing or editing except very much as a
quick-and-diirty. Consider creating a report based on the query instead; it
will use the aliased fieldname.

If you have a need to print the query (or the table, for that matter) you can
set the Caption property of the field in query or table design view.

 

[John Spencer]

And the reason you are not getting the alias name in the datasheet view
is that Access is being helpful and using the caption property of the
field as the column name in datasheet view.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dale Fye]

Laura,

Where is your data coming from? Are the tables Access tables or are they
linked from some other data source?

I've had this problem with tables linked from SharePoint ever since
switching to Access 2007. Have not been able to get an acceptable answer
from anyone on the Access development team.

Generally, the way I handle this is to rename the labels that are associated
with the fields in the forms and reports. I do this anyway, so it is not
really a problem. Where I occassionally run into this problem is when I want
to refer to a field as an element of a recordset and try to refer to the
alias rather than the field name.

 

[David W. Fenton]

in the SQL window use

SELECT fieldname AS Aliasname FROM...

And, of course, as with other SQL dialects, the "AS" is optional,
though I certainly always include it, and if you save your SQL in
the Access query designer, it will put it in for you.

 

[Laura]

Okay - thanks! Now how do I transform a query into a Report?

 

[John Spencer]

David,
(Just to nitpick)

As far as I know, you must use AS when you alias a field name (at least
in Access 2003 and earlier - I've not tested this in 2007).

You are correct that the AS is optional if you alias a table name in a
FROM clause.

If I am wrong can you give me an example? I love to discover new things.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Okay - thanks! Now how do I transform a query into a Report?

You don't.

A query is one kind of object; a report is a very different kind of object!

Instead you create a Report and use the Query as its recordsource (instead of
using a Table as its recordsource). The Report has a "Recordsource" property;
set it to the name of the query.

 

[David W. Fenton]

As far as I know, you must use AS when you alias a field name (at
least in Access 2003 and earlier - I've not tested this in 2007).

You are correct that the AS is optional if you alias a table name
in a FROM clause.

If I am wrong can you give me an example? I love to discover new
things.

No, I was wrong. I should test things before I post them.

I don’t write my SQL by hand, so never ever leave it out. I should
have realized that meant I shouldn’t be speaking with such feigned
authority.

 

[John Spencer]

No problem. I double-checked before I posted by attempting to do it. In
other words, I was able to acknowledge that I had something to learn.

Of course (big grin) in this case what I learned was that my memory is
still working correctly.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Of course (big grin) in this case what I learned was that my memory is
still working correctly.

Marine boot camp drill sergeant:

"Everyone makes mistakes. Even I made a mistake once. I thought I was wrong
about something; it turned out I wasn't!"

<g, d & r>