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
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
__._,_.___
Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (10) |
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:
Posting Komentar