Creating a "crosstab" formview

[Guest]

I'm working on a form that allows an instructor to enter student progress
through a series of learning module. Each course has 5 modules and each
module could have up to 10 questions. They are tracked using a code: F,D,A,
(finished,draft,attempt)
Each student could be enrolled in upto 3 courses at a time.

I'd like to display the data filtered on student ID such as:

Course Module Asign1 Asign2 Asign3 .....asign10
Math 1 F D ""
Reading 3 A F ...... you get the
point.

The assignment columns would be combo boxes.

I can only get them to display vertically. Is there a way to cycle through
them and display them horizontally? or is there a better layout that
provides a one glance view/edit of assignments


Thanks,

Todd

 

[Guest]

btw - the underlying table has the studentID, courseID,ModuleID, assignment
and progress column. As per a post on the Table design forum a week ago

 

[Guest]

I don't think you will achieve what you want with a crosstab.
Even dynamic crosstab reports rely on 2 fields one for column, one for row,
and a calculated value as the data...

You should probably look at a Pivot Table Query or a form in Pivot Table view.

Steve

 

[Guest]

If your table is like this then try the crosstab below.
studentID courseID ModuleID Assignment Progress
1 Reading 1 1 F
1 Math 3 1 D
1 Reading 1 2 A
1 Math 3 2 D
2 Reading 1 1 A
2 Math 3 1 D
2 Reading 1 2 F
2 Math 3 2 A
3 Reading 1 1 F
3 Reading 1 2 F
4 Math 3 1 A
4 Math 3 2 D

TRANSFORM Last(TLuebke.Progress) AS LastOfProgress
SELECT TLuebke.studentID, TLuebke.courseID, TLuebke.ModuleID
FROM TLuebke
GROUP BY TLuebke.studentID, TLuebke.courseID, TLuebke.ModuleID
PIVOT TLuebke.Assignment;

Results from the above crosstab and data.
studentID courseID ModuleID 1 2
1 Math 3 D D
1 Reading 1 F A
2 Math 3 D A
2 Reading 1 A F
3 Reading 1 F F
4 Math 3 A D

 

[Guest]

Your labels ---
TRANSFORM Last(TLuebke.Progress) AS LastOfProgress
SELECT TLuebke.studentID, TLuebke.courseID, TLuebke.ModuleID
FROM TLuebke
GROUP BY TLuebke.studentID, TLuebke.courseID, TLuebke.ModuleID
PIVOT "Asign" & [Assignment];

 

[John W. Vinson]

I'm working on a form that allows an instructor to enter student progress
through a series of learning module. Each course has 5 modules and each
module could have up to 10 questions. They are tracked using a code: F,D,A,
(finished,draft,attempt)
Each student could be enrolled in upto 3 courses at a time.

I'd like to display the data filtered on student ID such as:

Course Module Asign1 Asign2 Asign3 .....asign10
Math 1 F D ""
Reading 3 A F ...... you get the
point.

The assignment columns would be combo boxes.

I can only get them to display vertically. Is there a way to cycle through
them and display them horizontally? or is there a better layout that
provides a one glance view/edit of assignments

You may want to look into using a DataGrid control (google for it). The only
real alternative is an unbound form with VBA code to move the data from and to
the table into unbound form controls - doable, but a fair bit of work.

John W. Vinson [MVP]