ACCESS FEED: RE: [MS_AccessPros] Monthly Count

Kamis, 30 Juni 2011

RE: [MS_AccessPros] Monthly Count

Art-

Duh. I didn't notice that you were passing Month([Activity Date]) to the format function. Using the Month function returns values 1-12, all in January of 1899. The Month call was screwing you up.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Thursday, June 30, 2011 8:41 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Monthly Count

John,

I figured it out. It was the FORMAT function. This is the correct code:

SELECT Count(tblLearningCenterClientActivities.StudentID) AS [Total Students], Format([ActivityDate],"mmmm") AS [Name of Month], Year([ActivityDate]) AS [Year]
FROM tblLearningCenterClientActivities
GROUP BY Format([ActivityDate],"mmmm"), Year([ActivityDate]), tblLearningCenterClientActivities.LearningCenterID
HAVING (((tblLearningCenterClientActivities.LearningCenterID)=1))
ORDER BY Year([ActivityDate]) DESC;

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947

1316 E. 7th Street
Sioux Falls, SD 57103
SQL Server Development
Database Adminstration Services
Microsoft Access Development
Grant Writing TA Services
IT Assessment Services
Software Application Training

From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, June 30, 2011 12:53 PM
Subject: RE: [MS_AccessPros] Monthly Count

Art-

Do you have activities in February for LearningCenterID 1?

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Thursday, June 30, 2011 7:41 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Monthly Count

I have the following query that returns a count based on activity dates by the
month:

SELECT Count(tblLearningCenterClientActivities.StudentID) AS [Total Students],
Format(Month([ActivityDate]),"mmmm") AS [Month of Activity]
FROM tblLearningCenterClientActivities
WHERE (((tblLearningCenterClientActivities.ActivityDate)>#1/1/2011#))
GROUP BY Format(Month([ActivityDate]),"mmmm"),
tblLearningCenterClientActivities.LearningCenterID
HAVING (((tblLearningCenterClientActivities.LearningCenterID)=1));

But it only returning a count in January even though I know there are activities
in feburary. Confused!!!

Thanks
Art Lorenzini
Sioux falls

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links