Solved Why Some Calculated Fields Will Not Work In A Query? (1 Viewer)

[Minty]

A plus one for @Mike Krailo option.
Personally, I wouldn't get into the habit of trying to reference calculated fields in the query by their alias in the query they are created in.
Other RDBMS don't let you do it, so if you ever try and use the same query structure it will fail.

I've had to convert a few of these abominations into T-SQL (SQL Server) and it's really painful.

 

[cheekybuddha]

This is your only option to get the effect you were going for. Get used to it.

Are you sure?

In Access I think this works too:

SELECT
  Length,
  Width,
  Depth,
  Length * Width AS AREA,
  AREA * Depth AS VOLUME
FROM Table1;

 

[MajP]

This is your only option to get the effect you were going for.

That is not true. As long as the OP drops query1 from the sql it works.
In this case there is no reason to duplicate the alias field.

SELECT length,
       width,
       depth,
       length * width AS AREA,
       area * depth   AS VOLUME
FROM   table1;

Now if you want to sort or filter on area or volume you will have to duplicate the calculations for the area. This is because of query order of operations. The Select is applied after the Oder By.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE/ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Solved - Why adding criteria to a column makes a query asking for parameters?

I have this query which shows the correct results SELECT d.PartID, Sum(d.Quan_In) AS InSum, Sum(d.Quan_Out) AS OutSum, [InSum]-Nz([OutSum]) AS Stock, [TraceCode] & "" AS TraceC FROM tblOldData AS d GROUP BY d.PartID, [TraceCode] & "" ORDER BY d.PartID; If I...

www.access-programmers.co.uk

 

[Pat Hartman]

Am I wrong here (usually am) - It seems in a query a calculated field (residing in that query) will require a subquery to be used if you reference the query name in the formula:

if the referenced name only appears in ONE source (table or query), no qualification is required. If it appears in more than one source, then it must be qualified by a source. The NameAutoCorrect feature does NOT properly correct field names embedded in calculations so you will almost always run into errors if you change column/table/object names and are relying on NameAutoCorrect to fix up and propagate the changes.

 

[Mike Krailo]

That is not true. As long as the OP drops query1 from the sql it works.
In this case there is no reason to duplicate the alias field.

SELECT length,
       width,
       depth,
       length * width AS AREA,
       area * depth   AS VOLUME
FROM   table1;

Now if you want to filter on the Area or Volume field you will have to duplicate the calculations. This is because of query order of operations. The alias is applied after the filter.

Solved - Why adding criteria to a column makes a query asking for parameters?

I have this query which shows the correct results SELECT d.PartID, Sum(d.Quan_In) AS InSum, Sum(d.Quan_Out) AS OutSum, [InSum]-Nz([OutSum]) AS Stock, [TraceCode] & "" AS TraceC FROM tblOldData AS d GROUP BY d.PartID, [TraceCode] & "" ORDER BY d.PartID; If I...

www.access-programmers.co.uk

I didn't word that correctly, was focused on the use of Quer1 in the Quer1 itself which is not possible. Referencing aliases in the same query is possible. My mistake.

 

[cheekybuddha]

Referencing aliases in the same query is possible.

This is also confusingly worded.

Query1 is the name of the query itself, and yes you are right that you can't reference the QueryDef's own name within the query itself.

But it is not an alias.

 

[Mike Krailo]

This is also confusingly worded.

Query1 is the name of the query itself, and yes you are right that you can't reference the QueryDef's own name within the query itself.

But it is not an alias.

What was confusing about it. You CAN reference the value of an alias in a query as was just demonstrated in the last few posts.

Wait, I may have updated my post after you read the initial one. Maybe that's where the confusion came in.

 

[cheekybuddha]

Wait, I may have updated my post after you read the initial one. Maybe that's where the confusion came in.

I think so

But it looks like we're trying to say the same thing, so no harm, no foul

 

[June7]

As already stated, can reference alias (or constructed) field name in another calc but don't include the table or query name. So an addition to the SQL Mike shows:

SELECT Table1.*, Length*Width AS AREA, AREA*Depth AS VOLUME FROM Table1;

 

[cheekybuddha]

The Select is applied after the Oder By.

In the list of operations you post immediately after this SELECT (operation 8) comes before ORDER BY (operation 10)

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE/ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

 

[MajP]

In the list of operations you post immediately after this SELECT (operation 8) comes before ORDER BY (operation 10)

I should have said that the Where comes before the select, which should explain the Where. Not sure the reason for the sort, but it is the same and you cannot use the alias in the sort. In either case you get a Parameter Prompt because it does not know about the alias when that occurs

So you have to duplicate the formula in the Order By (or the where). So you can do this

SELECT table1.length,
       table1.width,
       table1.depth,
       [table1].[length] * [table1].[width] AS AREA
FROM   table1
ORDER  BY [table1].[length] * [table1].[width] DESC;

Cannot do this

SELECT table1.length,
       table1.width,
       table1.depth,
       [table1].[length] * [table1].[width] AS AREA
FROM   table1
ORDER  BY [AREA] DESC;

Even though you can use AREA in other columns.

 

[Pat Hartman]

Referencing aliases in the same query is possible.

Not in my Access queries. You need to repeat the expression. SQL Server allows it.

 

[Mike Krailo]

Not in my Access queries. You need to repeat the expression. SQL Server allows it.

Please see june's post #29 for example of what i meant. Im not saying references outside the query.