Solved Why Some Calculated Fields Will Not Work In A Query?

[dalski]

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:

AliasName: [QueryName]![CalculatedField1]*[QueryName]![CalculatedField2]

Whereas if you reference the field itself it will not:

AliasName: [CalculatedField1]*[CalculatedField2]

I'm confused - I thought if the query was relying on info from another query; calculated fields in particular; the value is not know at compilation time - so it cannot derive the value. Whereas if a table was referenced it knows the value at compilation time so the calculated field would work. BUT this latest hurdle has me stumped. Saying this it's pretty late.

 

[plog]

I'm not entirely certain your issue. So let me throw a few things at you:

1. You can't make a calculated field in a query and then use that same calculated field in the same query in another field:

SELECT A, B, (A + B) AS AB_SUM, (AB_SUM *2) AS DoubleAB_SUM, FROM YourTable

AB_SUM will return a value in the above, but not DoubleAB_SUM.

2. Any datasource you want to use in a query must be in the FROM or in a JOIN in that query:

SELECT SourceA.FieldX, SourceB.FieldY, [SourceC].FieldZ
FROM SourceA
INNER JOIN SourceB ON SourceA.ID = SourceB.ID

FieldX works because SourceA is in the FROM, FieldY works because SourceB is in the INNER JOIN, FieldZ fails because SourceC isn't in the FROM or the JOIN.

 

[cheekybuddha]

AliasName: [QueryName]![CalculatedField1]*[QueryName]![CalculatedField2]

Did you try dot instead of bang?

AliasName: [QueryName].[CalculatedField1] * [QueryName].[CalculatedField2]

(Also, QueryName is included in the FROM clause, isn't it?)

 

[dalski]

Thank you both for your input. Creating a simplified test this morning (attached); calc'd fields in CAPS with a simple e.g. AREA based on tbl values & VOLUME dependant on the query result:


@plog thanks for the detailed explanation. I must not be following you completely as if I'm interpreting this correctly this would mean the volume field would not produce a value. No doubt I'm reading it wrong.

1. You can't make a calculated field in a query and then use that same calculated field in the same query in another field:

Creating The Nomenclature references first seems to allow a calculated field based on a parent calculated field; created in the same query (VOLUME).

Test 2:
This is where it gets really interesting. Referencing the fields only both of them work, but the nomenclature reference now fails (which originally worked in the first stage).

Did you try dot instead of bang?

AliasName: [QueryName].[CalculatedField1] * [QueryName].[CalculatedField2]

(Also, QueryName is included in the FROM clause, isn't it?)

Thanks Dave, I'm not altering the SQL which Access generates & know little about SQL. I want full functionality of the Access Query Design atm. I wouldn't think I need to alter the generated SQL on such a simple task atm (I hope so at least). I'll have a look at this later in my real world problem. But was flabbergasted as to what the basic test yielded this morning.

SELECT Table1.Length, Table1.Width, Table1.Depth, [Table1]![Length]*[Table1]![Width] AS [AREA-NomenclatureRef], [AREA]*[Depth] AS [VOLUME-NomenclatureRef], [Length]*[Width] AS [AREA-FieldRefs], [AREA-FieldRefs]*[Depth] AS [VOLUME-FieldRefs]
FROM Table1;

 

[cheekybuddha]

Try:

SELECT
  Table1.Length,
  Table1.Width,
  Table1.Depth,
  Table1.Length * Table1.Width AS [AREA-NomenclatureRef],
  Table1.Length * Table1.Width * Table1.Depth AS [VOLUME-NomenclatureRef],
  Table1.Length * Table1.Width AS [AREA-FieldRefs],
  Table1.Length * Table1.Width * Table1.Depth AS [VOLUME-FieldRefs]
FROM Table1;

Since you are only selecting a single table it's more simply written:

SELECT
  Length,
  Width,
  Depth,
  Length * Width AS [AREA-NomenclatureRef],
  Length * Width * Depth AS [VOLUME-NomenclatureRef],
  Length * Width AS [AREA-FieldRefs],
  Length * Width * Depth AS [VOLUME-FieldRefs]
FROM Table1;

I'm not sure why you want [AREA-NomenclatureRef] and [AREA-FieldRefs] to use the same calculation; same applies to [VOLUME-NomenclatureRef] and[VOLUME-FieldRefs]

 

[cheekybuddha]

SELECT Table1.Length, Table1.Width, Table1.Depth, [Table1]![Length]*[Table1]![Width] AS [AREA-NomenclatureRef], [AREA]*[Depth] AS [VOLUME-NomenclatureRef], [Length]*[Width] AS [AREA-FieldRefs], [AREA-FieldRefs]*[Depth] AS [VOLUME-FieldRefs] FROM Table1;

The Query1 in your first db (calculatedFields.accdb) fails because you have just typed 'AREA' instead of 'AREA-NomenclatureRef' in the [VOLUME-NomenclatureRef] calculation.

You have no field defined as AREA, hence Access looks for it as a parameter

 

[dalski]

Thanks Dave, it was merely a test in trying to understand what's going on at a basic level. Surely on something so basic it is capable within the Access Query Designer itself & not necessary to alter the SQL. Grateful for the input & I can see what's happening with the SQL but I'm trying to see what the deeper issue is. E.g. got some great input the other day that ALIAS' are one of the last things to be assigned & can cause problems if the ALIAS is not used in the form on initial testing; the column ALIAS is then lost... something like that. Bit too much for my limited understanding atm. Need to grasp the basics. I'm more concerned with what I haven't grasped from Plog's explanation.

I'm getting prompts a lot to enter parameter data in my real problems & I do not know why so that is what I am trying to figure out. I do not think I did anything wrong in the query design itself (I should know better than to write this) so what in Access has caused this simple problem?

I'm not sure why you want [AREA-NomenclatureRef] and [AREA-FieldRefs] to use the same calculation; same applies to [VOLUME-NomenclatureRef] and[VOLUME-FieldRefs]

Creating a simplified test this morning (attached);

... flabbergasted as to what the basic test yielded this morning.

 

[June7]

Actually, can use a calculated field alias name in another calculated field. Except for extraneous comma before FROM (which I removed), this is perfectly legit:

SELECT A, B, (A + B) AS AB_SUM, (AB_SUM *2) AS DoubleAB_SUM FROM YourTable;

What cannot do is use that alias name in other clauses such as WHERE or GROUP BY.

Advise not to use spaces nor punctuation in naming convention.

 

[dalski]

The Query1 in your first db (calculatedFields.accdb) fails because you have just typed 'AREA'

Thanks Dave, how embarassing .

 

[dalski]

Actually, can use a calculated field alias name in another calculated field. Except for extraneous comma before FROM (which I removed), this is perfectly legit:

Thanks @June7, yes I thought so & thought it was erroneous. I hadn't messed around with the SQL at all so this was created by Access or more likely thanks to @cheekybuddha pointing out my bad reference. Ok makes a bit more sense. Do I need to look out for anything regarding unknown values not being known at compilation time? I think I've heard this in the past but still learning basics.

 

[June7]

Use dot instead of bang. Why did you have bang (!) instead of dot? Query builder will use dot with fields.

Plog's second point is valid. If a table is not pulled into query, can't directly reference any of its fields.

 

[dalski]

Thanks, I typed it in on the Query Builder thinking as i was referencing an object/ class (table/ query) in trying to avoid the annoying <<Expr>> labels which appear when transferring from 'Expression Categories' & loosing a lot of time navigating. So in using dot's instead of explanation points it fails. Probs me again with bad references.

 

[dalski]

Sorry, no need to reference the object itself in the same query. But I would've thought it would allow it though as I would've thought being as explicit as possible is a good thing usually.

 

[arnelgp]

tyry reading some MS Access books, it will help you a lot.

 

[dalski]

Any recommendations?

 

[arnelgp]

there are a lot if you google search it.
don't look for old version prior to A2007.