# Thread: Grouping Query Records (2000)

1. ## Grouping Query Records (2000)

I'm trying to sum and average three fields in the attached QRend2Mens query by grouping on month and year but I get an error message when I run the following(QRend2Mensx) query.

<font color=red>SELECT Sum(G2Dez.Energia) AS SommaDiEnergia, Avg(Val(qx(Potmedia(G2dez.Energia,g2dez.Ore_marc),[QT],Potmedia(G3dez.Energia,g3dez.Ore_marc)))) AS PORT2, Sum(G2Dez.ORE_MARC) AS SommaDiORE_MARC, Month(g2dez.giorno) AS Espr1, Year(g2dez.giorno) AS Espr2
FROM (PortDez23 INNER JOIN G2Dez ON PortDez23.Giorno = G2Dez.GIORNO) INNER JOIN G3Dez ON PortDez23.Giorno = G3Dez.GIORNO
WHERE (((G2Dez.GIORNO) Between DateSerial([forms]![Mseledata1]![anno],[forms]![Mseledata1]![mese],1) And DateSerial([forms]![Mseledata1]![anno1],[forms]![Mseledata1]![mese1],gmese([forms]![Mseledata1]![anno1],[forms]![Mseledata1]![mese1]))))
GROUP BY Month(g2dez.giorno), Year(g2dez.giorno);</font color=red>

What can I change to make it work?

2. ## Re: Grouping Query Records (2000)

There are a few records for which qx(PotMedia(...)) returns Null; the Val function can't handle that. Use Nz to replace Null by 0:

Avg(Val(Nz(qx(Potmedia([G2dez].[Energia],[g2dez].[Ore_marc]),[QT],Potmedia([G3dez].[Energia],[g3dez].[Ore_marc])),0)))

3. ## Re: Grouping Query Records (2000)

Thank you Hans

#### Posting Permissions

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