ACCESS FEED: Re: [MS_AccessPros] Monthly Count

Kamis, 30 Juni 2011

Re: [MS_AccessPros] Monthly Count

I am starting to think it has something to do with the format command. I tried a new query:

SELECT Count(tblLearningCenterClientActivities.StudentID) AS CountOfStudentID, Month([ActivityDate]) AS [Month], Year([ActivityDate]) AS [Year]
FROM tblLearningCenterClientActivities
GROUP BY Month([ActivityDate]), Year([ActivityDate]), tblLearningCenterClientActivities.LearningCenterID
HAVING (((tblLearningCenterClientActivities.LearningCenterID)=1));

Query1
CountOfStudentID
Month
Year
1

180 2 2011
1 6 2011
84 10 2007
92 11 2007
14 11 2010
65 12 2010

But as soon as I try putting in the Format command on the month field to get the Month name instead of number everything goes south.

SELECT Count(tblLearningCenterClientActivities.StudentID) AS CountOfStudentID, Format(Month([ActivityDate]),"mmmm") AS [Month], Year([ActivityDate]) AS [Year]
FROM tblLearningCenterClientActivities
GROUP BY Format(Month([ActivityDate]),"mmmm"), Year([ActivityDate]), tblLearningCenterClientActivities.LearningCenterID
HAVING (((tblLearningCenterClientActivities.LearningCenterID)=1));

Query1
CountOfStudentID
Month
Year
1

176 January 2007
79 January 2010
181 January 2011

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]