Thanks, I'll give this a shot. I'll let you know tomorrow or Friday.
"KARL DEWEY" wrote:
> 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" wrote:
>
> > Sorry, Events is simply an Autonumber field.
> >
> > "KARL DEWEY" wrote:
> >
> > > What data do you store in field Events?
> > >
> > > --
> > > KARL DEWEY
> > > Build a little - Test a little
> > >
> > >
> > > "Dan Robles" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > Post the crosstab SQL, sample data, and how you want the average to be
> > > > > displayed.
> > > > > --
> > > > > KARL DEWEY
> > > > > Build a little - Test a little
> > > > >
> > > > >
> > > > > "Dan Robles" wrote:
> > > > >
> > > > > > 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?
|
Similar Threads
