Why does access create alias %$##@_Alias

[Raymond Martin]

I created an Access query, and when SQL reformatted it, it added a special
alias as shown below. I cannot seem to find any info on this behavior, and
although the query works fine, if I change the name of the alias it will
not work. What's happening?

SELECT product, (avg_unit_cost * (qty_on_hand - deliverable)) AS inv_value
FROM [SELECT [inventorymaster].[product], [avg_unit_cost], [qty_on_hand],
sum([qty_sold]-[total_qty_delivered]) AS deliverable
FROM inventorymaster LEFT JOIN invoice_line ON
[inventorymaster].[product]=[invoice_line].[product]
GROUP BY [inventorymaster].[product], [avg_unit_cost], [qty_on_hand]]. AS
[%$##@_Alias];

 

[Van T. Dinh]

Because you have a Sub-Query which is the SELECT statement
inside the first SELECT.

When you switch between the SQLView and GridView, Access
re-arranges the SQL String as it interprets. If you don't
want this to happen, work wxclusively with SQLView.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I created an Access query, and when SQL reformatted it, it added a special
alias as shown below. I cannot seem to find any info on this behavior, and
although the query works fine, if I change the name of the alias it will
not work. What's happening?

SELECT product, (avg_unit_cost * (qty_on_hand - deliverable)) AS inv_value
FROM [SELECT [inventorymaster].[product],

[avg_unit_cost], [qty_on_hand],

sum([qty_sold]-[total_qty_delivered]) AS deliverable
FROM inventorymaster LEFT JOIN invoice_line ON
[inventorymaster].[product]=[invoice_line].[product]
GROUP BY [inventorymaster].[product], [avg_unit_cost], [qty_on_hand]]. AS
[%$##@_Alias];



.

 

[Dirk Goldgar]

Sorry, but that does not work. Changing the alias to anything other
than the MS Access default (say to "t3" or "tab_3" ALWAYS results in
a "SYNTAX ERROR IN FROM CLAUSE" when trying to save the query (with
the cursor on the SUM function). However there is no error when the
alias is specifically named [%$##@_Alias]; All the SQL books indicate
that I can change this name - yet if I do, I get a syntax error.

So there must be something "special" about this name - but what?

the nested subquery returns a table which must have a name - any
name or alias.
If you do not like the Alias access provided for you, you can change
it, but you must provide an alias
When you create a query and run it, access adds the alias

HS

I created an Access query, and when SQL reformatted it, it added a
special alias as shown below. I cannot seem to find any info on
this behavior, and although the query works fine, if I change the
name of the alias it will not work. What's happening?

SELECT product, (avg_unit_cost * (qty_on_hand - deliverable)) AS
inv_value FROM [SELECT [inventorymaster].[product],
[avg_unit_cost], [qty_on_hand],
sum([qty_sold]-[total_qty_delivered]) AS deliverable
FROM inventorymaster LEFT JOIN invoice_line ON
[inventorymaster].[product]=[invoice_line].[product]
GROUP BY [inventorymaster].[product], [avg_unit_cost],
[qty_on_hand]]. AS [%$##@_Alias];

It's not the alias itself, I don't think. If I recall correctly from my
own experience, it's that the moment you touch the SQL statement
directly, it gets parsed again, and then the nested brackets confuse the
parser. I'll bet that if you removed all the brackets around table
names and field names, and left only the ones surrounding the subquery,
then you could change the alias to a name of your own choosing.

 

[Van T. Dinh]

My feeling is the square brackets plus the dot after the closing square
bracket around the SubQuery that creates the problem. I don't know why
Access uses the square brackets + dot rather which works when you leave them
alone but won't work when you try to modify the SQL. The normal syntax is
to use parentheses.

Try (untested):

SELECT product, (avg_unit_cost * (qty_on_hand - deliverable)) AS inv_value
FROM
( SELECT [inventorymaster].[product], [avg_unit_cost], [qty_on_hand],
sum([qty_sold]-[total_qty_delivered]) AS deliverable
FROM inventorymaster LEFT JOIN invoice_line
ON [inventorymaster].[product]=[invoice_line].[product]
GROUP BY [inventorymaster].[product], [avg_unit_cost], [qty_on_hand]
) AS t3

 

[Raymond Martin]

I think you may be right - I will try and re-post.

Thanks

Sorry, but that does not work. Changing the alias to anything other
than the MS Access default (say to "t3" or "tab_3" ALWAYS results in
a "SYNTAX ERROR IN FROM CLAUSE" when trying to save the query (with
the cursor on the SUM function). However there is no error when the
alias is specifically named [%$##@_Alias]; All the SQL books indicate
that I can change this name - yet if I do, I get a syntax error.

So there must be something "special" about this name - but what?

the nested subquery returns a table which must have a name - any
name or alias.
If you do not like the Alias access provided for you, you can change
it, but you must provide an alias
When you create a query and run it, access adds the alias

HS

I created an Access query, and when SQL reformatted it, it added a
special alias as shown below. I cannot seem to find any info on
this behavior, and although the query works fine, if I change the
name of the alias it will not work. What's happening?

SELECT product, (avg_unit_cost * (qty_on_hand - deliverable)) AS
inv_value FROM [SELECT [inventorymaster].[product],
[avg_unit_cost], [qty_on_hand],
sum([qty_sold]-[total_qty_delivered]) AS deliverable
FROM inventorymaster LEFT JOIN invoice_line ON
[inventorymaster].[product]=[invoice_line].[product]
GROUP BY [inventorymaster].[product], [avg_unit_cost],
[qty_on_hand]]. AS [%$##@_Alias];

It's not the alias itself, I don't think. If I recall correctly from my
own experience, it's that the moment you touch the SQL statement
directly, it gets parsed again, and then the nested brackets confuse the
parser. I'll bet that if you removed all the brackets around table
names and field names, and left only the ones surrounding the subquery,
then you could change the alias to a name of your own choosing.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Raymond Martin]

This works! - you are right it was the parser getting confused with the
brackets

Thanks to all who helped

My feeling is the square brackets plus the dot after the closing square
bracket around the SubQuery that creates the problem. I don't know why
Access uses the square brackets + dot rather which works when you leave them
alone but won't work when you try to modify the SQL. The normal syntax is
to use parentheses.

Try (untested):

SELECT product, (avg_unit_cost * (qty_on_hand - deliverable)) AS inv_value
FROM
( SELECT [inventorymaster].[product], [avg_unit_cost], [qty_on_hand],
sum([qty_sold]-[total_qty_delivered]) AS deliverable
FROM inventorymaster LEFT JOIN invoice_line
ON [inventorymaster].[product]=[invoice_line].[product]
GROUP BY [inventorymaster].[product], [avg_unit_cost], [qty_on_hand]
) AS t3

--
HTH
Van T. Dinh
MVP (Access)

Sorry, but that does not work. Changing the alias to anything other than the
MS Access default (say to "t3" or "tab_3" ALWAYS results in a "SYNTAX ERROR
IN FROM CLAUSE" when trying to save the query (with the cursor on the SUM
function). However there is no error when the alias is specifically named
[%$##@_Alias]; All the SQL books indicate that I can change this name - yet
if I do, I get a syntax error.

So there must be something "special" about this name - but what?