order on the months.

  • Thread starter Thread starter Rodolfo Fontes
  • Start date Start date
R

Rodolfo Fontes

Hi group,

I've done a query to list all events, grouping by the month.
By this query, i've used the format() command to transform the date on
month.
After that, i've done another query to group the months.
And fnally, i've put then on a graph.
The trouble is, if i put the months in order on the graph, it will follow
the order, a... b.. c....
But I wanna follow Jan... Fev....March... and so.
Any ideas?

The SQL code from the first querie
SELECT Format([data],"mmmm") AS Mes, Count([T Eventos].DescrEvento) AS
Evento
FROM [T Eventos] INNER JOIN [T Ocorrencias] ON [T Eventos].CodEvento = [T
Ocorrencias].CodEvento
GROUP BY Format([data],"mmmm"), [T Ocorrencias].Data, [T
Ocorrencias].CodEvento
HAVING ((([T Ocorrencias].Data)>=nz([Formulários]![F
Relatorio_Completo]![txtData_I]) And ([T
Ocorrencias].Data)<=nz([Formulários]![F Relatorio_Completo]![txtData_F]))
AND (([T Ocorrencias].CodEvento)=[Formulários]![F
Relatorio_Completo]![cmbOcorrencia])) OR ((([Formulários]![F
Relatorio_Completo]![cmbOcorrencia]) Is Null));

The SQL code from the second querie
SELECT [C QtdOcorrenciasMesC].Mes, Count([C QtdOcorrenciasMesC].Evento) AS
Evento
FROM [C QtdOcorrenciasMesC]
GROUP BY [C QtdOcorrenciasMesC].Mes
ORDER BY [C QtdOcorrenciasMesC].Mes;

Thanks,
Rodolfo Fontes
 
Can I do that on the graph? My problem is there.


Duane Hookom said:
Add an Order By
Order By Month([data]);

--
Duane Hookom
MS Access MVP
--

Rodolfo Fontes said:
Hi group,

I've done a query to list all events, grouping by the month.
By this query, i've used the format() command to transform the date on
month.
After that, i've done another query to group the months.
And fnally, i've put then on a graph.
The trouble is, if i put the months in order on the graph, it will follow
the order, a... b.. c....
But I wanna follow Jan... Fev....March... and so.
Any ideas?

The SQL code from the first querie
SELECT Format([data],"mmmm") AS Mes, Count([T Eventos].DescrEvento) AS
Evento
FROM [T Eventos] INNER JOIN [T Ocorrencias] ON [T Eventos].CodEvento = [T
Ocorrencias].CodEvento
GROUP BY Format([data],"mmmm"), [T Ocorrencias].Data, [T
Ocorrencias].CodEvento
HAVING ((([T Ocorrencias].Data)>=nz([Formulários]![F
Relatorio_Completo]![txtData_I]) And ([T
Ocorrencias].Data)<=nz([Formulários]![F Relatorio_Completo]![txtData_F]))
AND (([T Ocorrencias].CodEvento)=[Formulários]![F
Relatorio_Completo]![cmbOcorrencia])) OR ((([Formulários]![F
Relatorio_Completo]![cmbOcorrencia]) Is Null));

The SQL code from the second querie
SELECT [C QtdOcorrenciasMesC].Mes, Count([C QtdOcorrenciasMesC].Evento) AS
Evento
FROM [C QtdOcorrenciasMesC]
GROUP BY [C QtdOcorrenciasMesC].Mes
ORDER BY [C QtdOcorrenciasMesC].Mes;

Thanks,
Rodolfo Fontes
Click to expand...
Click to expand...
 
Duane,

I've added that, as you said.
As i'm going to use them on the graph (bar graph), the graph should appears
on Jan.. Feb... and so. And grouping the data on months too.
Now, everything that i've got is a querie that returns the data, but still
on this format dd/mm/yyyy.
The Graph should appear only the months with the total.

Rodolfo Fontes

Duane Hookom said:
Add an Order By
Order By Month([data]);

--
Duane Hookom
MS Access MVP
--

Rodolfo Fontes said:
Hi group,

I've done a query to list all events, grouping by the month.
By this query, i've used the format() command to transform the date on
month.
After that, i've done another query to group the months.
And fnally, i've put then on a graph.
The trouble is, if i put the months in order on the graph, it will follow
the order, a... b.. c....
But I wanna follow Jan... Fev....March... and so.
Any ideas?

The SQL code from the first querie
SELECT Format([data],"mmmm") AS Mes, Count([T Eventos].DescrEvento) AS
Evento
FROM [T Eventos] INNER JOIN [T Ocorrencias] ON [T Eventos].CodEvento = [T
Ocorrencias].CodEvento
GROUP BY Format([data],"mmmm"), [T Ocorrencias].Data, [T
Ocorrencias].CodEvento
HAVING ((([T Ocorrencias].Data)>=nz([Formulários]![F
Relatorio_Completo]![txtData_I]) And ([T
Ocorrencias].Data)<=nz([Formulários]![F Relatorio_Completo]![txtData_F]))
AND (([T Ocorrencias].CodEvento)=[Formulários]![F
Relatorio_Completo]![cmbOcorrencia])) OR ((([Formulários]![F
Relatorio_Completo]![cmbOcorrencia]) Is Null));

The SQL code from the second querie
SELECT [C QtdOcorrenciasMesC].Mes, Count([C QtdOcorrenciasMesC].Evento) AS
Evento
FROM [C QtdOcorrenciasMesC]
GROUP BY [C QtdOcorrenciasMesC].Mes
ORDER BY [C QtdOcorrenciasMesC].Mes;

Thanks,
Rodolfo Fontes
Click to expand...
Click to expand...
 
What is the Row Source sql of the graph? Did you set this to order by the
numeric month?

--
Duane Hookom
MS Access MVP
--

Rodolfo Fontes said:
Duane,

I've added that, as you said.
As i'm going to use them on the graph (bar graph), the graph should
appears
on Jan.. Feb... and so. And grouping the data on months too.
Now, everything that i've got is a querie that returns the data, but still
on this format dd/mm/yyyy.
The Graph should appear only the months with the total.

Rodolfo Fontes

Duane Hookom said:
Add an Order By
Order By Month([data]);

--
Duane Hookom
MS Access MVP
--

Rodolfo Fontes said:
Hi group,

I've done a query to list all events, grouping by the month.
By this query, i've used the format() command to transform the date on
month.
After that, i've done another query to group the months.
And fnally, i've put then on a graph.
The trouble is, if i put the months in order on the graph, it will follow
the order, a... b.. c....
But I wanna follow Jan... Fev....March... and so.
Any ideas?

The SQL code from the first querie
SELECT Format([data],"mmmm") AS Mes, Count([T Eventos].DescrEvento) AS
Evento
FROM [T Eventos] INNER JOIN [T Ocorrencias] ON [T Eventos].CodEvento = [T
Ocorrencias].CodEvento
GROUP BY Format([data],"mmmm"), [T Ocorrencias].Data, [T
Ocorrencias].CodEvento
HAVING ((([T Ocorrencias].Data)>=nz([Formulários]![F
Relatorio_Completo]![txtData_I]) And ([T
Ocorrencias].Data)<=nz([Formulários]![F Relatorio_Completo]![txtData_F]))
AND (([T Ocorrencias].CodEvento)=[Formulários]![F
Relatorio_Completo]![cmbOcorrencia])) OR ((([Formulários]![F
Relatorio_Completo]![cmbOcorrencia]) Is Null));

The SQL code from the second querie
SELECT [C QtdOcorrenciasMesC].Mes, Count([C QtdOcorrenciasMesC].Evento) AS
Evento
FROM [C QtdOcorrenciasMesC]
GROUP BY [C QtdOcorrenciasMesC].Mes
ORDER BY [C QtdOcorrenciasMesC].Mes;

Thanks,
Rodolfo Fontes
Click to expand...
Click to expand...
Click to expand...
 
Back
Top