Query to group by months, last n months.

[CMontoro]

I need to make a consultation SQL that groups per months the data of a table (INVOICE) in which there is a field that is the date in format dd/mm/aaaa (DATEINV). Show last n months selected from a form, beginning of the present month to backwards

Thanks

 

[Jon K]

I have attached a sample database.

You can open the form, type the number of months and click on the command button to run the query:-

SELECT Format([DATEINV],"yyyy mmmm") AS [Month], Count([INVOICE].[Amount]) AS InovicCount,
Sum([INVOICE].[Amount]) AS MonthlyTotal, Avg([INVOICE].[Amount]) AS MonthlyAverage
FROM INVOICE
WHERE [DATEINV] Between DateSerial(year(Date()),Month(Date())-[Forms]![MyForm]![txtNMonths]+1,1) And
DateSerial(year(Date()),Month(Date())+1,0)
GROUP BY Format([DATEINV],"yyyy mm"), Format([DATEINV],"yyyy mmmm");
.

 

[CMontoro]

Sorry. I'm late
Yours answer helped me a lot
Thanks
Carlos