Group by month and year

[Captive]

I try this code

SELECT Format(ReportTbl.lot_date,"mm-yyyy") AS Lot, 
Round(Avg(ReportTbl.avg_dat),2) AS DataAvg, 
Round(Avg(ReportTbl.avg_len),2) AS LenAvg, 
Round(Avg(ReportTbl.avg_in),2) AS InAvg, 
Round(Avg(ReportTbl.avg_out),2) AS OutAvg, 
Round(Avg(ReportTbl.avg_thi),2) AS ThiAvg, 
Round(Avg(ReportTbl.avg_moi),2) AS MoiAvg
FROM ReportTbl
[B]GROUP BY Format(ReportTbl.lot_date,"mm-yyyy")[/B]
ORDER BY ReportTbl.lot_date;

But it not working and have warning box with "you tried to execute a query that does not include the specified expression 'ReportTbl.lot_date' as part of an aggregate function"

and when I try
GROUP BY Month(ReportTbl.lot_date),Year(ReportTbl.lot_date) or
GROUP BY datepart("m",ReportTbl.lot_date),datepart("yyyy",ReportTbl.lot_date)
or other code that I can search in google

I get the warning message that resemble with above message

and when I try to GROUP BY ReportTbl.lot_date it work but the result does not meet the requirements.

...(TOT)...

 

[plog]

Try this:

SELECT Format(ReportTbl.lot_date,"mm-yyyy") AS Lot, 
Round(Avg(ReportTbl.avg_dat),2) AS DataAvg, 
Round(Avg(ReportTbl.avg_len),2) AS LenAvg, 
Round(Avg(ReportTbl.avg_in),2) AS InAvg, 
Round(Avg(ReportTbl.avg_out),2) AS OutAvg, 
Round(Avg(ReportTbl.avg_thi),2) AS ThiAvg, 
Round(Avg(ReportTbl.avg_moi),2) AS MoiAvg
FROM ReportTbl
GROUP BY Format(ReportTbl.lot_date,"mm-yyyy")
ORDER BY MIN(ReportTbl.lot_date);

It adds an aggregate function (MIN) to lot_date to get it to order correctly.

 

[Captive]

Thank you plog ^.^

 

[jreedb]

Very grateful I am to find tiny snippet, so obvious but so nice to find... it made my afternoon much more pleasant. Sometimes the most obvious are the most frustrating.

GROUP BY Format(ReportTbl.lot_date,"mm-yyyy")
ORDER BY MIN(ReportTbl.lot_date);

Many thanks!