Dan Robles said:Karl, thanks for your help.
The SQL looks like this:
TRANSFORM Count([Tutoring by Day].Events) AS CountOfEvents
SELECT [Tutoring by Day].Activity, Count([Tutoring by Day].Events) AS [Total
Of Events]
FROM [Tutoring by Day]
GROUP BY [Tutoring by Day].Activity
PIVOT [Tutoring by Day].Day;
The data looks like this:
(I truncated one of the days so that it would fit in the window here)
Activity Total Events Friday Monday Thursday Tuesday
Homework Lab 415 21 161 21 186
Study Hall 1639 143 221 584 327
Tutoring 2357 251 502 658 531
What my boss is looking for is an average number of kids coming in for
homework lab, tutoring, etc. for each day. What do you suggest?
KARL DEWEY said:Post the crosstab SQL, sample data, and how you want the average to be
displayed.
KARL DEWEY said:What data do you store in field Events?
--
KARL DEWEY
Build a little - Test a little
Dan Robles said:Karl, thanks for your help.
The SQL looks like this:
TRANSFORM Count([Tutoring by Day].Events) AS CountOfEvents
SELECT [Tutoring by Day].Activity, Count([Tutoring by Day].Events) AS [Total
Of Events]
FROM [Tutoring by Day]
GROUP BY [Tutoring by Day].Activity
PIVOT [Tutoring by Day].Day;
The data looks like this:
(I truncated one of the days so that it would fit in the window here)
Activity Total Events Friday Monday Thursday Tuesday
Homework Lab 415 21 161 21 186
Study Hall 1639 143 221 584 327
Tutoring 2357 251 502 658 531
What my boss is looking for is an average number of kids coming in for
homework lab, tutoring, etc. for each day. What do you suggest?
KARL DEWEY said:Post the crosstab SQL, sample data, and how you want the average to be
displayed.
--
KARL DEWEY
Build a little - Test a little
:
I made a crosstab query that calculates the days of the week that our
students are on campus, and the activities they complete. Now I need to
average the activity for each weekday. Any Advice?
Dan Robles said:Sorry, Events is simply an Autonumber field.
KARL DEWEY said:What data do you store in field Events?
--
KARL DEWEY
Build a little - Test a little
Dan Robles said:Karl, thanks for your help.
The SQL looks like this:
TRANSFORM Count([Tutoring by Day].Events) AS CountOfEvents
SELECT [Tutoring by Day].Activity, Count([Tutoring by Day].Events) AS [Total
Of Events]
FROM [Tutoring by Day]
GROUP BY [Tutoring by Day].Activity
PIVOT [Tutoring by Day].Day;
The data looks like this:
(I truncated one of the days so that it would fit in the window here)
Activity Total Events Friday Monday Thursday Tuesday
Homework Lab 415 21 161 21 186
Study Hall 1639 143 221 584 327
Tutoring 2357 251 502 658 531
What my boss is looking for is an average number of kids coming in for
homework lab, tutoring, etc. for each day. What do you suggest?
:
Post the crosstab SQL, sample data, and how you want the average to be
displayed.
--
KARL DEWEY
Build a little - Test a little
:
I made a crosstab query that calculates the days of the week that our
students are on campus, and the activities they complete. Now I need to
average the activity for each weekday. Any Advice?
KARL DEWEY said:With your data structure you will need two queries --
Tutoring by Day_Rollup ---
SELECT [Tutoring by Day].Day, [Tutoring by Day].Activity, Count([Tutoring by
Day].Events) AS CountOfEvents
FROM [Tutoring by Day]
GROUP BY [Tutoring by Day].Day, [Tutoring by Day].Activity;
TRANSFORM Avg([Tutoring by Day_Rollup].CountOfEvents) AS AvgOfCountOfEvents
SELECT [Tutoring by Day_Rollup].Activity, Avg([Tutoring by
Day_Rollup].CountOfEvents) AS Averages
FROM [Tutoring by Day_Rollup]
GROUP BY [Tutoring by Day_Rollup].Activity
PIVOT [Tutoring by Day_Rollup].Day;
Your table should look like this ---
Activity – text
TutorDate – DateTime – calendar date recorded.
Events – number – number of students attending a particular activity on a
given date.
Then your average crosstab would look like this ---
TRANSFORM Avg([Tutoring by Day].Events) AS AvgOfEvents
SELECT [Tutoring by Day].Activity, Avg([Tutoring by Day].Events) AS [Average]
FROM [Tutoring by Day]
GROUP BY [Tutoring by Day].Activity
PIVOT Format([TutorDate],"dddd") IN("Sunday", "Monday", "Tuesday",
"Wednesday", "Thursday", "Friday", "Saturday");
TRANSFORM Sum([Tutoring by Day].Events) AS SumOfEvents
SELECT [Tutoring by Day].Activity, Sum([Tutoring by Day].Events) AS [Total
Of Events]
FROM [Tutoring by Day]
GROUP BY [Tutoring by Day].Activity
PIVOT Format([TutorDate],"dddd") In ("Sunday", "Monday", "Tuesday",
"Wednesday", "Thursday", "Friday", "Saturday");
--
KARL DEWEY
Build a little - Test a little
Dan Robles said:Sorry, Events is simply an Autonumber field.
KARL DEWEY said:What data do you store in field Events?
--
KARL DEWEY
Build a little - Test a little
:
Karl, thanks for your help.
The SQL looks like this:
TRANSFORM Count([Tutoring by Day].Events) AS CountOfEvents
SELECT [Tutoring by Day].Activity, Count([Tutoring by Day].Events) AS [Total
Of Events]
FROM [Tutoring by Day]
GROUP BY [Tutoring by Day].Activity
PIVOT [Tutoring by Day].Day;
The data looks like this:
(I truncated one of the days so that it would fit in the window here)
Activity Total Events Friday Monday Thursday Tuesday
Homework Lab 415 21 161 21 186
Study Hall 1639 143 221 584 327
Tutoring 2357 251 502 658 531
What my boss is looking for is an average number of kids coming in for
homework lab, tutoring, etc. for each day. What do you suggest?
:
Post the crosstab SQL, sample data, and how you want the average to be
displayed.
--
KARL DEWEY
Build a little - Test a little
:
I made a crosstab query that calculates the days of the week that our
students are on campus, and the activities they complete. Now I need to
average the activity for each weekday. Any Advice?