Expression is not part of aggregate function?

[mailman26]

I am trying to run a query based on two other queries i have created to calculate tolat yield for product. Here is the SQL for the query:

SELECT qry_search_premiumtot.Trailer_ID, qry_search_premiumtot.Employee_ID, qry_search_premiumtot.Production_Date, qry_search_premiumtot.Stage_ID, [qry_search_premiumtot]!Sum/[qry_search_rm]!Sum AS Yield
FROM qry_search_premiumtot INNER JOIN qry_search_rm ON qry_search_premiumtot.Production_Date = qry_search_rm.Production_Date
GROUP BY qry_search_premiumtot.Trailer_ID, qry_search_premiumtot.Employee_ID, qry_search_premiumtot.Production_Date, qry_search_premiumtot.Stage_ID
HAVING (((qry_search_premiumtot.Stage_ID)=[Stage ID]));






The following two are the SQL for the querries I want to calculate:

SELECT tbl_Stageing.Trailer_ID, tbl_Stageing.Production_Date, tbl_Stageing.Employee_ID, First(tbl_Stageing.Stage_ID) AS FirstOfStage_ID, First(tbl_Stageing.Product_Category) AS FirstOfProduct_Category, tbl_Stageing.Product_Condition, Sum(tbl_Stageing.Product_Weight_lbs) AS [Sum]
FROM tbl_Stageing
WHERE (((tbl_Stageing.Product_Condition)="raw") AND ((tbl_Stageing.Product_Category)="material"))
GROUP BY tbl_Stageing.Trailer_ID, tbl_Stageing.Production_Date, tbl_Stageing.Employee_ID, tbl_Stageing.Product_Condition;


AND



SELECT tbl_Stageing.Trailer_ID, tbl_Stageing.Employee_ID, tbl_Stageing.Production_Date, tbl_Stageing.Stage_ID, Sum(tbl_Stageing.Product_Weight_lbs) AS [Sum]
FROM tbl_Stageing
WHERE (((tbl_Stageing.Product_Condition)="Premium") AND ((tbl_Stageing.Product_Category)<>"Material"))
GROUP BY tbl_Stageing.Trailer_ID, tbl_Stageing.Employee_ID, tbl_Stageing.Production_Date, tbl_Stageing.Stage_ID
HAVING (((tbl_Stageing.Stage_ID)=[Stage ID]));





In the first query i posted, [qry_search_premiumtot]!Sum/[qry_search_rm]!Sum AS Yield returns an error saying that this is not part of an aggregate function (When I change the "Total" row to "Expression") . How do i solve this problem?



Thanks!

Kumail

 

[PNGBill]

You should enclose your posted code in the Hash Key Symbol provided.

Your first two sql's work ??
They both result in the same name - Sum. I would imagine Sum is a reserved word and shouldn't be used.
alternative names could RawSum, PremiumSum.

Your last sql only appears to refer to Premium product, why use the Raw sql ?

 

[mailman26]

They both work. I changed the names, thanks.

I need the Raw sql to calculate the yield of Premium by dividing premium over raw in the expression.

 

[mailman26]

I think i almost figured out how to work around the aggregate function error.

SELECT DISTINCT [qry_search_premiumtot]![PremiumSum]/[qry_search_rm]![RawSum] AS Yield, qry_search_premiumtot.Trailer_ID, qry_search_premiumtot.Employee_ID, qry_search_premiumtot.Production_Date, qry_search_premiumtot.Stage_ID, qry_search_premiumtot.Product_Condition
FROM qry_search_rm, qry_search_premiumtot;


The only problem now is that the query returns '0' values for yield instead of an actual calculated value

 

[PNGBill]

I like to build up my querys/sqls from simple that works and then add more data.

If your first two sql's return the data you require, then I suggest your final sql just returns the two fields. Then add a calculated field to return the product of field1/field2.

When you get that working, add in the other fields and remove the first two fields if they are no longer req'd.

This way you identify what action caused everything to go