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 in 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.

 

[The_Doc_Man]

@dalski, The original question was "Why do some Access calculated fields not work in a query?"

I'll give a technical answer that might help you understand things a little better. When you are using Access you have not one but two active environments. The graphic user interface (GUI), either through forms or perhaps through the navigation panel and ribbons, controls all of the resources needed to show what data objects and what data content you have. However, the graphic aspect of Access doesn't do SQL. That is done by the database engine. These days, that is called ACE (Access Connectivity Engine). When you are going to run something through SQL, the query has to be sent to ACE for SQL processing. ACE returns the SQL results back to the GUI for display or printing. The problem is that ACE is running in a different Windows task and has a different set of memory than the GUI.

The problem you observed was that you had a reference to a form's control's property, referenced from an SQL statement. The form's control (and its properties) exist in the GUI's memory but you sent the SQL statement to ACE's memory - which cannot see the properties in that way. The GUI has the ability to translate property references but ACE does not. (The reference libraries are part of the GUI, not of ACE.) The suggestion was to evaluate the reference into a string so you could concatenate it into a literal constant in the SQL statement.

 

[dalski]

Thanks @The_Doc_Man.

@Pat Hartman, not trying to be argumentative here at all, for my clarification:

Not in my Access queries. You need to repeat the expression.

How am I misinterpreting you here - so in my mind the below contradicts this: I have defined an alias AREA, used a reference to that alias in the same query in VOLUME; without repeating the expression .

 

[Minty]

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

@Pat Hartman - I think you have this the wrong way around.
SQL Server doesn't allow it, which is why I don't bother to do it in Access, which does if you write it in the correct order.