Reference One Calculated Query Field in the Expression for Another (1 Viewer)

[mamandeno]

I have a select query within which I want to add a calculated query (Expr1 below), that uses another calculated query (Kgm below) in it's expression. I have set the format of Kgm in the query to fixed and limited it to two decimal places.

View attachment 108444

The problem I'm having is that Access isn't recognising the reference to Kgm. I get the little form seeking a value for Kgm. I'm not sure what I'm doing wrong. I used the expression builder to build the expressions, so the references should be correct (one would hope). The other calculated fields shown above are working.

I can work around this problem by repeating the maths , but I'd like to understand what I'm doing wrong.

Mike

 

[GaP42]

Having problems seeing the attachment: OOPS! We ran into some problems: The requested page could not be found. Is it an image?

 

[The_Doc_Man]

Rather than linking to the information, you obviously have created a query. If you can get it into SQL view, you can copy/paste the SQL statement into a post, after which we might be able to offer guidance. Your attachment link didn't work for me, either.

 

[mamandeno]

Sorry, I'm having a little trouble with it. I'll post the expressions

Months_to_MinHarvestDate: DateDiff("m",[DateSeeded],[MinOfHarvestDate])

Kgm: [SumOfFactoryNetGreenWeightKgs]/[LengthSeeded]

Expr1: [Kgm]/[Months_to_MinHarvestDate]/12

The first two calculated fields are working. The third one isn't. When I run the query the "Enter Parameter Query" dialog box pops up, seeking a value for Kgm

 

[mamandeno]

SQL VIEW as requested

SELECT MusselCrops.CropNoKey, Sites.AreaCode, MusselCrops.SiteNo, Longlines.LongLineID, MusselCrops.DateSeeded, MusselCrops.TypeSeeded, MusselCrops.Con_SpatBatches, MusselCrops.RateSeeded, MusselCrops.SizeSeeded, MusselCrops.LengthSeeded, Qry_Mike_Harvests_Factory_Sum.MinOfHarvestDate, Qry_Mike_Harvests_Factory_Sum.MaxOfHarvestDate, Max(Assessments.Length) AS MaxOfLength, Qry_Mike_Harvests_Factory_Sum.SumOfReceivedWeightKgs, Qry_Mike_Harvests_Factory_Sum.SumOfTotalDeductionKgs, Qry_Mike_Harvests_Factory_Sum.SumOfFactoryNetGreenWeightKgs, MusselCrops.CropOwner1, MusselCrops.CropOwner2, DateDiff("m",[DateSeeded],[MinOfHarvestDate]) AS Months_to_MinHarvestDate, [SumOfFactoryNetGreenWeightKgs]/[LengthSeeded] AS Kgm, [Kgm]/[Months_to_MinHarvestDate]/12 AS Expr1
FROM (((MusselCrops INNER JOIN Qry_Mike_Harvests_Factory_Sum ON MusselCrops.CropNoKey = Qry_Mike_Harvests_Factory_Sum.CropNoKey) INNER JOIN Longlines ON MusselCrops.LongLineKey = Longlines.LongLineKey) INNER JOIN Assessments ON Longlines.LongLineKey = Assessments.LongLineKey) INNER JOIN Sites ON MusselCrops.SiteNo = Sites.SiteNo
GROUP BY MusselCrops.CropNoKey, Sites.AreaCode, MusselCrops.SiteNo, Longlines.LongLineID, MusselCrops.DateSeeded, MusselCrops.TypeSeeded, MusselCrops.Con_SpatBatches, MusselCrops.RateSeeded, MusselCrops.SizeSeeded, MusselCrops.LengthSeeded, Qry_Mike_Harvests_Factory_Sum.MinOfHarvestDate, Qry_Mike_Harvests_Factory_Sum.MaxOfHarvestDate, Qry_Mike_Harvests_Factory_Sum.SumOfReceivedWeightKgs, Qry_Mike_Harvests_Factory_Sum.SumOfTotalDeductionKgs, Qry_Mike_Harvests_Factory_Sum.SumOfFactoryNetGreenWeightKgs, MusselCrops.CropOwner1, MusselCrops.CropOwner2, MusselCrops.LongLineKey, DateDiff("m",[DateSeeded],[MinOfHarvestDate]), [SumOfFactoryNetGreenWeightKgs]/[LengthSeeded], [Kgm]/[Months_to_MinHarvestDate]/12
HAVING (((Qry_Mike_Harvests_Factory_Sum.MinOfHarvestDate)>=[Start Date] And (Qry_Mike_Harvests_Factory_Sum.MinOfHarvestDate)<=[End Date]));

 

[The_Doc_Man]

Kgm appears twice in your query. Once in the SELECT (As Expr1) and once in the GROUP BY (as the last group). It is hard to know from that error WHICH of the two references it doesn't like. Perhaps if you grouped by Expr1 (or give it a "real" name in the SELECT portion and grouped by that name) it might like you better.

I'm going to also make an observation (NOT a criticism) that unless you have literally thousands or even tens of thousands of records, that many groups in a GROUP BY might be rather impractical. By the 15th group things ought to be pretty well grouped out. But of course, you know your data better than we do.

 

[plog]

You didn't really do anything wrong--not syntatically or logically--you just can't accomplish what you are trying in Access. You can't immediately use a calculated field in the same query in which it is calculated. It needs to run the query to calculate the value, but it needs the calculated value to run the query but it needs to run the query to calculate the value but etc. etc.

So, either repeat the math or do the Months_to_MinHarvestDate & Kgm math in this query and then use it as the basis as another query in which you can then use those two fields to do further calculation.

2 very big notes:

1. Be careful about division. Can any of those denominators (LengthSeeded, Months_to_MinHarvestDate) ever be 0? If so that's going to be an error as well--you can't divide by 0.

2. This seems over engineered and under thought out.
2A--You're aggregating an aggregate query. By your names I can tell Qry_Mike_Harvests_Factory_Sum is an aggregate (it uses a GROUP BY, total functions like SUM / MIN / MAX). But then in this query you are aggregating as well.
2B--You're aggregating for just one field. In the query you've posted you only use an aggregate function (MAX) once.

You should build a subquery using Assessments and aggregate just the data you need there, then JOIN that query back here without using Assessments itself. That will spare you the need to aggregate this query and completed rid you of that GROUP BY.

 

[ebs17]

Regarding the actual error, I would deepen @plog's note: The column aliases Months_to_MinHarvestDate and Kgm are not (yet) known in the grouping because the grouping section is executed before the renaming (allocation of aliases in the SELECT section).

...
   DateDiff("m", [DateSeeded], [MinOfHarvestDate]) AS Months_to_MinHarvestDate,
   [SumOfFactoryNetGreenWeightKgs] / [LengthSeeded] AS Kgm,
   (SumOfFactoryNetGreenWeightKgs / LengthSeeded) / DateDiff("m", DateSeeded, MinOfHarvestDate) / 12 AS Expr1
FROM
...

I would also agree with the other critical comments.

 

[mamandeno]

Thanks for the replies. Lots of food for thought. I think I can simplify things somewhat.

I'm dealing with an old and creaky database that I did not design/build. I need to match multiple harvest records to one crop record to work out yield. Unfortunately, the required info lives in different tables. I can't change this, I have to work around it.

Something I don't understand is what is the alternative to "Group By". I build queries in the query builder (I can't write SQL code). The minute I add the totals row, all fields are set to Group By.

 

[mamandeno]

I'm going to also make an observation (NOT a criticism) that unless you have literally thousands or even tens of thousands of records, that many groups in a GROUP BY might be rather impractical. By the 15th group things ought to be pretty well grouped out. But of course, you know your data better than we do.

I have >100k records.

 

[The_Doc_Man]

OK, then I have another comment that is more about limitations. If you look in this article,

Access specifications - Microsoft Support

Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.

support.microsoft.com

You would find that the number of fields or expressions that you can sort or group on in a report is 10. You have considerably more than 10 groups in the query. You could export your query to Excel or just open the query in datasheet view, but if you try to make a formal Access report, your reports will blow up on the 11th grouping.

Something I don't understand is what is the alternative to "Group By". I build queries in the query builder (I can't write SQL code). The minute I add the totals row, all fields are set to Group By.

It is an unfortunate fact of life that if you are using an SQL Aggregate function (such as SUM or COUNT) that you must aggregate things, and the way to aggregate things is the GROUP BY clause. Think of it this way: To take a COUNT or a SUM of a group of things, you have to DEFINE the grouping that you want. There IS no alternative to "group by" when dealing with aggregation statistics. The rule that is tripping you up is that when an Aggregate function is in use, Access SQL (and many OTHER SQLs as well) require that a field either be the subject of an aggregate function, a WHERE clause element, or a GROUP element. This is because it is the nature of the aggregation to show ONE RECORD to represent however many records were members of the group. In order to do that, you CANNOT show individual records to see details (which may vary) because you are compressing the records statistically.

 

[mamandeno]

You didn't really do anything wrong--not syntatically or logically--you just can't accomplish what you are trying in Access. You can't immediately use a calculated field in the same query in which it is calculated. It needs to run the query to calculate the value, but it needs the calculated value to run the query but it needs to run the query to calculate the value but etc. etc.

So, either repeat the math or do the Months_to_MinHarvestDate & Kgm math in this query and then use it as the basis as another query in which you can then use those two fields to do further calculation.

2 very big notes:

1. Be careful about division. Can any of those denominators (LengthSeeded, Months_to_MinHarvestDate) ever be 0? If so that's going to be an error as well--you can't divide by 0.

2. This seems over engineered and under thought out.
2A--You're aggregating an aggregate query. By your names I can tell Qry_Mike_Harvests_Factory_Sum is an aggregate (it uses a GROUP BY, total functions like SUM / MIN / MAX). But then in this query you are aggregating as well.
2B--You're aggregating for just one field. In the query you've posted you only use an aggregate function (MAX) once.

You should build a subquery using Assessments and aggregate just the data you need there, then JOIN that query back here without using Assessments itself. That will spare you the need to aggregate this query and completed rid you of that GROUP BY.

1) Great call. There were a few null or blank values in some of the older data. Adding ">0" as a criteria to the field I was using in the calculations has everything working as it should. There shouldn't be because we have validation rules, but that's a separate issue, the query now works.

I still can't use one calculated field as in another, so I've simply repeated the maths and that works. I can see I have some data tidy up to do, but I now have a 20 year data set I can use for building my predictive model.

 

[mamandeno]

OK, then I have another comment that is more about limitations. If you look in this article,

Access specifications - Microsoft Support

Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.

support.microsoft.com

You would find that the number of fields or expressions that you can sort or group on in a report is 10. You have considerably more than 10 groups in the query. You could export your query to Excel or just open the query in datasheet view, but if you try to make a formal Access report, your reports will blow up on the 11th grouping.



It is an unfortunate fact of life that if you are using an SQL Aggregate function (such as SUM or COUNT) that you must aggregate things, and the way to aggregate things is the GROUP BY clause. Think of it this way: To take a COUNT or a SUM of a group of things, you have to DEFINE the grouping that you want. There IS no alternative to "group by" when dealing with aggregation statistics. The rule that is tripping you up is that when an Aggregate function is in use, Access SQL (and many OTHER SQLs as well) require that a field either be the subject of an aggregate function, a WHERE clause element, or a GROUP element. This is because it is the nature of the aggregation to show ONE RECORD to represent however many records were members of the group. In order to do that, you CANNOT show individual records to see details (which may vary) because you are compressing the records statistically.

Thanks. I have no need to run reports using this query in access. So, all good there.

Also thanks for that explanation.

 

[mamandeno]

You didn't really do anything wrong--not syntatically or logically--you just can't accomplish what you are trying in Access. You can't immediately use a calculated field in the same query in which it is calculated. It needs to run the query to calculate the value, but it needs the calculated value to run the query but it needs to run the query to calculate the value but etc. etc.

I missed this in my original read of your post, makes sense, thankyou.