ACCESS FEED: RE: [MS_AccessPros] Get a weekly average

Kamis, 30 Juni 2011

RE: [MS_AccessPros] Get a weekly average

Sorry Art - I replied to the wrong message - this one should have been in
the "Monthly Count" thread.

-- Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Graham Mandeno
Sent: Friday, 1 July 2011 06:57
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Get a weekly average

Hi Art

Just use Format([Activity Date], "mmmm") not
Format(Month([ActivityDate]),"mmmm").

The latter is taking the month number (between 1 and 12) and interpreting it
as a date by adding it to 30-Dec-1899 (giving a date between 31-Dec-1899 and
11-Jan-1900). So for all dates in January you will get "December" and for
all other dates you will get "January". Confused? I'm not surprised! :-)

Cheers,
Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Friday, 1 July 2011 04:56
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Get a weekly average

I am working on a query that should pull the average number of students
participating in an activity on a weekly basis. The following query returns
a count of students that attended a actvitiy on a monthly basis:

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

Is there way to tweek this one to pull the weekly average?

Thanks,

Arthur Lorenzini
Sioux Falls, SD