RE: [MS_AccessPros] RE: sql to show count by month

 

Liz,
As John implies, never assume records will appear in a particular order (even with a group by).
 
I don't believe it is possible to order by a field that isn't in the select clause particularly when the query is a totals query. I would stick with answer number 1.
 
Regards,
Duane
 

---

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 14 Jun 2016 10:53:52 +0200
Subject: Re: [MS_AccessPros] RE: sql to show count by month



Or just 

ORDER BY [Date approved];

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Jun 14, 2016, at 10:26 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Liz-

Try adding:

ORDER BY Year([Date approved]), Month([Date approved])

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Jun 14, 2016, at 12:43 AM, Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

And the interesting thing is, when I put it to a chart, it sequences out right fine.

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] 
Sent: Monday, June 13, 2016 3:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] sql to show count by month

 

Pros and heh – sql meister John…

 

I'm trying to get the sql to show a total count by month / year of the last 365 days but show it in chronological sequence and am at a loss.

 

Here's my sql

 

SELECT DatePart("m",[Date approved]) & "-" & DatePart("yyyy",[Date approved]) AS [When], Count([RFC Input].[CRF #]) AS ECRs

FROM [RFC Input]

WHERE ((([RFC Input].[Date approved])>Date()-365))

GROUP BY DatePart("m",[Date approved]) & "-" & DatePart("yyyy",[Date approved]);

 

But then it shows up in ascii sequence

ECRApprovedCountOfYear
When	ECRs
10-2015	239
11-2015	170
1-2016	216
12-2015	155
2-2016	167
3-2016	208
4-2016	230
5-2016	270
6-2015	164
6-2016	102
7-2015	337
8-2015	297
9-2015	250

 

 

Respectfully,

Liz Ravenwood

Programmer / Analyst

B/E Aerospace | Super First Class Environments

 

1851 S Pantano Road | Tucson, Arizona 85710

Office +1.520.239.4808 | Internal 814-4808

beaerospace.com

Passion to Innovate. Power to Deliver

 

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___