Sorting help

[Michael Lam]

Hi All

I have created query to group the records department and month

SELECT DISTINCTROW Format$([RequestFormAllrecords].[Created Date],'mmmm
yyyy') AS [Created Date/Month], RequestFormAllrecords.Department,
Sum([RequestFormAllrecords].[Estimate Effort]) AS [Total Estimate Effort],
Count(*) AS RequestFormAllrecordscount
FROM RequestFormAllrecords
GROUP BY Format$([RequestFormAllrecords].[Created Date],'mmmm yyyy'),
RequestFormAllrecords.Department, Year([RequestFormAllrecords].[Created
Date])*12+DatePart('m',[RequestFormAllrecords].[Created Date])-1;

however the output result like this
Created Date/Month Department Total Estimate Effort
RequestFormAllrecordscount
March 2004 AFFC 0
1
January 2004 AFFC 0.75
1
February 2004 AFFC 2
3

how can I sort the Create Date /Month so that It appear from Jan to Mar and
so on

Michael Lam

 

[Michel Walsh]

Hi,


Just add the appropriate ORDER BY clause:


SELECT whatever

FROM RequestFormAllrecords

GROUP BY Year(RequestFormAllrecords.[Created Date]),
Month(RequestFormAllrecords.[Created Date]),
RequestFormAllrecords.Department

ORDER BY Year(RequestFormAllrecords.[Created Date]),
Month(RequestFormAllrecords.[Created Date])


Note that SELECT can use arithmetic expression appearing in the GROUP BY
clause. Also, your query will be generally faster if your groups are
numerical rather than alphanumerical.



Hoping it may help,
Vanderghast, Access MVP

 

[Michael Lam]

Thx

Am I just modify the query as the same as you mention or what?

By the way, what is the following statements function

Year([RequestFormAllrecords].[Created
Date])*12+DatePart('m',[RequestFormAllrecords].[Created Date])-1;

Can you let me know
Thx again

Michael Lam

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> ¦b¶l¥ó
¤¤¼¶¼g...
Hi,


Just add the appropriate ORDER BY clause:


SELECT whatever

FROM RequestFormAllrecords

GROUP BY Year(RequestFormAllrecords.[Created Date]),
Month(RequestFormAllrecords.[Created Date]),
RequestFormAllrecords.Department

ORDER BY Year(RequestFormAllrecords.[Created Date]),
Month(RequestFormAllrecords.[Created Date])


Note that SELECT can use arithmetic expression appearing in the GROUP BY
clause. Also, your query will be generally faster if your groups are
numerical rather than alphanumerical.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


It is

12*Year +( Month-1)


where year and month are the year part and month part of the Created
Date.



Hoping it may help,
Vanderghast, Access MVP

Thx

Am I just modify the query as the same as you mention or what?

By the way, what is the following statements function

Year([RequestFormAllrecords].[Created
Date])*12+DatePart('m',[RequestFormAllrecords].[Created Date])-1;

Can you let me know
Thx again

Michael Lam

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> ¦b¶l¥ó
¤¤¼¶¼g...
Hi,


Just add the appropriate ORDER BY clause:


SELECT whatever

FROM RequestFormAllrecords

GROUP BY Year(RequestFormAllrecords.[Created Date]),
Month(RequestFormAllrecords.[Created Date]),
RequestFormAllrecords.Department

ORDER BY Year(RequestFormAllrecords.[Created Date]),
Month(RequestFormAllrecords.[Created Date])


Note that SELECT can use arithmetic expression appearing in the GROUP BY
clause. Also, your query will be generally faster if your groups are
numerical rather than alphanumerical.



Hoping it may help,
Vanderghast, Access MVP

Hi All

I have created query to group the records department and month

SELECT DISTINCTROW Format$([RequestFormAllrecords].[Created Date],'mmmm
yyyy') AS [Created Date/Month], RequestFormAllrecords.Department,
Sum([RequestFormAllrecords].[Estimate Effort]) AS [Total Estimate Effort],
Count(*) AS RequestFormAllrecordscount
FROM RequestFormAllrecords
GROUP BY Format$([RequestFormAllrecords].[Created Date],'mmmm yyyy'),
RequestFormAllrecords.Department, Year([RequestFormAllrecords].[Created
Date])*12+DatePart('m',[RequestFormAllrecords].[Created Date])-1;

however the output result like this
Created Date/Month Department Total Estimate Effort
RequestFormAllrecordscount
March 2004 AFFC 0
1
January 2004 AFFC 0.75
1
February 2004 AFFC 2
3

how can I sort the Create Date /Month so that It appear from Jan to Mar and
so on

Michael Lam

 

[Michel Walsh]

Hi,



The easiest modification is probably, in this case, to just add:


ORDER BY Year([RequestFormAllrecords].[Created
Date])*12+DatePart('m',[RequestFormAllrecords].[Created Date])-1



and I also suggest you place that computed expression in the SELECT part.


Hoping it may help,
Vanderghast, Access MVP

Thx

Am I just modify the query as the same as you mention or what?

By the way, what is the following statements function

Year([RequestFormAllrecords].[Created
Date])*12+DatePart('m',[RequestFormAllrecords].[Created Date])-1;

Can you let me know
Thx again

Michael Lam

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> ¦b¶l¥ó
¤¤¼¶¼g...
Hi,


Just add the appropriate ORDER BY clause:


SELECT whatever

FROM RequestFormAllrecords

GROUP BY Year(RequestFormAllrecords.[Created Date]),
Month(RequestFormAllrecords.[Created Date]),
RequestFormAllrecords.Department

ORDER BY Year(RequestFormAllrecords.[Created Date]),
Month(RequestFormAllrecords.[Created Date])


Note that SELECT can use arithmetic expression appearing in the GROUP BY
clause. Also, your query will be generally faster if your groups are
numerical rather than alphanumerical.



Hoping it may help,
Vanderghast, Access MVP

Hi All

I have created query to group the records department and month

SELECT DISTINCTROW Format$([RequestFormAllrecords].[Created Date],'mmmm
yyyy') AS [Created Date/Month], RequestFormAllrecords.Department,
Sum([RequestFormAllrecords].[Estimate Effort]) AS [Total Estimate Effort],
Count(*) AS RequestFormAllrecordscount
FROM RequestFormAllrecords
GROUP BY Format$([RequestFormAllrecords].[Created Date],'mmmm yyyy'),
RequestFormAllrecords.Department, Year([RequestFormAllrecords].[Created
Date])*12+DatePart('m',[RequestFormAllrecords].[Created Date])-1;

however the output result like this
Created Date/Month Department Total Estimate Effort
RequestFormAllrecordscount
March 2004 AFFC 0
1
January 2004 AFFC 0.75
1
February 2004 AFFC 2
3

how can I sort the Create Date /Month so that It appear from Jan to Mar and
so on

Michael Lam