Error 3122

[egidijus.jankauskas]

Hi,
when I run a query it says "You tried to execute a query that does not include the specified expression '[Receiving details]![Price FC]*0.1*[Receiving]![Exchange Rate]' as part of an aggregate function." and
access help says "You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)". There is SQL code:

SELECT [Deliveries to factory].DeliveryID, [Deliveries to factory].[Delivery to factory date], [Additional costs].[Customs clearance date], [Additional costs].Discharge, [Additional costs].[Customs brokerage], [Additional costs].[IMP garanties], [Additional costs].Storage, [Additional costs].[Storage per day], [Deliveries to factory].ReceivingID, [Deliveries to factory]![Delivery to factory date]-[Additional costs]![Customs clearance date] AS DACC, [Deliveries to factory].[Gross weight], [Deliveries to factory].[Net weight], [Net Weight]/DLookUp("[Sum Of Weight]","[Pivot query]","[ReceivingID]=" & [Receiving]![ReceivingID]) AS POTC, Receiving.ReceivingID, [POTCG]*[Discharge] AS DischargePart, [POTCG]*[IMP garanties] AS [IMP garantiesPart], [POTCG]*[Customs brokerage] AS CustBrokeragePart, [POTCG]*[Storage] AS StoragePart, [DACC]*[Storage per day]*[Deliveries to factory].[Gross weight]*0.001 AS [Storage ACC], [Deliveries to factory].TranspCosts, [POTC]*[Total LTL]+[DischargePart]+[IMP garantiesPart]+[custbrokeragePart]+[storagepart]+[Storage ACC]+[TranspCosts] AS PriceKG, Receiving.[Received in Store], [Deliveries to factory]![Gross weight]/DLookUp("[Gross weight]","[Receiving]","[ReceivingID]=" & [Receiving]![ReceivingID]) AS POTCG, [Receiving details]![Price FC]*0.1*[Receiving]![Exchange Rate] AS [TOTAL LTL]
FROM ((Receiving INNER JOIN [Additional costs] ON Receiving.ReceivingID = [Additional costs].ReceivingID) INNER JOIN [Deliveries to factory] ON Receiving.ReceivingID = [Deliveries to factory].ReceivingID) INNER JOIN [Receiving details] ON Receiving.ReceivingID = [Receiving details].ReceivingID
GROUP BY [Deliveries to factory].DeliveryID, [Deliveries to factory].[Delivery to factory date], [Additional costs].[Customs clearance date], [Additional costs].Discharge, [Additional costs].[Customs brokerage], [Additional costs].[IMP garanties], [Additional costs].Storage, [Additional costs].[Storage per day], [Deliveries to factory].ReceivingID, [Deliveries to factory].[Gross weight], [Deliveries to factory].[Net weight], Receiving.ReceivingID, [Deliveries to factory].TranspCosts, Receiving.[Received in Store];

Anyone has any ideas where is the problem THank you.​

 

[Galaxiom]

The error indicates you have not included one the fields you have selected to be part of a GroupBy clause. Any field that is not part of aggregates such as SUM, AVG, MAX, MIN etc must be included in the Group By.

BTW. Post code in a code box and format it for readability.

I would also reconsider the names you use. Firstly dump the spaces and reconsider the length of some names. Do you really need to include prepositions? For example:

[Deliveries to factory]![Delivery to factory date]

 

[egidijus.jankauskas]

1. I thought if a field has a formula than I have to choose "Expression" in TOTAL row. And I've used to do this in my previous query which was succesfully done.
2. If I choose "Group by" in last column then I have results in my query multiplied by 5 (the same numbers in 5 separate rows).
3. Sorry, but I'dont know how to run Code box. Is this function included in Access, or It's third part software.
4. I could reconsider the names but what it will change?

 

[Galaxiom]

I have never quite got my head around when Access accepts Expression or demands GroupBy. However if you reformt you code it might be easier to see.

Another way to help readability is to alias the table names. eg:

SELECT A.fieldname FROM [somebigtablename] AS A

You can edit your original post. For the code box go to the Advanced Editor and select the code section. Hit the hash (#) button.

This simply put tags before and after the code, respectively, [code ] and [/code ] (without the spaces). Many posters just insert them manually as they do with [quote ] tags.

The code box allows the forum to display multiple spaces in sequence, a better font to recognise in particular a pair of single quotes in sequence, which can otherwise look like a double quote.

 

[egidijus.jankauskas]

I have reconsidered the names. And I hope that now will be not so difficult to observe a problem:

SELECT tblDeliveriesToFactory.DeliveryID, [tblDeliveriesToFactory].[Delivery to factory date] AS Expr1, tblAdditionalCosts.[Customs clearance date], tblAdditionalCosts.Discharge, tblAdditionalCosts.[Customs brokerage], tblAdditionalCosts.[IMP garanties], tblAdditionalCosts.Storage, tblAdditionalCosts.[Storage per day], tblDeliveriesToFactory.ReceivingID, [tblDeliveriesToFactory]![Delivery to factory date]-[tblAdditionalCosts]![Customs clearance date] AS DACC, [tblDeliveriesToFactory].[Gross weight] AS Expr2, [tblDeliveriesToFactory].[Net weight] AS Expr3, [Net Weight]/DLookUp("[Sum Of Weight]","[Pivot query]","[ReceivingID]=" & [tblReceiving]![ReceivingID]) AS POTC, tblReceiving.ReceivingID, [POTCG]*[Discharge] AS DischargePart, [POTCG]*[IMP garanties] AS [IMP garantiesPart], [POTCG]*[Customs brokerage] AS CustBrokeragePart, [POTCG]*[Storage] AS StoragePart, [DACC]*[Storage per day]*[Deliveries to factory].[Gross weight]*0.001 AS [Storage ACC], tblDeliveriesToFactory.TranspCosts, [POTC]*[Total LTL]+[DischargePart]+[IMP garantiesPart]+[custbrokeragePart]+[storagepart]+[Storage ACC]+[TranspCosts] AS PriceKG, tblReceiving.[Received in Store], [tblDeliveriesToFactory]![Gross weight]/DLookUp("[Gross weight]","[tblReceiving]","[ReceivingID]=" & [tblReceiving]![ReceivingID]) AS POTCG, [tblReceivingDetails]![Price FC]*0.1*[tblReceiving]![Exchange Rate] AS [TOTAL LTL]
FROM ((tblReceiving INNER JOIN tblAdditionalCosts ON tblReceiving.ReceivingID = tblAdditionalCosts.ReceivingID) INNER JOIN tblDeliveriesToFactory ON tblReceiving.ReceivingID = tblDeliveriesToFactory.ReceivingID) INNER JOIN tblReceivingDetails ON tblReceiving.ReceivingID = tblReceivingDetails.ReceivingID
GROUP BY tblDeliveriesToFactory.DeliveryID, [tblDeliveriesToFactory].[Delivery to factory date], tblAdditionalCosts.[Customs clearance date], tblAdditionalCosts.Discharge, tblAdditionalCosts.[Customs brokerage], tblAdditionalCosts.[IMP garanties], tblAdditionalCosts.Storage, tblAdditionalCosts.[Storage per day], tblDeliveriesToFactory.ReceivingID, [tblDeliveriesToFactory].[Gross weight], [tblDeliveriesToFactory].[Net weight], tblReceiving.ReceivingID, tblDeliveriesToFactory.TranspCosts, tblReceiving.[Received in Store];

The error now says: "You tried to execute a query that does not include the specified expression '[tblReceivingDetails]![Price FC]*0.1*[tblReceiving]![Exchange Rate]' as part of an aggregate function."

 

[Galaxiom]

Generally the easiest way to get around demands for grouping when you want an expression is to create a custom function.

Put something like this into a Standard Module.

Public Function MyFunction (Price as Currency, Exchange as Double) As Currency
    MyFunction = Price * 0.1 * Exchange)
End Function

(You may have to filddle with the datatypes of the arguments)

Call it in the query with:

NewFieldname: MyFunction ([tblReceivingDetails]![Price FC], [tblReceiving]![Exchange Rate])

You might also ned to do some rounding. Also note that working with Single and Double datatypes sometimes causes rounding problems that you wouldn't expect because computers work with binary representations of the number.

 

[egidijus.jankauskas]

Generally the easiest way to get around demands for grouping when you want an expression is to create a custom function.

Put something like this into a Standard Module.

Public Function MyFunction (Price as Currency, Exchange as Double) As Currency
    MyFunction = Price * 0.1 * Exchange)
End Function

(You may have to filddle with the datatypes of the arguments)

Call it in the query with:

NewFieldname: MyFunction ([tblReceivingDetails]![Price FC], [tblReceiving]![Exchange Rate])

You might also ned to do some rounding. Also note that working with Single and Double datatypes sometimes causes rounding problems that you wouldn't expect because computers work with binary representations of the number.

I have created the function succesfully. But now I have "Enter parameter value" question 10 times where field contains expression. Every time when I run the query it asks me to write some data. My query now looks like this:

SELECT tblDeliveriesToFactory.DeliveryID, tblAdditionalCosts.[Customs clearance date], tblDeliveriesToFactory.DelFactoryDate, tblAdditionalCosts.Discharge, tblAdditionalCosts.[Customs brokerage], tblAdditionalCosts.[IMP garanties], tblAdditionalCosts.Storage, tblAdditionalCosts.[Storage per day], tblDeliveriesToFactory.ReceivingID, DateDiff("d",[tblAdditionalCosts]![Customs clearance date],[tblDeliveriesToFactory]![DelFactoryDate]) AS DACC, [tblDeliveriesToFactory]![NetWeight]/DLookUp("[Sum Of Weight]","[qryPivotReceiving]","[ReceivingID]=" & [tblReceiving]![ReceivingID]) AS POTC, tblReceiving.ReceivingID, [POTCG]*[Discharge] AS DischargePart, [POTCG]*[IMP garanties] AS [IMP garantiesPart], [POTCG]*[Customs brokerage] AS CustBrokeragePart, [POTCG]*[Storage] AS StoragePart, [DACC]*[Storage per day]*[Tones delivered] AS [Storage ACC], tblDeliveriesToFactory.TranspCosts, [POTC]*[Total LTL]+[DischargePart]+[IMP garantiesPart]+[custbrokeragePart]+[storagepart]+[Storage ACC]+[TranspCosts] AS PriceKG, tblReceiving.[Received in Store], [tblDeliveriesToFactory]![Grossweight]/DLookUp("[Gross weight]","[tblReceiving]","[ReceivingID]=" & [tblReceiving]![ReceivingID]) AS POTCG, ExchangeFunction([tblReceivingDetails]![Price FC],[tblReceiving]![Exchange Rate]) AS [TOTAL LTL], Tones([tblDeliveriesToFactory]![GrossWeight]) AS [Tones delivered]
FROM ((tblReceiving INNER JOIN tblAdditionalCosts ON tblReceiving.ReceivingID = tblAdditionalCosts.ReceivingID) INNER JOIN tblDeliveriesToFactory ON tblReceiving.ReceivingID = tblDeliveriesToFactory.ReceivingID) INNER JOIN tblReceivingDetails ON tblReceiving.ReceivingID = tblReceivingDetails.ReceivingID
GROUP BY tblDeliveriesToFactory.DeliveryID, tblAdditionalCosts.[Customs clearance date], tblDeliveriesToFactory.DelFactoryDate, tblAdditionalCosts.Discharge, tblAdditionalCosts.[Customs brokerage], tblAdditionalCosts.[IMP garanties], tblAdditionalCosts.Storage, tblAdditionalCosts.[Storage per day], tblDeliveriesToFactory.ReceivingID, DateDiff("d",[tblAdditionalCosts]![Customs clearance date],[tblDeliveriesToFactory]![DelFactoryDate]), [tblDeliveriesToFactory]![NetWeight]/DLookUp("[Sum Of Weight]","[qryPivotReceiving]","[ReceivingID]=" & [tblReceiving]![ReceivingID]), tblReceiving.ReceivingID, [POTCG]*[Discharge], [POTCG]*[IMP garanties], [POTCG]*[Customs brokerage], [POTCG]*[Storage], [DACC]*[Storage per day]*[Tones delivered], tblDeliveriesToFactory.TranspCosts, [POTC]*[Total LTL]+[DischargePart]+[IMP garantiesPart]+[custbrokeragePart]+[storagepart]+[Storage ACC]+[TranspCosts], tblReceiving.[Received in Store], [tblDeliveriesToFactory]![Grossweight]/DLookUp("[Gross weight]","[tblReceiving]","[ReceivingID]=" & [tblReceiving]![ReceivingID]), ExchangeFunction([tblReceivingDetails]![Price FC],[tblReceiving]![Exchange Rate]), Tones([tblDeliveriesToFactory]![GrossWeight]);

Even if I do not put any data in It seems that the results are right. Any ideas?