Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015
    Posts
    31

    Question Unanswered: You tried to execute a query that does not include the specified expression aggregate

    Hello everyone,

    I've been working on a calculated field in my Access database, i recently added some new criteria to this calculation however it now doesn't work. Prior to me adding this extra criteria it was working fine. Now when i try and run my query the following message is displayed:

    "You tried to execute a query that does not include the specified expression aggregate".

    Here is the part of my SQL underlined in red that was recently added but i can't see any faults to it, unless i am misunderstanding the source of my problems.

    SELECT [001- Format OF 7-15-29].[Nom Client] AS Client, POS.[Nom Acheteur], Format([001- Format OF 7-15-29]![' Mois],"mm/yyyy") AS [' Mois], [001- Format OF 7-15-29].OF, [001- Format OF 7-15-29].Référence, [001- Format OF 7-15-29].Délai, [001- Format OF 7-15-29].[Qte Cde] AS [Reste à livrer], [005- Livraisons + Max N°BL].[Qté cde] AS [Qté cde initiale], Sum([005- Livraisons + Max N°BL].[Qté facturée]) AS [SommeDeQté facturée], [005- Livraisons + Max N°BL].[MaxDeN° BL], [005- Livraisons + Max N°BL].[MaxDeDt BL], IIf([001- Format OF 7-15-29]![Qte Cde]<[001- Format OF 7-15-29]![StkDisp],"1",IIf([001- Format OF 7-15-29]![Fin Prévue]<[001- Format OF 7-15-29]![Délai]+30,"0",IIf([001- Format OF 7-15-29]![Fin Prévue]>[001- Format OF 7-15-29]![Délai] Or [001- Format OF 7-15-29]![Fin Prévue]=[001- Format OF 7-15-29]![Délai] Or [001- Format OF 7-15-29]![Fin Prévue]+7<[001- Format OF 7-15-29]![Délai],"Livrées OK",IIf([001- Format OF 7-15-29]![Fin Prévue]="" Or [diag anomalie]="En Stock","1",IIf([002- 17-40 OF avancé]![Section]="A LANCER" Or [001- Format OF 7-15-29]![OF]<>[002- 17-40 OF avancé]![PremierDeOF],"X",IIf([001- Format OF 7-15-29]![Fin Prévue]<[001- Format OF 7-15-29]![Délai]+7,"Livrées KO",IIf([001- Format OF 7-15-29]![Fin Prévue]<=[001- Format OF 7-15-29]![Délai],"A",IIf([001- Format OF 7-15-29]![StkDisp]<[005- Livraisons + Max N°BL]![Qté cde],[005- Livraisons + Max N°BL]![Qté facturée]/[005- Livraisons + Max N°BL]![Qté cde],"X")))))))) AS Statut,

    I didn't want to post the whole thing because it is pretty huge. Don't be alarmed by any of the weird vocabulary as i'm working for a french company and must therefore use french fields.

    Any help is very much appreciated !

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    sounds like you have to add additional field(s) to the group by. Hard for us to tell as we don't have entire SQL
    Dave

  3. #3
    Join Date
    Mar 2015
    Posts
    31
    I found the source of the problem but now i don't know how to deal with it:

    SELECT [001- Format OF 7-15-29].[Nom Client] AS Client, POS.[Nom Acheteur], Format([001- Format OF 7-15-29]![Mois],"mm/yyyy") AS Mois, [001- Format OF 7-15-29].OF, [001- Format OF 7-15-29].Référence, [001- Format OF 7-15-29].Délai, [001- Format OF 7-15-29].[Qte Cde] AS [Reste à livrer], [005- Livraisons + Max N°BL].[Qté cde] AS [Qté cde initiale], Sum([005- Livraisons + Max N°BL].[Qté facturée]) AS [SommeDeQté facturée], [005- Livraisons + Max N°BL].[MaxDeN° BL], [005- Livraisons + Max N°BL].[MaxDeDt BL], IIf([001- Format OF 7-15-29]![Qte Cde]<[001- Format OF 7-15-29]![StkDisp],"1",IIf([001- Format OF 7-15-29]![Fin Prévue]<[001- Format OF 7-15-29]![Délai]+30,"0",IIf([001- Format OF 7-15-29]![Fin Prévue]>[001- Format OF 7-15-29]![Délai] Or [001- Format OF 7-15-29]![Fin Prévue]=[001- Format OF 7-15-29]![Délai] Or [001- Format OF 7-15-29]![Fin Prévue]+7<[001- Format OF 7-15-29]![Délai],"Livrées OK",IIf([001- Format OF 7-15-29]![Fin Prévue]="" Or [diag anomalie]="En Stock","1",IIf([002- 17-40 OF avancé]![Section]="A LANCER" Or [001- Format OF 7-15-29]![OF]<>[002- 17-40 OF avancé]![PremierDeOF],"X",IIf([001- Format OF 7-15-29]![Fin Prévue]<[001- Format OF 7-15-29]![Délai]+7,"Livrées KO",IIf([001- Format OF 7-15-29]![Fin Prévue]<=[001- Format OF 7-15-29]![Délai],"A",IIf([001- Format OF 7-15-29]![StkDisp]<[005- Livraisons + Max N°BL]![Qté cde],[005- Livraisons + Max N°BL]![Qté facturée]/[005- Livraisons + Max N°BL]![Qté cde],"X")))))))) AS Statut, [001- Format OF 7-15-29].[Fin Prévue], [001- Format OF 7-15-29].StkDisp, IIf(Left([17-40]![Réclamation],2)="AC","voir planning AC",IIf([001- Format OF 7-15-29]![Act]="f00","Atelier Rapide",IIf((IsNull([001- Format OF 7-15-29]![Fin Prévue]) And [001- Format OF 7-15-29]![StkDisp]=0 And [001- Format OF 7-15-29]![Délai]<Now()),"A solder?",IIf([001- Format OF 7-15-29]![Qte Cde]<[001- Format OF 7-15-29]![StkDisp],"En Stock",IIf([001- Format OF 7-15-29]![StkDisp]>0,"Stock partiel",IIf([001- Format OF 7-15-29]![OF]<>[002- 17-40 OF avancé]![PremierDeOF],"Changement d'OF",IIf(Left([002- 17-40 OF avancé]![En Chg],2)="NC","NC",IIf(Left([002- 17-40 OF avancé]![Réclamation],2)="MQ","MQ",IIf([005- Livraisons + Max N°BL]![Reste à livrer]>[002- 17-40 OF avancé]![QtéOF],"Qté OF insuffisant","ok"))))))))) AS [diag anomalie], [FAI Suivi qualité].Observations AS [Commentaire Qlté], [Suivi DVI].PRIORITES AS [Prio DVI], [001- Format OF 7-15-29].Article, [002- 17-40 OF avancé].PremierDeOF, [002- 17-40 OF avancé].[En Chg], [002- 17-40 OF avancé].QtéOF, [002- 17-40 OF avancé].Section, [002- 17-40 OF avancé].CCharge, [002- 17-40 OF avancé].[Op Q], [002- 17-40 OF avancé].[OP Q+1], [002- 17-40 OF avancé].[Attente Sect], [002- 17-40 OF avancé].Réclamation, [002- 17-40 OF avancé].[Commentaire Réclamation], [001- Format OF 7-15-29].[Partiel Prévu], [001- Format OF 7-15-29].[Qte Lan], [001- Format OF 7-15-29].[Spot/Contrat], [001- Format OF 7-15-29].Prix, [001- Format OF 7-15-29].Act, [001- Format OF 7-15-29].ARC, [001- Format OF 7-15-29].Commande, [001- Format OF 7-15-29].Lg, [001- Format OF 7-15-29].Poste, IIf([005- Livraisons + Max N°BL]![MaxDeDt Prom]<[005- Livraisons + Max N°BL]![MaxDeDt BL],"0",[005- Livraisons + Max N°BL]![Qté facturée]/[005- Livraisons + Max N°BL]![Qté cde]) AS [OTD Réel], Mid([001- Format OF 7-15-29]![Commande],1,3) AS [Type de commande]
    FROM (((([001- Format OF 7-15-29] LEFT JOIN [FAI Suivi qualité] ON [001- Format OF 7-15-29].Référence=[FAI Suivi qualité].REF) LEFT JOIN [Suivi DVI] ON ([001- Format OF 7-15-29].Commande=[Suivi DVI].Commande) AND ([001- Format OF 7-15-29].Article=[Suivi DVI].[Réf Article])) LEFT JOIN POS ON [001- Format OF 7-15-29].Commande=POS.Commande) LEFT JOIN [002- 17-40 OF avancé] ON ([001- Format OF 7-15-29].OF=[002- 17-40 OF avancé].OF) AND ([001- Format OF 7-15-29].Article=[002- 17-40 OF avancé].Article)) LEFT JOIN [005- Livraisons + Max N°BL] ON ([001- Format OF 7-15-29].ARC=[005- Livraisons + Max N°BL].ARC) AND ([001- Format OF 7-15-29].Commande=[005- Livraisons + Max N°BL].Commande) AND ([001- Format OF 7-15-29].Poste=[005- Livraisons + Max N°BL].Poste)
    GROUP BY [001- Format OF 7-15-29].[Nom Client], POS.[Nom Acheteur], Format([001- Format OF 7-15-29]![Mois],"mm/yyyy"), [001- Format OF 7-15-29].OF, [001- Format OF 7-15-29].Référence, [001- Format OF 7-15-29].Délai, [001- Format OF 7-15-29].[Qte Cde], [005- Livraisons + Max N°BL].[Qté cde], [005- Livraisons + Max N°BL].[MaxDeN° BL], [005- Livraisons + Max N°BL].[MaxDeDt BL], IIf([001- Format OF 7-15-29]![Qte Cde]<[001- Format OF 7-15-29]![StkDisp],"1",IIf([001- Format OF 7-15-29]![Fin Prévue]<[001- Format OF 7-15-29]![Délai]+30,"0",IIf([001- Format OF 7-15-29]![Fin Prévue]>[001- Format OF 7-15-29]![Délai] Or [001- Format OF 7-15-29]![Fin Prévue]=[001- Format OF 7-15-29]![Délai] Or [001- Format OF 7-15-29]![Fin Prévue]+7<[001- Format OF 7-15-29]![Délai],"Livrées OK",IIf([001- Format OF 7-15-29]![Fin Prévue]="" Or [diag anomalie]="En Stock","1",IIf([002- 17-40 OF avancé]![Section]="A LANCER" Or [001- Format OF 7-15-29]![OF]<>[002- 17-40 OF avancé]![PremierDeOF],"X",IIf([001- Format OF 7-15-29]![Fin Prévue]<[001- Format OF 7-15-29]![Délai]+7,"Livrées KO",IIf([001- Format OF 7-15-29]![Fin Prévue]<=[001- Format OF 7-15-29]![Délai],"A",IIf([001- Format OF 7-15-29]![StkDisp]<[005- Livraisons + Max N°BL]![Qté cde],[005- Livraisons + Max N°BL]![Qté facturée]/[005- Livraisons + Max N°BL]![Qté cde],"X")))))))), [001- Format OF 7-15-29].[Fin Prévue], [001- Format OF 7-15-29].StkDisp, IIf(Left([17-40]![Réclamation],2)="AC","voir planning AC",IIf([001- Format OF 7-15-29]![Act]="f00","Atelier Rapide",IIf((IsNull([001- Format OF 7-15-29]![Fin Prévue]) And [001- Format OF 7-15-29]![StkDisp]=0 And [001- Format OF 7-15-29]![Délai]<Now()),"A solder?",IIf([001- Format OF 7-15-29]![Qte Cde]<[001- Format OF 7-15-29]![StkDisp],"En Stock",IIf([001- Format OF 7-15-29]![StkDisp]>0,"Stock partiel",IIf([001- Format OF 7-15-29]![OF]<>[002- 17-40 OF avancé]![PremierDeOF],"Changement d'OF",IIf(Left([002- 17-40 OF avancé]![En Chg],2)="NC","NC",IIf(Left([002- 17-40 OF avancé]![Réclamation],2)="MQ","MQ",IIf([005- Livraisons + Max N°BL]![Reste à livrer]>[002- 17-40 OF avancé]![QtéOF],"Qté OF insuffisant","ok"))))))))), [FAI Suivi qualité].Observations, [Suivi DVI].PRIORITES, [001- Format OF 7-15-29].Article, [002- 17-40 OF avancé].PremierDeOF, [002- 17-40 OF avancé].[En Chg], [002- 17-40 OF avancé].QtéOF, [002- 17-40 OF avancé].Section, [002- 17-40 OF avancé].CCharge, [002- 17-40 OF avancé].[Op Q], [002- 17-40 OF avancé].[OP Q+1], [002- 17-40 OF avancé].[Attente Sect], [002- 17-40 OF avancé].Réclamation, [002- 17-40 OF avancé].[Commentaire Réclamation], [001- Format OF 7-15-29].[Partiel Prévu], [001- Format OF 7-15-29].[Qte Lan], [001- Format OF 7-15-29].[Spot/Contrat], [001- Format OF 7-15-29].Prix, [001- Format OF 7-15-29].Act, [001- Format OF 7-15-29].ARC, [001- Format OF 7-15-29].Commande, [001- Format OF 7-15-29].Lg, [001- Format OF 7-15-29].Poste, IIf([005- Livraisons + Max N°BL]![MaxDeDt Prom]<[005- Livraisons + Max N°BL]![MaxDeDt BL],"0",[005- Livraisons + Max N°BL]![Qté facturée]/[005- Livraisons + Max N°BL]![Qté cde]), Mid([001- Format OF 7-15-29]![Commande],1,3)
    HAVING ((([001- Format OF 7-15-29].[Nom Client]) Like "*Dassault*") And ((Format([001- Format OF 7-15-29]!Mois,"mm/yyyy"))=[Entrer la date mm/aaa]) And (([001- Format OF 7-15-29].Commande)<>""));
    Last edited by Guy Winfield; 06-16-15 at 07:23.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •