Sort month chronologically

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

neeraj - 02 Nov 2005 20:57 GMT

I have a calculated field in a query say Qry1 based on a linked table say Tbl1:
Month: Format([submit_date],"mmmm") & " " & Format([submit_date],"yyyy")
whose output is like: September 2005. submit_date is a date/time type field
in Tbl1. I am writing another query say Qry2 based on Qry1 in which there are
2 grouping levels: another field say Fld2 and Month. In Qry2 results, results
are grouped properly but within each Fld2 group, Month appears alphabetically
and not chronologically. Earlier in Qry1, I had another formula for Month:
DatePart('m',[submit_date]) and Month was sorted numerically and of course
chronologically. How can I have my alphabetical format and sort
chronologically also in Qry2

Reply to this Message

Ofer - 02 Nov 2005 21:23 GMT

I'm not sure that this is what you are looking for, but try this

Select Format([submit_date],"mmmm") & " " & Format([submit_date],"yyyy") As
MonthField From TableName Order By Month([submit_date])

The order by doesn't need to have any connection to the way you display the
records

Signature

If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck

> I have a calculated field in a query say Qry1 based on a linked table say Tbl1:
> Month: Format([submit_date],"mmmm") & " " & Format([submit_date],"yyyy")
[quoted text clipped - 6 lines]
> chronologically. How can I have my alphabetical format and sort
> chronologically also in Qry2

Reply to this Message

neeraj - 02 Nov 2005 22:17 GMT

I understand what you are saying but this groups all the records of each
month together and yes, it sorts chronologically but I want them sorted
chrono only within groups of Fld2, your approach clubs together every
occurence of a specific month together regardless of Fld2 value

> I'm not sure that this is what you are looking for, but try this
>
[quoted text clipped - 14 lines]
> > chronologically. How can I have my alphabetical format and sort
> > chronologically also in Qry2

Reply to this Message

Ken Snell [MVP] - 03 Nov 2005 00:48 GMT

Post the SQL statement that you're using... it'll be easier to suggest a
modification if we can see how you're grouping the data, etc.

Signature

Ken Snell
<MS ACCESS MVP>

>I understand what you are saying but this groups all the records of each
> month together and yes, it sorts chronologically but I want them sorted
[quoted text clipped - 29 lines]
>> > chronologically. How can I have my alphabetical format and sort
>> > chronologically also in Qry2

Reply to this Message

neeraj - 03 Nov 2005 18:33 GMT

Here is the SQL of my simplified Qry2:

SELECT [Qry1].Fld2, [Qry1].Month
FROM [Qry1]
GROUP BY [Qry1].Fld2, [Qry1].Month;

When the calculated field Month in Qry1 is defined as Month:
DatePart('m',[submit_date]), I get the following results:

Fld2    Month
A    6
A    7
A    8
A    9
A    10
B    7
B    8
B    9
C ...
....

and when I define the same field as Month: Format([submit_date],"mmm") & " "
& Format([submit_date],"yyyy"), I get the following results:
Fld2    Month
A    Aug 2005
A    Jul 2005
A    Jun 2005
A    Oct 2005
A    Sep 2005
B    Aug 2005
B    Jul 2005
B    Sep 2005
C ...
....

(BTW, it seems that the Group By statement not only groups but also sorts
the results as seen from the above results without even an explicit Order By
statement).
This is how I would like the results to be displayed:
Fld2    Month
A    Jun 05
A    Jul 05
A    Aug 05
A    Sep 05
A    Oct 05
B    Jul 05
B    Aug 05
B    Sep 05
C ...
....

How can I do it?

> Post the SQL statement that you're using... it'll be easier to suggest a
> modification if we can see how you're grouping the data, etc.
[quoted text clipped - 32 lines]
> >> > chronologically. How can I have my alphabetical format and sort
> >> > chronologically also in Qry2

Reply to this Message

Ken Snell [MVP] - 04 Nov 2005 02:03 GMT

SELECT [Qry1].Fld2, [Qry1].[Month]
FROM [Qry1]
GROUP BY [Qry1].Fld2, [Qry1].[Month]
ORDER BY [Qry1].Fld2, [Qry1].[Month];

Also, do not use Month as a field, control, or variable name! It's a VBA
function, and one of many reserved words in ACCESS. See these articles for
more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Signature

Ken Snell
<MS ACCESS MVP>

> Here is the SQL of my simplified Qry2:
>
[quoted text clipped - 92 lines]
>> >> > chronologically. How can I have my alphabetical format and sort
>> >> > chronologically also in Qry2

Reply to this Message