Query : aggregate Function Error

[ck1999]

I have the following SQL:

SELECT A.FormulaName, [cost]+8.34*[specificgravity] AS QBCost, [qbcost]*2.5 AS Retail, D.PLCost, D.PLRetail, D.QuotedRetail, Sum(A.specificgravity*B.percentage*8.34*C.total_Cost) AS cost, E.Description

FROM (((tblproduct AS A LEFT JOIN tblprices AS D ON A.FormulaNumber = D.ProductNumber) LEFT JOIN tblpricelist AS E ON A.FormulaName = E.ProductName) INNER JOIN tblingredients AS B ON A.FormulaNumber = B.FormulaNumber) INNER JOIN tblcost AS C ON B.Ingredient = C.NAME

GROUP BY A.FormulaName, [qbcost]*2.5, D.PLCost, D.PLRetail,retail ;









I am getting this error:
You tried to execute a query that doesn't include the specified expression 'Description' as part of an aggregate function.

Chris

 

[lespaul]

every feild in the select that is not an aggregate must be included in the group by clause:

SELECT A.FormulaName, [cost]+8.34*[specificgravity] AS QBCost, [qbcost]*2.5 AS Retail, D.PLCost, D.PLRetail, D.QuotedRetail, Sum(A.specificgravity*B.percentage*8.34*C.total_Cost) AS cost, E.Description

FROM (((tblproduct AS A LEFT JOIN tblprices AS D ON A.FormulaNumber = D.ProductNumber) LEFT JOIN tblpricelist AS E ON A.FormulaName = E.ProductName) INNER JOIN tblingredients AS B ON A.FormulaNumber = B.FormulaNumber) INNER JOIN tblcost AS C ON B.Ingredient = C.NAME

GROUP BY A.FormulaName, [cost]+8.34*[specificgravity], [qbcost]*2.5 AS Retail, D.PLCost, D.PLRetail, D.QuotedRetail, E.Description


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[ck1999]

Desciption is a memo field and I get an error about cannot group a memo field.

Chris

 

[Golom]

Then you'll need to do it this way

SELECT A.FormulaName, 
       [cost]+8.34*[specificgravity] AS QBCost, 
       [qbcost]*2.5 AS Retail, 
       D.PLCost, 
       D.PLRetail, 
       D.QuotedRetail, 

( Select Sum(A1.specificgravity*B1.percentage*8.34*C1.total_Cost)
  FROM (((tblproduct AS A1  
LEFT JOIN tblprices AS D1       ON A1.FormulaNumber = D1.ProductNumber) 
LEFT JOIN tblpricelist AS E1    ON A1.FormulaName = E1.ProductName) 
INNER JOIN tblingredients AS B1 ON A1.FormulaNumber = B1.FormulaNumber) 
INNER JOIN tblcost AS C1        ON B1.Ingredient = C1.NAME
  WHERE A1.FormulaName = A.FormulaName ) AS cost, 

       E.Description

FROM (((tblproduct AS A 
LEFT JOIN tblprices AS D       ON A.FormulaNumber = D.ProductNumber) 
LEFT JOIN tblpricelist AS E    ON A.FormulaName = E.ProductName) 
INNER JOIN tblingredients AS B ON A.FormulaNumber = B.FormulaNumber) 
INNER JOIN tblcost AS C        ON B.Ingredient = C.NAME

I've assumed that FormulaName is a key field sufficient to identify the record in the sub-query.

You cannot JOIN or GROUP on Memo, Object or Hyperlink fields.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ck1999]

Golom,
That worked great. Now when I run the query it displays correctly. I cannot change and update the information. It says recordset is not updatable. How do i change the recordset to make them updateable


Chris

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Essentially you don't. The query isn't updatable because one of its fields (the SUM) is coming from a coordinated sub-query and updating a field would require that the query be re-run to recompute the SUM. SQL queries don't work like spreadsheets where fields are recomputed "on-the-fly".

About the only thing you might be able to do without resorting to code, is run a MAKE TABLE query to compute and save the SUMs and then join that to the main table. The SUM will not update when you change the values that it is based on however.

When I'm doing something like this I usually just use the displayed query to select records and then populate a series of text boxes via a different query with the values that you are allowed to change.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ck1999]

Golom,

I do not want the sums to be saved. They are for pricing and therefore are continuously updated based off of the current cost of materials. So I donot need to update the Sum field however the other table I have linked I will want to change fields on them. Would it be better or possible to use a subform that contains the sum amount. Also the above query is taking about 30 - 40 seconds to run.

Chris

 

[ck1999]

PHV,
Thank you for the link. I read through the page and from my understanding I have the database set correctly.
Tblproducts and tblprices have one-to-one relationship. However when establishing the relationship between tblproducts and tblpricelist the relationship says intermediate. Tblcost and tblingreident are only used in the b1 and c1 areas above and are not needed as b and c so I have removed them from this section. These tables ahve to set relationships. Please let me know if I have overlooked something.
Thank you
Chris