Alias amnesia after subqueries in Access?

[Guest]

Hi, when I am running the following query, my order by statement is unable to
execute when I use aliases in my query, because it seems to forget aliases
AND full field specifiers after executing a subquery.

The following query is the only one I got working:

SELECT FUR.FUR_ID AS Id, FUR.FUR_OMSCHRIJVING AS Omschrijving,
FUR.FUR_BEDRAG + FUR.FUR_BEDRAG * BTW.BTW_PERCENTAGE AS Bedrag
FROM DTB_FACTUURUITREGELS FUR INNER JOIN STB_BTW BTW ON FUR.BTW_ID =
BTW.BTW_ID
WHERE FUR.FCU_ID = [ID] AND FUR.FUR_ID Not In (SELECT FUR.FUR_ID_CRED_OP
FROM DTB_FACTUURUITREGELS FUR INNER JOIN DTB_FACTUURUIT FCU ON FUR.FCU_ID =
FCU.FCU_ID
WHERE FCU.FST_ID = 5 AND FCU.FCU_ID_CRED_OP = [ID])
ORDER BY FUR_OMSCHRIJVING;

Replacing FUR_OMSCRIJVING in the ORDER BY clause with:
- Omschrijving
- FUR.FUR_OMSCRIJVING
- DTB_FACTUURUITREGELS.FUR_OMSCHRIJVING
all cause Access to prompt you for a parameter value for it.

Anyone familiar with this peculiar behavior and the exact cause of it?

I am using Access 2003.

Regards

 

[Marshall Barton]

Hi, when I am running the following query, my order by statement is unable to
execute when I use aliases in my query, because it seems to forget aliases
AND full field specifiers after executing a subquery.

The following query is the only one I got working:

SELECT FUR.FUR_ID AS Id, FUR.FUR_OMSCHRIJVING AS Omschrijving,
FUR.FUR_BEDRAG + FUR.FUR_BEDRAG * BTW.BTW_PERCENTAGE AS Bedrag
FROM DTB_FACTUURUITREGELS FUR INNER JOIN STB_BTW BTW ON FUR.BTW_ID =
BTW.BTW_ID
WHERE FUR.FCU_ID = [ID] AND FUR.FUR_ID Not In (SELECT FUR.FUR_ID_CRED_OP
FROM DTB_FACTUURUITREGELS FUR INNER JOIN DTB_FACTUURUIT FCU ON FUR.FCU_ID =
FCU.FCU_ID
WHERE FCU.FST_ID = 5 AND FCU.FCU_ID_CRED_OP = [ID])
ORDER BY FUR_OMSCHRIJVING;

Replacing FUR_OMSCRIJVING in the ORDER BY clause with:
- Omschrijving
- FUR.FUR_OMSCRIJVING
- DTB_FACTUURUITREGELS.FUR_OMSCHRIJVING
all cause Access to prompt you for a parameter value for it.

Anyone familiar with this peculiar behavior and the exact cause of it?

That's standard behavior. You need to use the original
field name (or expression) in ORDER BY and GROUP BY clauses.

 

[John Spencer]

Access does not allow you to use aliases in the Select clause of the main query
in the ORDER BY clause. You could work around this by saving the query and then
running another query based on the saved query. In that case, Access does
recognize the "new" column names.