Assistance needed for complex cross-tab report

[Guest]

I need some ideas on how to solve a problem. Excuse me for the length of the
post; I’ll be as concise as opssible. I need to create a report in which
weekly school attendance and class data appears in the following cross-tab
format:

Times Rm #18 Rm #22 Rm #36
7:40 - StuCnt: 25 StuCnt:27 StuCnt:30
8:25 AvAttn: 3.5 AvAttn:4.3 AvAttn:4.45
Period 1 Resource Trans Outcome Science
Amy Kris Glenda

8:26 - StuCnt:32 StuCnt:30 StuCnt:35
8:45 AvAttn:3.6 AvAttn:3.8 AvAttn:3.76
Period 2 SDL SDL SDL
Amy Kris Mary
Scott

This table columns represent room assignments and the rows designate class
periods. Inside each cell the following data appear:
1.Teacher; 2. Course; 3. Avg Weekly Attendance; 4. # of students in class.

The data is stored in several, related Access tables. I gathered the data
into a query that produces the following view:

Period Time RmNo Teacher Course StuID Attnd
1 7:40 - 8:25 18 Amy Resource 394 2.00
1 7:40 - 8:25 18 Amy Resource 403 0.00
1 7:40 - 8:25 18 Amy Resource 408 2.50
1 7:40 - 8:25 22 Kris Tran Outcome 362 3.00
1 7:40 - 8:25 22 Kris Tran Outcome 370 3.00
1 7:40 - 8:25 22 Kris Tran Outcome 407 1.00
1 7:40 - 8:25 23 Glenda Science 95
0.00
1 7:40 - 8:25 23 Glenda Science 106
2.50

Each row represents a student (StuID) and related data. The total dataset
represents the attendance information for the past week. (thus, student 394
attended period 1 class in Rm 18 for 2 days)

Of course, the normal Access crosstab report cannot produce this; neither
can Excel. I have even tried subreports, but without success. Unless I am
overlooking something. I’ve tried to build the report manually, using report
textboxes and =IIF statements, e.g. =IIf(Nz([RoomNo]=19,""),"count: " &
Count([AccessCode]),""). However, this is only partially correct.

Does anybody have a better solution? I’m thinking that it will require a
programming approach, involving some arrays. If anybody has a few tips to
throw my way, I’d appreciate it greatly!

George

 

[Duane Hookom]

Create two totals queries based on your existing query (you may need to
change field or query names):

==qtotClass========
SELECT Period, Times, RmNo, Teacher, Course, Count(StuID) AS StuCnt,
Avg(Attnd) AS AvAttn
FROM qselAttend
GROUP BY Period, Times, RmNo, Teacher, Course;

==qgrpPeriods=======
SELECT Period, Times
FROM qselAttend
GROUP BY Period, Times;

Create a multi-column subreport based on qtotClass. Set it to a minimal
width and set the columns to display across then down.

Create a main report based on qgrpPeriods. Add the subreport to the detail
section of the main report and set the Link Master/Child properties to
Period. Stretch the subreport across the detail section of the main report.

--
Duane Hookom
MS Access MVP
--

I need some ideas on how to solve a problem. Excuse me for the length of
the
post; I'll be as concise as opssible. I need to create a report in which
weekly school attendance and class data appears in the following cross-tab
format:

Times Rm #18 Rm #22 Rm #36
7:40 - StuCnt: 25 StuCnt:27 StuCnt:30
8:25 AvAttn: 3.5 AvAttn:4.3 AvAttn:4.45
Period 1 Resource Trans Outcome Science
Amy Kris Glenda

8:26 - StuCnt:32 StuCnt:30 StuCnt:35
8:45 AvAttn:3.6 AvAttn:3.8 AvAttn:3.76
Period 2 SDL SDL SDL
Amy Kris Mary
Scott

This table columns represent room assignments and the rows designate class
periods. Inside each cell the following data appear:
1.Teacher; 2. Course; 3. Avg Weekly Attendance; 4. # of students in class.

The data is stored in several, related Access tables. I gathered the data
into a query that produces the following view:

Period Time RmNo Teacher Course StuID Attnd
1 7:40 - 8:25 18 Amy Resource 394
2.00
1 7:40 - 8:25 18 Amy Resource 403
0.00
1 7:40 - 8:25 18 Amy Resource 408
2.50
1 7:40 - 8:25 22 Kris Tran Outcome 362
3.00
1 7:40 - 8:25 22 Kris Tran Outcome 370
3.00
1 7:40 - 8:25 22 Kris Tran Outcome 407
1.00
1 7:40 - 8:25 23 Glenda Science 95
0.00
1 7:40 - 8:25 23 Glenda Science 106
2.50

Each row represents a student (StuID) and related data. The total dataset
represents the attendance information for the past week. (thus, student
394
attended period 1 class in Rm 18 for 2 days)

Of course, the normal Access crosstab report cannot produce this; neither
can Excel. I have even tried subreports, but without success. Unless I am
overlooking something. I've tried to build the report manually, using
report
textboxes and =IIF statements, e.g. =IIf(Nz([RoomNo]=19,""),"count: " &
Count([AccessCode]),""). However, this is only partially correct.

Does anybody have a better solution? I'm thinking that it will require a
programming approach, involving some arrays. If anybody has a few tips to
throw my way, I'd appreciate it greatly!

George

 

[Guest]

This is a great direction to move in, Duane. Many thanks. It is pretty close,
too. I presumed you meant that in the main report the period field would be
placed in the detail area, along with the sub-report, right? That's what it
looks like to me.

There are a few things i have to work out, such as getting the room numbers
to appear once across the top (I may hard-wire them into labels and not show
the txt boxes for room numbers).

Also, I found out that in some cases, there are two classes in the same room
at the same time! Thus, there might be an arrangement like this:

Per Room 18 Room 19
1 10 12 15
4.5 4.8 4.2
Liz Mike Dan
Math Math ISD

Another good reason to code the room numbers manually, I'm thinking. But, I
think this is working out. Hell, it's a lot further than I was getting.
Appreciate it!

George

Create two totals queries based on your existing query (you may need to
change field or query names):

==qtotClass========
SELECT Period, Times, RmNo, Teacher, Course, Count(StuID) AS StuCnt,
Avg(Attnd) AS AvAttn
FROM qselAttend
GROUP BY Period, Times, RmNo, Teacher, Course;

==qgrpPeriods=======
SELECT Period, Times
FROM qselAttend
GROUP BY Period, Times;

Create a multi-column subreport based on qtotClass. Set it to a minimal
width and set the columns to display across then down.

Create a main report based on qgrpPeriods. Add the subreport to the detail
section of the main report and set the Link Master/Child properties to
Period. Stretch the subreport across the detail section of the main report.

--
Duane Hookom
MS Access MVP
--

I need some ideas on how to solve a problem. Excuse me for the length of
the
post; I'll be as concise as opssible. I need to create a report in which
weekly school attendance and class data appears in the following cross-tab
format:

Times Rm #18 Rm #22 Rm #36
7:40 - StuCnt: 25 StuCnt:27 StuCnt:30
8:25 AvAttn: 3.5 AvAttn:4.3 AvAttn:4.45
Period 1 Resource Trans Outcome Science
Amy Kris Glenda

8:26 - StuCnt:32 StuCnt:30 StuCnt:35
8:45 AvAttn:3.6 AvAttn:3.8 AvAttn:3.76
Period 2 SDL SDL SDL
Amy Kris Mary
Scott

This table columns represent room assignments and the rows designate class
periods. Inside each cell the following data appear:
1.Teacher; 2. Course; 3. Avg Weekly Attendance; 4. # of students in class.

The data is stored in several, related Access tables. I gathered the data
into a query that produces the following view:

Period Time RmNo Teacher Course StuID Attnd
1 7:40 - 8:25 18 Amy Resource 394
2.00
1 7:40 - 8:25 18 Amy Resource 403
0.00
1 7:40 - 8:25 18 Amy Resource 408
2.50
1 7:40 - 8:25 22 Kris Tran Outcome 362
3.00
1 7:40 - 8:25 22 Kris Tran Outcome 370
3.00
1 7:40 - 8:25 22 Kris Tran Outcome 407
1.00
1 7:40 - 8:25 23 Glenda Science 95
0.00
1 7:40 - 8:25 23 Glenda Science 106
2.50

Each row represents a student (StuID) and related data. The total dataset
represents the attendance information for the past week. (thus, student
394
attended period 1 class in Rm 18 for 2 days)

Of course, the normal Access crosstab report cannot produce this; neither
can Excel. I have even tried subreports, but without success. Unless I am
overlooking something. I've tried to build the report manually, using
report
textboxes and =IIF statements, e.g. =IIf(Nz([RoomNo]=19,""),"count: " &
Count([AccessCode]),""). However, this is only partially correct.

Does anybody have a better solution? I'm thinking that it will require a
programming approach, involving some arrays. If anybody has a few tips to
throw my way, I'd appreciate it greatly!

George