Rabu, 15 Juni 2016

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

 

Liz,
You are correct to include all non-aggregate expressions that display in the SELECT also in the GROUP BY.
 
BTW: I had already read your email that you had assumed your SQL was resolved. However, I NEVER assume a recordset is going to appear in a particular order unless I include the ORDER BY clause.
 
Regards,
Duane
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 14 Jun 2016 21:49:40 +0000
Subject: RE: [MS_AccessPros] RE: sql to show count by month



Actually, that's how the sql wrote it out.  I think it had something to do with sorting it whether it was considered a string or not.  I wanted the formatting "-" to show, and the group by of the first group had to be there because that has to be the select.  It wouldn't let me take out one or the other I assure you.

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, June 14, 2016 2:05 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] RE: sql to show count by month

 




Hi Liz

The third expression in your GROUP BY is redundant, so you only need:
    GROUP BY Year([Date approved]), Month([Date approved])

It won't make the query function any differently, but it might be a tad faster
J

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.co! m [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, 15 June 2016 05:01
To: 'MS_Access_Professionals@yahoogroups.com' <MS_Access_Professionals@yahoogroups.com>
Subject: RE: [MS_AccessPros] RE: sql to show count by month

 

 

This did it:

 

SELECT Year([Date approved]) & "-" & Month([Date approved]) AS [When], Count([RFC Input].[CRF ! #]) AS ECRs

FROM [RFC Input]

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

GROUP BY Year([Date approved]), Month([Date approved]), Year([Date approved]) & "-" & Month([Date approved]);

 

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, June 14, 2016 9:58 AM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] RE: sql to show count by month

 




Trying all the stuff plus more and still don't have it.&n! bsp; Bawling about not being a part of the aggregate functions and all.!   It does work if I keep year and month separated and group by that, but I want that joined to one field and sequenced by the result.

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, June 14, 2016 9:21 AM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] RE: sql to show count by month

 



Um getting invalid procedure call error.

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
S! ent: Tuesday, June 14, 2016 8:58 AM
To: 'MS_Access_Profes! sionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] RE: sql to show count by month

 

 

I'll try this cause I added the order by and it choked.

 

From: MS_Acce! ss_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, June 14, 2016 7:40 AM
To: MS Access Pros List
Subject: RE: [MS_AccessPros] RE: sql to show count by month

 

 

Change the GROUP BY  from

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

TO:
GROUP BY DatePart("yyyy",[Date approved]) & "-" & DatePart("mm",[Date approved]);

 

hth,

Steve

 


To: ms_access_professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 14 Jun 2016 06:47:42 -0500
Subject: 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 O! ut

Building Microsoft Access Applications 

(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 

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

 

 

 

 

 

 

 

 

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

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.

 

 

 

 

 

 

 



This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary in! formation. 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.


&! nbsp;



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.






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 (17)

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.


.

__,_._,___

Tidak ada komentar:

Poskan Komentar