Query by Month

[mring42]

When I try to group by month, sometimes rather than having the output as:

January 2003
February 2003
April 2003
May 2003
April 2004
May 2004

The output will be:

April 2003
April 2004
February 2003
January 2003
May 2003
May 2004

This only happens when I have many, many columns, but it's very difficult to see trends like that...
Thanks,
Mike

 

[scriverb]

You are grouping by an alpha field so it is sorting by the Alpha representation of the Month Year. Create a column in the reverse, Year Month with numeric only and group by that column. The column that displays the month year as you wish should not be grouped but be identified as an expression.

Select . . . 


Order by (Year([datefield]) & Month([datefield]))
Group by (Year([datefield]) & Month([datefield]))

Above is an example. Just supply your current code and I will update it appropriately if you need further help.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JonFer]

Add a column to the query where you format the date as "yyyymm" and sort on that instead. You can uncheck the "Display" box if you don't want this column in your output.

 

[mring42]

Thanks a lot for your help

Here's the Group By statement:

GROUP BY Format$([Birds-All_Crosstab].[Date],'mmmm yyyy'), Year([Birds-All_Crosstab].[Date])*12+DatePart('m',[Birds-All_Crosstab].[Date])-1;

 

[scriverb]

Is this your current Group By or a new one you are have tried to implement after reading the postings?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[mring42]

This is what I was using before reading the postings.

 

[scriverb]

You can leave your Select portion of the query intact so that the display of the data looks the same. Just change the Group By and Order By to the following and the records will group and display in the correct combinations and sorting order.

GROUP BY Year([Birds-All_Crosstab].[Date]) & Format([Birds-All_Crosstab].[Date],"mm") 
ORDER BY Year([Birds-All_Crosstab].[Date]) & Format([Birds-All_Crosstab].[Date],"mm");

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[mring42]

Thanks for your help. I had to tweak the Select statement slightly because the expression that I was using to group the Date column had to be changed, but I figured it out, and it's all working now.