I think I am close. Here is the code:
SELECT Count(tblLearningCenterClientActivities.StudentID) AS [Total Students], Format([ActivityDate],"ww") AS [Week of Activity]
FROM tblLearningCenterClientActivities
WHERE (((tblLearningCenterClientActivities.ActivityDate)>#1/1/2011#) AND ((tblLearningCenterClientActivities.LearningCenterID)=1))
GROUP BY Format([ActivityDate],"ww"), tblLearningCenterClientActivities.LearningCenterID;
Which returns:
Query1
Total Students
Week of Activity
9 10
1 27
30 6
47 7
63 8
31 9
But how can I add on something like a field that display some like "During the Week of" and display the Monday Date of the that Week of Activity. Is it in even doable?
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 2:22 PM
Subject: RE: [MS_AccessPros] Get a weekly average
Art-
Do you want the average per day over a week? Or, do you want the overall average of all the weeks? If the latter, use the below as the input to another query where you do Avg on the Count field with no Group By.
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 9:08 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Get a weekly average
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]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar