asked on

Access "Aggregate Function Error"

when I try to run the query below, it generates an error saying that I tried to execute query that does not include the specified expression SumOfInspected as part of an aggregate function.

Could someone please explain what I am doing wrong here? I seem to chase my tail with this error.

SELECT GetWeekEndingDate([ProductionDate]) AS WeekEndingDate, SumOfInspected, SumOfGood, (SumOfGood/SumOfInspected)*100 AS CalculatedFPY
FROM EG_Wood_FPY_Trend_uqry
GROUP BY GetWeekEndingDate([ProductionDate])
ORDER BY GetWeekEndingDate([ProductionDate]);

Open in new window

Markus Fischer

You are writing a total query. In such a query, each field used in the SELECT clause must either:
* be enclosed in a domain aggregate function: sum(), min(), max(), avg(), etc.
* be repeated in the GROUP BY section

In other words, you could write:

SELECT
GetWeekEndingDate([ProductionDate]) AS WeekEndingDate,
SumOfInspected,
SumOfGood,
(SumOfGood/SumOfInspected)*100 AS CalculatedFPY
FROM EG_Wood_FPY_Trend_uqry
GROUP BY
GetWeekEndingDate([ProductionDate]),
SumOfInspected,
SumOfGood
ORDER BY GetWeekEndingDate([ProductionDate]);

OR

SELECT
GetWeekEndingDate([ProductionDate]) AS WeekEndingDate,
Sum(SumOfInspected) As SumOfSumOfInspected,
Sum(SumOfGood) As SumOfSumOfGood,
Avb((SumOfGood/SumOfInspected)*100) AS AvgOfCalculatedFPY
FROM EG_Wood_FPY_Trend_uqry
GROUP BY
GetWeekEndingDate([ProductionDate])
ORDER BY GetWeekEndingDate([ProductionDate]);

OR, better

Reopen your original total query, EG_Wood_FPY_Trend_uqry, which already performs the two sums, and adjust that one instead to group by "week ending date".

(°v°)

Rex

ASKER

Thank you for stating the requirements so clearly. However, I'm afraid I don't undertand how to aplly them.

EG_Wood_FPY_Trend_uqry is a UNION Query that pulls together several sources. (See below) I am already Grouping by the week ending date, but do not know how to handle the calculated field. that's why I was trying the two step query approach.

SELECT GetWeekEndingDate([ProductionDate]) AS WeekEndingDate,Sum(Inspected) AS SumofInspected, Sum(Good) AS SumofGood
FROM [Archive 15th St L1 FPY Weekly Trend]
GROUP BY GetWeekEndingDate([ProductionDate])
UNION ALL SELECT GetWeekEndingDate([ProductionDate]) AS WeekEndingDate,Sum(Inspected) AS SumofInspected, Sum(Good) AS SumofGood
FROM [Archive 15th St L2 FPY Weekly Trend]
GROUP BY GetWeekEndingDate([ProductionDate])
UNION ALL SELECT GetWeekEndingDate([ProductionDate]) AS WeekEndingDate, Sum(FifteenthStqryWoodFPY.Inspected) AS SumOfInspected, Sum(FifteenthStqryWoodFPY.Good) AS SumOfGood
FROM FifteenthStqryWoodFPY
GROUP BY GetWeekEndingDate([ProductionDate]);

ASKER CERTIFIED SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rex

ASKER

I used the second example with the subquery. You are also correct in my not having the ProductionDate field. I found that after I posted my question.

Thank you very much. You have been very helpful.

Markus Fischer

You are welcome. Thank you for the feedback, and success with your project!
(°v°)

Access &quot;Aggregate Function Error&quot;

Access "Aggregate Function Error"