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]);
Trying all the stuff plus more and still don't have it. 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.
Um getting invalid procedure call error.
I'll try this cause I added the order by and it choked.
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]);
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
ORDER BY [Date approved];
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
Liz-
ORDER BY Year([Date approved]), Month([Date approved])
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
And the interesting thing is, when I put it to a chart, it sequences out right fine.
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.
SELECT DatePart("m",[Date approved]) & "-" & DatePart("yyyy",[Date approved]) AS [When], Count([RFC Input].[CRF #]) AS ECRs
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 |
| 239 |
| 170 |
| 216 |
| 155 |
| 167 |
| 208 |
| 230 |
| 270 |
| 164 |
| 102 |
| 337 |
| 297 |
| 250 |
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 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.
Tidak ada komentar:
Posting Komentar