Sort month chronologically

[neeraj]

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

 

[Ofer]

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

 

[neeraj]

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

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
--
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")
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

 

[Ken Snell [MVP]]

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.

--

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
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

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
--
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")
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

 

[neeraj]

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.

--

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
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

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
--
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")
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

 

[Ken Snell [MVP]]

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
--

Ken Snell
<MS ACCESS MVP>

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.

--

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
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

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
--
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")
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