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

Kamis, 30 Juni 2011

RE: [MS_AccessPros] Get a weekly average

Arthur-

Just look up the options for the Format function. You probably want:

SELECT Count([Learning Center Client Activities].StudentID) AS [Total Students],

Format(Month([ActivityDate]),"ww") AS [Month of Activity]
FROM [Learning Center Client Activities]
WHERE ([Learning Center Client Activities].[ActivityDate]>#1/1/2011#)
And ([Learning Center Client Activities].LearningCenterID=1)
GROUP BY Format(Month([ActivityDate]),"ww"), [Learning Center Client
Activities].LearningCenterID;

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 6:56 PM
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

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

Yahoo! Groups Links