same error is 2 queries

[rainbows]

hi

for some reason these errors have appeared on 2 of my queries and i have no idea why the first screen does use the second screen . but this is where i am testing different ways of doing things , the strange thing is they did work so i must have now done something wrong

thanks for your help

steve

I also get the error on this one

 

[Grumm]

Long time I used Access, but since you are doing a sum, why isn't there a group by in the query ?
You either put only the 'sum' in the select, or use a group by. (I think)

 

[arnelgp]

change your Query to Total (Σ) query.

 

[rainbows]

i have changed the first screen shot to total and this now takes me into design view again so i can keep testing but the second screen shot that uses the first screen shot but still wont let me into the design view / so i dont know how to change it

steve

 

[The_Doc_Man]

First, that is one helluva complex query and hard to read. For future reference, it might be easier for us if you did a cut/paste of the SQL rather than a screen shot. Use the CODE tag (in []) in front and /CODE (in []) at the back of the pasted segment. That will help us understand it better. OR paste the code then go back and highlight it in the post and then click the </> symbol in the post's menu line.

Second, the error is telling you that Access saw OrderQty in a context where aggregation was occurring but it was neither part of an Aggregate function nor part of a grouping specification.

The rule (roughly speaking) is that when you reference something in an aggregates query, every reference must be either part of an aggregate, part of a WHERE clause, or part of a GROUP BY list. A "naked" field cannot appear by itself in a SELECT clause for a Totals query - it must be matched by an ORDER BY clause. A field CAN appear as part of an expression if the expression as a whole conforms to the previous rule.

 

[Gasman]

Would be eaiser to just click the </> icon and then paste into the new window?
I must admit I manually add the code tags myself, but to newbies that method is far easier.

 

[rainbows]

SELECT Now()+91 AS EndDate, [Order Details].ProductNo, Stocklist.allocation, Types.Type, Stocklist.StockNumber, Stocklist.Material, Sum([Order Details]![OrderQty]*[Product Detail]![ProductQty]) AS ReqQty, Stocklist.DeadQty, Stocklist.StockQty, Stocklist.BpQty, Stocklist.PendingQty, Stocklist.OnOrderQty, [StockQty]+[OnOrderQty]+[PendingQty]-Sum([Order Details]![OrderQty]*[Product Detail]![ProductQty]) AS DiffQty, Stocklist.UnitID, Sum([Order Details]![OrderQty]*[Product Detail]![ProductQty]) AS ReqQty2, [Order Details].partsissued, [Order Details].[parts ordered], [Order Details].shipped
FROM (Types INNER JOIN Stocklist ON Types.TypeID = Stocklist.TypeID) INNER JOIN ([Order Details] INNER JOIN [Product Detail] ON [Order Details].ProductNo = [Product Detail].ProductID) ON Stocklist.MaterialID = [Product Detail].Material
WHERE ((([Order Details].Required)<=Now()+91))
GROUP BY Now()+91, [Order Details].ProductNo, Stocklist.allocation, Types.Type, Stocklist.StockNumber, Stocklist.Material, Stocklist.DeadQty, Stocklist.StockQty, Stocklist.BpQty, Stocklist.PendingQty, Stocklist.OnOrderQty, Stocklist.UnitID, [Order Details].partsissued, [Order Details].[parts ordered], [Order Details].shipped
HAVING (((Types.Type)<>"capacitor") AND (([StockQty]+[OnOrderQty]+[PendingQty]-Sum([Order Details]![OrderQty]*[Product Detail]![ProductQty]))<0))
ORDER BY Types.Type, Stocklist.StockNumber;

I Have deleted code just to try and open the query in design view , and then managed to get the query working again

 

[The_Doc_Man]

If the query is working again, great! Don't know if I helped that much, but glad to have contributed.