Reporto to show month and year

[mari_hitz]

Hi everyone,

Hope you are good. I have a question regarding reports. I have a database that contains a table with volumes. This table has the following fields: Country, Volume, Month, Year. The field Month comes from another table which is has MonthID and Month.
Then I have a query based on this table where I perform several formulas and I have country, month, year, volume, formula1,formula2.
I am creating a report based on this query and I would like results to appear by year and by month in order. If I put "ascending" in the field "Year" the results are brought correctly by year, but by month not. Example:
Result 1, 2013, January
Result 2, 2013, November
Result 3, 2013, May

I want it to be
Result 1, 2013, January
Result 2, 2013, February
Result 3, 2013, March
Result 4, 2013, April
(....)

I have tried several ways to make it ordered by year and month but had no good results. Do you have any idea? Thanks!

 

[Beetle]

You need to use Sorting and Grouping in the report itself. Trying to sort at the query will not produce reliable results in a report because any existing sort rules in the report will override anything you attempt in the query.

 

[plog]

I agree with sorting/grouping in the report to get it like you want, but you will also need to edit the query. You will need a field in there to determine the sort order of the months. As you have it now the field called 'month' (which is a poor choice for a field name because its a reserved word - http://support.microsoft.com/kb/286335) is a string and will be sorted as such: April, December, February,...

You need a field in your data that allows you to correctly put the months in the proper order. Hopefully the field MonthID does that, if so bring it into your query and Sort by it in your report. If not, you need to figure out a field on which you can do that.