Of course! John is SQL KING. J
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, July 06, 2016 11:58 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Counting dates in a month
That worked out perfectly. Thank you.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, July 6, 2016 10:28 AM
Subject: Re: [MS_AccessPros] Re: Counting dates in a month
Liz & Art-
That's basically the problem. He wants to display and group by month name and year, but he needs to sort by year and month number.
Art-
To get the result you want you need two queries:
1: qryBaseCalc
SELECT Year([MOVEINDATE]) AS InYear, Month([MOVEINDATE]) AS InMonth, Count(tblTenantListing.ID) AS [Total Move-In]
FROM tblTenantListing
WHERE (((tblTenantListing.MOVEINDATE) Between #10/1/2015# And #6/30/2016#))
GROUP BY Year([MOVEINDATE]), Month([MOVEINDATE])
2:
SELECT Format(DateSerial([InYear], [InMonth], 1),"mmmm"" , ""yyyy") AS [Month of Move-In], [Total Move-In]
FROM qryBaseCalc
ORDER BY InYear, InMonth;
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 Jul 6, 2016, at 5:54 PM, Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I had a similar situation recently. Here's my sql:
SELECT Year([Date approved]) & "-" & Month([Date approved]) AS [When], Count([RFC Input].[CRF #]) AS ECRs
FROM [RFC Input]
WHERE ((([RFC Input].[Date approved])>#1/1/2015#))
GROUP BY Year([Date approved]) & "-" & Month([Date approved]), Year([Date approved]), Month([Date approved])
ORDER BY Year([Date approved]), Month([Date approved]);
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, July 06, 2016 8:52 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Counting dates in a month
Art-
See if you can get away with changing the Group By and Order By to:
GROUP BY Format([MOVEINDATE], "yyyy mm")
John
Sent from my iPad
> On Jul 6, 2016, at 17:42, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
>
> GROUP BY Format([MOVEINDATE],"mmmm"" , ""yyyy")
------------------------------------
Posted by: John Viescas <johnv@msn.com>
------------------------------------
------------------------------------
Yahoo Groups Links
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: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
------------------------------------
------------------------------------
Yahoo Groups Links
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: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
Tidak ada komentar:
Posting Komentar