Sorting help

M

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
 
M

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
 
M

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
 
M

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


Michael Lam said:
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


Michael Lam said:
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
Click to expand...
Click to expand...
 
M

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


Michael Lam said:
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


Michael Lam said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sort by Month in a query 3
Consecutive months 2
Cross-Tab Query 2
Access shuts down, when i try to save this query 12
QueryHelp 4
Sum,Sort, Format, and Date Range 2
Ask for advise 1
Date Range 2

Top