Access becoming confused - column is both aliased to a pretty name and ORDER BY

[mdlueck]

Access 2007 is getting confused at what I am trying to ask it to do. This query is to go into a Combo Box to populate the control with the correct pick list choices.

Since it is a Combo Box, I end up aliasing the plain field name(s) with more meaningful names. I also need to include that name column in the ORDER BY clause to have the list sort alphabetically.

Pasting the query into an Access query object, when I switch to the Design View of the query, Access gets confused and doubles up the title column. Trying to execute it, I get a popup prompting to enter a value (presumably for the second of the duplicate column.)

Here is the SQL I would like Access to execute in the Combo Box control:

SELECT t.title AS [Tool Type:],t.id FROM tmptblqry_metoolingtooltype AS t WHERE t.active <> 0 ORDER BY t.sort,t.title;

So in Design View, the first column is where title gets renamed to "Tool Type:"

Then second to the last column of the column list is a second title which has the sort option set.

I try to plop the sort back on the first column, delete that extra column, and Access is still not happy.

If I can not get the query to run in an Access query window, I see why I would have problems in the Combo Box! And I have done far more wild queries in Combo Boxes, so no clue why this more simple query is putting up such a fight.

Suggestions please.

 

[vbaInet]

Try it on a new query.

 

[mdlueck]

Try it on a new query.

I copied the query out of the Combo Box control and pasted it into a new query window. (In order to work on it / find out why it was blowing up.)

I found a reference to a sharp spot in MS database products I think I bumped into:

"Why can't we have column alias in ORDER BY?"
http://sqlblog.com/blogs/tibor_kara...y-can-t-we-have-column-alias-in-order-by.aspx

Dropping the table alias as below executes correctly.

SELECT title AS [Tool Type:],id FROM tmptblqry_metoolingtooltype WHERE active <> 0 ORDER BY sort,title;

Note: Access still insists on doubling up the title column in the Query Design View. Since it executes, I am calling the "drop the table alias" work-around good enough, as apparently MS does not handle such correctly.

 

[vbaInet]

Your original SQL statement is a perfectly valid one.

The url doesn't disprove the way you've used it. It talkes about using field aliases in the ORDER BY clause. You qualified the original field name with the table alias and Access shouldn't complain.

 

[mdlueck]

LOL... Ja, this is a simple control, thus simple query... so no need to provide pretty aliases as the combo box control only displays one field!

Updated the query to be like others I have successfully placed into Combo Boxes:

SELECT t.title,t.id FROM tmptblqry_metoolingtooltype AS t WHERE t.active <> 0 ORDER BY t.sort,t.title;

fffeeewww!!! Always glad to have the Ahh haa moment come / for things to maintain consistency!

 

[mdlueck]

Your original SQL statement is a perfectly valid one.

The url doesn't disprove the way you've used it. It talkes about using field aliases in the ORDER BY clause. You qualified the original field name with the table alias and Access shouldn't complain.

Specifically what jumped out at me on that page was:

The problem was that a table name was also in the ORDER BY clause. And this is when it becomes weird. The problem isn't the column alias, it is the table alias.

So, I dropped the table alias and then it worked.

I knew I had gotten away with table aliases in other Combo Box controls, and that is when I discovered the inconsistency... I had copied from a more complex query where I needed to provide several columns with pretty aliases.

 

[vbaInet]

I still find it strange that your original query is blowing up.

 

[mdlueck]

original query is blowing up.

It actually does not blow up with an error, it simply returns nothing. Equally useless to raising an error message, though.

 

[vbaInet]

Oh alright. What happens when you remove the WHERE part?

You've probably moved on already so you don't have to reply.