Problem with Aliasing (AS)

[David Wetmore]

I have a link table where the field containing the parent key is named "PKEY".
In the parent table the key is named "KEY".
I wish to build a query to construct a recordset where the have the PKEY value
in a field named "KEY"

This statement works fine:
SELECT PKey FROM tblJoin WHERE (((tblJoin.CKey)=59))

This one does not:
SELECT PKey AS Key FROM tblJoin WHERE (((tblJoin.CKey)=59))

How can I get values from the join table PKEY field into a field named KEY?
I could go back and rename the join table field, but I would like an easier way, if possible.

Thanks, Dave

 

[Jerry Whittle]

Strange. It works for me in Access 2007.

KEY is a reserved word in SQL. That might be a problem. I thought that
putting either single or double quotes around "KEY" would help but the alias
then keeps the quotation marks.

http://support.microsoft.com/kb/286335/

What happens if you change the alias to something not a reserved word like:


SELECT PKey AS NotKey FROM tblJoin WHERE (((tblJoin.CKey)=59))

 

[Jeff Boyce]

David

Perhaps you are running into a conflict/confusion on the part of Access. I
don't recall exactly, but suspect that Access treats "Key" as a reserved
word ... so what it means and what it thinks you mean may not match what you
think you mean.

What happens if you change the alias to something else (i.e., not a reserved
word)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[David Wetmore]

Changing from "Key" did it.

Thanks