This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(281.6, 12%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EFR%3C/text%3E%3C/svg%3E)
Hello,
I have the following information generated in a report (from a query). The query groups data by the Fiscal Year and identifies all costs for that year. FTE is a calculation (=Cost/43.26/2080). How do I calculate the average of the groups and the FTE? I do want the average of the Group Totals not the average value that creates the total.
Assuming that Cost is a column in the query's result table, to average the monthly costs should be simply a call of the AVG operator against the Cost column in an unbound text box in the report footer:
=AVG([Cost])
To average the FTE values call the AVG function against the original expression:
=AVG([Cost]/43.26/2080)
Thanks for the response...
Following is the query where "Valin recur" is the Cost value.
SELECT tblKOB1.[Cost Element], tblKOB1.[Cost element name], tblIW38.Location, tblKOB1.[Valin repcur], tblIW38.Description, tblIW38.Order, tblKOB1.[Fiscal Year], tblKOB1.[Posting Date]
FROM tblIW38 LEFT JOIN tblKOB1 ON tblIW38.Order = tblKOB1.Order
WHERE (((tblKOB1.[Cost Element])=[Please Enter Cost Element]));
When I run the report I receive the following results...
In an expression used as the ControlSource property of a control you cannot apply an aggregation operator to the result of another aggregation operation. You will need to call the SUM operator in a correlated subquery in the query's SELECT clause, and bind a text box to the computed Cost column:
SELECT tblKOB1.[Cost Element], tblKOB1.[Cost element name], tblIW38.Location,
tblKOB1.[Valin repcur], tblIW38.Description, tblIW38.Order, tblKOB1.[Fiscal Year],
tblKOB1.[Posting Date],
(SELECT SUM([Valin repcur])
FROM tblKOB1 AS tblKOB1_1
WHERE tblKOB1_1.[Fiscal Year] = tblKOB1.[Fiscal Year]) AS Cost
FROM tblIW38 INNER JOIN tblKOB1 ON tblIW38.Order = tblKOB1.Order
WHERE tblKOB1.[Cost Element]=[Please Enter Cost Element];
Note that I've used an INNER JOIN here. Restricting a query on a column on the right side of a LEFT OUTER JOIN, other than for IS NULL, in effect makes the join an INNER JOIN, so a LEFT JOIN is pointless.
Hello...Evidently I don't understand your suggestion. I created a new query using the SQL you provided.
SELECT tblKOB1.[Cost Element], tblKOB1.[Cost element name], tblIW38.Location,
tblKOB1.[Valin repcur], tblIW38.Description, tblIW38.Order, tblKOB1.[Fiscal Year],
tblKOB1.[Posting Date],
(SELECT SUM([Valin repcur])
FROM tblKOB1 AS tblKOB1_1
WHERE tblKOB1_1.[Fiscal Year] = tblKOB1.[Fiscal Year]) AS Cost
FROM tblIW38 INNER JOIN tblKOB1 ON tblIW38.Order = tblKOB1.Order
WHERE tblKOB1.[Cost Element]=[Please Enter Cost Element];
The values are incorrect and I am unsure of the issue. FY2021 should equal 303,885.67 while FY2022 should equal 218,940.51
Without access to your data I can't be sure, but I'm guessing you need to aggregate the multiple instances of the Valin repcur amounts returned by the join with tblIW38, in which case the latter needs to be included in the subquery:
SELECT tblKOB1.[Cost Element], tblKOB1.[Cost element name], tblIW38.Location,
tblKOB1.[Valin repcur], tblIW38.Description, tblIW38.Order, tblKOB1.[Fiscal Year],
tblKOB1.[Posting Date],
(SELECT SUM([Valin repcur])
FROM tblIW38 INNER JOIN tblKOB1AS tblKOB1_1 ON tblIW38.Order = tblKOB1_1.Order
WHERE tblKOB1_1.[Fiscal Year] = tblKOB1.[Fiscal Year]) AS Cost
FROM tblIW38 INNER JOIN tblKOB1 ON tblIW38.Order = tblKOB1.Order
WHERE tblKOB1.[Cost Element]=[Please Enter Cost Element];