How to query data on monthly basis

[morgan]

Please have a look at the below SQL. I'd like to get the data by categories,
on monthly basis, limiting the result on the last 5 months. The problem of my
query is that it cannot include data from the previous year. The query is
used to create a chart. Possibly I'd like to have a button in order to scroll
the chart back in the past and show data older that 5 month ago. How could I
do it?

SELECT DISTINCTROW Format$([Movimenti 2006-->].[Data Valuta],'mm yyyy') AS
[Data Valuta per mese], [Movimenti 2006-->].[Centro Costo], Sum([Movimenti
2006-->].[Importo Debito]) AS [Somma Di Importo Debito]
FROM [Movimenti 2006-->]
GROUP BY Format$([Movimenti 2006-->].[Data Valuta],'mm yyyy'), [Movimenti
2006-->].[Centro Costo], Year([Movimenti 2006-->].[Data
Valuta])*12+DatePart('m',[Movimenti 2006-->].[Data Valuta])-1, Year([data
valuta]), Month([data valuta])
HAVING (((Year([data valuta])) Like [Forms]![Movimenti Y]![data]) AND
((Month([data valuta])) Between Month(Date()) And Month(Date())-5));

 

[KARL DEWEY]

Try this ---
SELECT Format([Data Valuta],"mm yyyy") AS [Data Valuta per mese],
Sum([Movimenti 2006-->].[Centro Costo]) AS [SumOfCentro Costo],
Sum([Movimenti 2006-->].[Importo Debito]) AS [SumOfImporto Debito]
FROM [Movimenti 2006-->]
WHERE ((([Movimenti 2006-->].[Data Valuta]) Between Date()-Day(Date()) And
DateAdd("m",-5,Date()-Day(Date()))+1))
GROUP BY Format([Data Valuta],"mm yyyy");

 

[John W. Vinson]

Try this ---
SELECT Format([Data Valuta],"mm yyyy") AS [Data Valuta per mese],
Sum([Movimenti 2006-->].[Centro Costo]) AS [SumOfCentro Costo],
Sum([Movimenti 2006-->].[Importo Debito]) AS [SumOfImporto Debito]
FROM [Movimenti 2006-->]
WHERE ((([Movimenti 2006-->].[Data Valuta]) Between Date()-Day(Date()) And
DateAdd("m",-5,Date()-Day(Date()))+1))
GROUP BY Format([Data Valuta],"mm yyyy");

The disadvantage to this is that it will sort months before years - i.e. in
the order 01 2008, 09 2007, 10 2007 etc. To sort chronolgically you will need
to use a date format such as yyyymm for sorting and grouping; you could use
the existing format for display.

John W. Vinson [MVP]

 

[morgan]

Thks. It works.

Try this ---
SELECT Format([Data Valuta],"mm yyyy") AS [Data Valuta per mese],
Sum([Movimenti 2006-->].[Centro Costo]) AS [SumOfCentro Costo],
Sum([Movimenti 2006-->].[Importo Debito]) AS [SumOfImporto Debito]
FROM [Movimenti 2006-->]
WHERE ((([Movimenti 2006-->].[Data Valuta]) Between Date()-Day(Date()) And
DateAdd("m",-5,Date()-Day(Date()))+1))
GROUP BY Format([Data Valuta],"mm yyyy");

The disadvantage to this is that it will sort months before years - i.e. in
the order 01 2008, 09 2007, 10 2007 etc. To sort chronolgically you will need
to use a date format such as yyyymm for sorting and grouping; you could use
the existing format for display.

John W. Vinson [MVP]