Cannot group by query

[diegomarino]

Hi, i'm not able to group by date (a.data) this query

SELECT a.CF AS CFbase, a.data AS Data_base, (SELECT cf FROM qryassoreti as a1 WHERE a1.data=a.data AND a1.azienda = a.azienda and  a1.tipo = a.tipo)-(SELECT cf FROM qryassoreti as a1 WHERE a1.data=dateadd("M",-1,a.data) AND a1.azienda = a.azienda and  a1.tipo = a.tipo) AS [+- CF], Format((SELECT cf FROM qryassoreti as a1 WHERE a1.data=a.data AND a1.azienda = a.azienda and  a1.tipo = a.tipo)/(SELECT cf FROM qryassoreti as a1 WHERE a1.data=dateadd("M",-1,a.data) AND a1.azienda = a.azienda and  a1.tipo = a.tipo)-1,"Percent") AS [% CF], (SELECT Totale FROM qryassoreti as a2 WHERE a2.data=a.data AND a2.azienda = a.azienda and  a2.tipo = "Stock") AS StockBaseFROM qryassoreti AS a
WHERE (((a.data)>=[MASCHERE]![Aziende]![datainiziotb] And (a.data)<=[MASCHERE]![Aziende]![datafinetb]) AND ((a.azienda)=[Maschere]![Aziende]![Azienda])) OR (((a.data)<=[MASCHERE]![Aziende]![datafinetb]) AND ((a.azienda)=[Maschere]![Aziende]![Azienda]) AND (([MASCHERE]![Aziende]![datainiziotb]) Is Null)) OR (((a.data)>=[MASCHERE]![Aziende]![datainiziotb]) AND ((a.azienda)=[Maschere]![Aziende]![Azienda]) AND (([MASCHERE]![Aziende]![datafinetb]) Is Null)) OR (((a.azienda)=[Maschere]![Aziende]![Azienda]) AND (([MASCHERE]![Aziende]![datainiziotb]) Is Null) AND (([MASCHERE]![Aziende]![datafinetb]) Is Null))
ORDER BY a.data;

can you help me? tell me if you need more infos, thanks

[ranman256]

Is date in date format or String? numeric date format will sort correctly.

[June7]

You mean you can't ORDER by date? What field data type is a.data?

[diegomarino]

nevermind, the source of data is the problem.
i have to change that, however, the pic shows the result i get, and i need to group by date

[CJ_London]

Use an aggregate query - in query design it’s on the ribbon

[June7]

Grouping at what level? By the combination of AziendaBase/CFBase/Data_base? What do you want to do with the +/- and % fields? Maybe should build a report and use its Sorting & Grouping features with aggregate calcs. This allows display of detail data as well as summary info.

[diegomarino]

thanks both, now i'm pretty proficient in sql, i did a grouping but i got a strange error base on the source query. I just have to incorporate the precedent sql
thanks