Maybe I need to rephrase what I want. I would like to get the average number of students attending activities on a weekly basis where the activity date is after 01/01/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:27 PM
Subject: 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
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar